Syntax error at or near returning

PostgreSQL error 42601 mainly occurs due to the syntax errors in the code. Proper syntax check and code correction will fix it up.

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник
Posted By: Anonymous

I would like to know how to use a dynamic query inside a function. I’ve tried lots of ways, however, when I try to compile my function a message SQL 42601 is displayed.

The code that I use:

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS 
$$
BEGIN
  WITH v_tb_person AS (return query execute sql)
  select name, count(*) from v_tb_person where nome like '%a%' group by name
  union
  select name, count(*) from v_tb_person where gender = 1 group by name;
END     
$$ LANGUAGE plpgsql;

Error message I receive:

ERROR:  syntax error at or near "return"
LINE 5:     WITH v_tb_person AS (return query execute sql)

I tried using:

WITH v_tb_person AS (execute sql)

WITH v_tb_person AS (query execute)

WITH v_tb_person AS (return query execute)

What is wrong? How can I solve this problem?

Its a question related to PostgreSQL equivalent of Oracle “bulk collect”

Solution

Your function would work like this:

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS 
$$
BEGIN

RETURN QUERY EXECUTE '
WITH v_tb_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM v_tb_person WHERE nome LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM v_tb_person WHERE gender = 1 GROUP BY name$x$;

END     
$$ LANGUAGE plpgsql;

Call:

SELECT * FROM prc_tst_bulk($$SELECT a AS name, b AS nome, c AS gender FROM tbl$$)

  • You cannot mix plain and dynamic SQL the way you tried to do it. The whole statement is either all dynamic or all plain SQL. So I am building one dynamic statement to make this work. You may be interested in the chapter about executing dynamic commands in the manual.

  • The aggregate function count() returns bigint, but you had rowcount defined as integer, so you need an explicit cast ::int to make this work

  • I use dollar quoting to avoid quoting hell.

However, is this supposed to be a honeypot for SQL injection attacks or are you seriously going to use it? For your very private and secure use, it might be ok-ish – though I wouldn’t even trust myself with a function like that. If there is any possible access for untrusted users, such a function is a loaded footgun. It’s impossible to
make this secure.

Craig (a sworn enemy of SQL injection!) might get a light stroke, when he sees what you forged from his piece of code in the answer to your preceding question. 🙂

The query itself seems rather odd, btw. But that’s beside the point here.

Answered By: Anonymous

Related Articles

  • Combining Ember Table with Ember Data
  • Maven2: Missing artifact but jars are in place
  • What is the worst programming language you ever worked with?
  • Homebrew install specific version of formula?
  • sql query to find priority jobs
  • generate days from date range
  • Multiple separate IF conditions in SQL Server
  • How to properly do JSON API GET requests and assign output…
  • SQL query return data from multiple tables
  • Gradle error: Execution failed for task…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Источник

#10474

closed


(duplicate)

Reported by: Owned by: Malcolm Tredinnick
Component: Core (Other) Version: dev
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

changeset:10029 introduces a syntax error (running Django in apache with mod_python): 

Traceback (most recent call last):

  File "/usr/lib/python2.4/site-packages/django/core/handlers/base.py", line 89, in get_response
    response = callback(request, *callback_args, **callback_kwargs)

  File "/usr/lib/python2.4/site-packages/django/contrib/auth/decorators.py", line 67, in __call__
    return self.view_func(request, *args, **kwargs)

  File "/srv/atizo/atizo/platform/views/auth.py", line 29, in logout
    django_logout(request)

  File "/usr/lib/python2.4/site-packages/django/contrib/auth/__init__.py", line 75, in logout
    request.session.flush()

  File "/usr/lib/python2.4/site-packages/django/contrib/sessions/backends/base.py", line 240, in flush
    self.create()

  File "/usr/lib/python2.4/site-packages/django/contrib/sessions/backends/db.py", line 36, in create
    self.save(must_create=True)

  File "/usr/lib/python2.4/site-packages/django/contrib/sessions/backends/db.py", line 58, in save
    obj.save(force_insert=must_create)

  File "/usr/lib/python2.4/site-packages/django/db/models/base.py", line 329, in save
    self.save_base(force_insert=force_insert, force_update=force_update)

  File "/usr/lib/python2.4/site-packages/django/db/models/base.py", line 401, in save_base
    result = manager._insert(values, return_id=update_pk)

  File "/usr/lib/python2.4/site-packages/django/db/models/manager.py", line 144, in _insert
    return insert_query(self.model, values, **kwargs)

  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 1054, in insert_query
    return query.execute_sql(return_id)

  File "/usr/lib/python2.4/site-packages/django/db/models/sql/subqueries.py", line 315, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)

  File "/usr/lib/python2.4/site-packages/django/db/models/sql/query.py", line 2097, in execute_sql
    cursor.execute(sql, params)

ProgrammingError: syntax error at or near "RETURNING" at character 211

Источник

Ошибка синтаксиса ошибки postgres в или около «int» при создании функции

Я новичок в postgres. Я получил эту ошибку при попытке запустить следующий скрипт:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
    SELECT count(*) into result from tbldealercommissions
    WHERE 
    txtdealercode = $1;

    if result < 1 then returns 1; 
    else returns 2 ;
    end if;
END;
    '
LANGUAGE sql VOLATILE;

Ошибка 

ERROR:  syntax error at or near "int"
LINE 3: 'DECLARE result int;

не уверен, что вызывает эту ошибку. Любая помощь приветствуется.

2 ответы

Это не подходит: 

LANGUAGE sql

используйте это вместо:

LANGUAGE plpgsql

Синтаксис, который вы пытаетесь использовать, — это не чистый язык SQL, а процедурный язык PL / pgSQL. В PostgreSQL вы можете устанавливать разные языки, и PL / pgSQL является в этом отношении только первыми. Это также означает, что вы можете получить сообщение об ошибке, что этот язык не установлен. В этом случае используйте 

CREATE LANGUAGE plpgsql;

который его активизирует. В зависимости от версии PostgreSQL для выполнения этого шага вам могут потребоваться права суперпользователя.

Удачи.

ответ дан 06 окт ’11, 14:10

Вы не только используете неправильный язык (как отмечает AH), но и returns ключевое слово, вы хотите return. Возможно, вы захотите использовать другой разделитель, чтобы избежать проблем со строковыми литералами в ваших функциях, $$ довольно часто. Я думаю, ваша функция должна выглядеть примерно так:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
    select count(*) into result
    from tbldealercommissions
    where txtdealercode = $1;

    if result < 1 then return 1; 
    else return 2;
    end if;
END;
$$ LANGUAGE plpgsql VOLATILE;

ответ дан 06 окт ’11, 18:10

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

postgresql

or задайте свой вопрос.

Источник

Понравилась статья? Поделить с друзьями:
  • Syntax error at or near references
  • Svn an error occurred during ssl communication
  • Syntax error at or near postgresql что это
  • Svg изображения как изменить размер
  • Syntax error at or near limit postgresql