Sql error 42601 error query has no destination for result data

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»;

Источник

Вопрос:

Я пытаюсь создать функцию, которая возвращает SELECTed resultset.
Когда я вызываю функцию postgres, такую ​​как select * from tst_dates_func(), я получаю сообщение об ошибке, как показано ниже:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "tst_dates_func" line 3 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "tst_dates_func" line 3 at SQL statement

Вот созданная мной функция:

CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$
BEGIN
select a.date_value, a.date_id, a.date_desc from dates_tbl a;
END;
$BODY$
LANGUAGE plpgsql;

Я не уверен, почему я получаю вышеуказанную ошибку. Я хотел бы запустить select * from tst_dates_func();
и вернуть данные. Или, если необходимо, присоедините к набору результатов. В чем проблема?

Лучший ответ:

Сделайте это как простой SQL

CREATE OR REPLACE FUNCTION tst_dates_func() 
    RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$   
    select a.date_value, a.date_id, a.date_desc from dates_tbl a;

$BODY$
      LANGUAGE sql;

Если вам действительно нужно использовать plpgsql return query

CREATE OR REPLACE FUNCTION tst_dates_func() 
    RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$   
BEGIN
    perform SELECT dblink_connect('remote_db');
    return query
    select a.date_value, a.date_id, a.date_desc from dates_tbl a;

END;
$BODY$
      LANGUAGE plpgsql;

Ответ №1

У меня та же проблема с моим кодом ниже. Кто-нибудь может помочь?

CREATE OR REPLACE FUNCTION public.faq_sc13_rate()
RETURNS real
AS $function$
BEGIN
select
sum(stock_rate.s13)/sum(stock_rate.weight) as s13_rate,
sum(stock_rate.weight) as weight
from
(
select ss.init_qty as weight,
ss.init_qty*ss.screen13 as s13,
-- ss.init_qty*screen16 as s16,
-- ss.init_qty*(screen18+screen19+screen20) as s18
from stock_stack ss
join product_product pp on pp.id = ss.product_id

where ss.init_qty > 0 and pp.default_code = 'FAQ'
) as stock_rate;
return stock_rate.s13_rate*stock_rate.weight/100*0.95;
end;
$function$
LANGUAGE plpgsql;

Ответ №2

In PLPGSQL - use RETURN QUERY

CREATE OR REPLACE FUNCTION tst_dates_func()
RETURNS TABLE( date_value date, date_id int, date_desc varchar) as
$BODY$
BEGIN
RETURN QUERY (select a.date_value, a.date_id, a.date_desc from dates_tbl a);
END;
$BODY$
LANGUAGE plpgsql;
Источник

I am learning Npgsql and PostgreSQL. I am unable to define the output parameter correctly. What am I doing wrong?

Here is the function:

CREATE OR REPLACE FUNCTION Insert_Customer_WithOutputParameter(
    IN _FirstName character varying DEFAULT NULL::character varying,
    IN _LastName character varying DEFAULT NULL::character varying,
    OUT _CustomerID integer)
  RETURNS integer as
$BODY$
BEGIN
INSERT INTO Customers (FirstName, LastName) VALUES (_FirstName, _LastName);

SELECT _CustomerID = lastval();

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here is the code:

[Test]
public void ExecuteNonQuerySproc()
{
    NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
    conn.Open();
    IDbCommand command = conn.CreateCommand();
    command.CommandText = "Insert_Customer_WithOutputParameter";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new NpgsqlParameter("@FirstName", "John"));
    command.Parameters.Add(new NpgsqlParameter("@LastName", "Smith"));
    NpgsqlParameter outParm = new NpgsqlParameter("@CustomerID", NpgsqlDbType.Integer)
    {
        Direction = ParameterDirection.Output
    };
    command.Parameters.Add(outParm);

    command.ExecuteNonQuery();            
    conn.Close();
    Console.WriteLine(outParm.Value);
}

Here is the error message I am getting: Npgsql.NpgsqlException : ERROR: 42601: query has no destination for result data

The following doesn’t work:

SELECT _CustomerID = lastval();

Replace it with a simple:

Note that Npgsql currently binds parameters by position only, and not by name. This means that the names you give in the NpgsqlParameter instances mean nothing — their order of addition must correspond to the function’s declaration. Npgsql 3.1 will support named binding of function arguments (see this issue).

before fixing:

-- Function: sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)

-- DROP FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer);

CREATE OR REPLACE FUNCTION sp_updatesecurity(
    OUT sqlcode_out integer,
    IN parastaffid integer,
    IN parafunctioncode character,
    IN paraviewflag character,
    IN paramodifyflag character,
    IN paraadvanceflag character,
    IN paralocalversion integer,
    IN original_staffid integer,
    IN original_functioncode character,
    IN original_localversion integer)
  RETURNS integer AS
$BODY$                                                              
DECLARE SQLCODE_OUT_TMP INT DEFAULT 0;
begin
IF (paraVIEWFLAG IS NULL) OR ((paraVIEWFLAG <> '1') AND (paraVIEWFLAG <> '0')) THEN
    set SQLCODE_OUT_TMP = -1;
END IF;

IF (paraMODIFYFLAG IS NULL) OR ((paraMODIFYFLAG <> '1') AND (paraMODIFYFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -2;
END IF;

IF (paraADVANCEFLAG IS NULL) OR ((paraADVANCEFLAG <> '1') AND (paraADVANCEFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -3;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.CM_STAFF
               WHERE STAFFPKID = paraSTAFFID AND STATUS = '10' AND (DELETED IS NULL OR DELETED = '0')) THEN
    SET SQLCODE_OUT_TMP = -4;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.MM_FUNCTION
               WHERE FUNCTIONCODE = paraFUNCTIONCODE ) THEN
    SET SQLCODE_OUT_TMP = -5;
END IF;

UPDATE DMS.MM_SECURITY
    SET STAFFID = paraSTAFFID
    , FUNCTIONCODE = paraFUNCTIONCODE
    , VIEWFLAG = paraVIEWFLAG
    , MODIFYFLAG = paraMODIFYFLAG
    , ADVANCEFLAG = paraADVANCEFLAG
    , INPUTTIME = CURRENT_TIMESTAMP
    , LOCALVERSION = LOCALVERSION + 1
WHERE (STAFFID = original_STAFFID) AND (FUNCTIONCODE = original_FUNCTIONCODE) AND (LOCALVERSION = original_LOCALVERSION);
SELECT LOCALVERSION INTO paraLOCALVERSION FROM MM_SECURITY
WHERE (STAFFID = paraSTAFFID) AND (FUNCTIONCODE = paraFUNCTIONCODE);
SELECT SQLCODE_OUT_TMP;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)
  OWNER TO postgres;

After modification:

-- Function: sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)

-- DROP FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer);

CREATE OR REPLACE FUNCTION sp_updatesecurity(
    OUT sqlcode_out integer,
    IN parastaffid integer,
    IN parafunctioncode character,
    IN paraviewflag character,
    IN paramodifyflag character,
    IN paraadvanceflag character,
    IN paralocalversion integer,
    IN original_staffid integer,
    IN original_functioncode character,
    IN original_localversion integer)
  RETURNS integer AS
$BODY$                                                              
DECLARE SQLCODE_OUT_TMP INT DEFAULT 0;
begin
IF (paraVIEWFLAG IS NULL) OR ((paraVIEWFLAG <> '1') AND (paraVIEWFLAG <> '0')) THEN
    set SQLCODE_OUT_TMP = -1;
END IF;

IF (paraMODIFYFLAG IS NULL) OR ((paraMODIFYFLAG <> '1') AND (paraMODIFYFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -2;
END IF;

IF (paraADVANCEFLAG IS NULL) OR ((paraADVANCEFLAG <> '1') AND (paraADVANCEFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -3;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.CM_STAFF
               WHERE STAFFPKID = paraSTAFFID AND STATUS = '10' AND (DELETED IS NULL OR DELETED = '0')) THEN
    SET SQLCODE_OUT_TMP = -4;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.MM_FUNCTION
               WHERE FUNCTIONCODE = paraFUNCTIONCODE ) THEN
    SET SQLCODE_OUT_TMP = -5;
END IF;

UPDATE DMS.MM_SECURITY
    SET STAFFID = paraSTAFFID
    , FUNCTIONCODE = paraFUNCTIONCODE
    , VIEWFLAG = paraVIEWFLAG
    , MODIFYFLAG = paraMODIFYFLAG
    , ADVANCEFLAG = paraADVANCEFLAG
    , INPUTTIME = CURRENT_TIMESTAMP
    , LOCALVERSION = LOCALVERSION + 1
WHERE (STAFFID = original_STAFFID) AND (FUNCTIONCODE = original_FUNCTIONCODE) AND (LOCALVERSION = original_LOCALVERSION);
SELECT LOCALVERSION INTO paraLOCALVERSION FROM MM_SECURITY
WHERE (STAFFID = paraSTAFFID) AND (FUNCTIONCODE = paraFUNCTIONCODE);
sqlcode_out = SQLCODE_OUT_TMP;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)
  OWNER TO postgres;
Источник

One of the nice things about MS Sql Server procedural language is that it doesn’t support multiple languages, T-SQL only, and as such it can facilitate on-the-fly testing of code logic:

declare @person_id int;

set @person_id = 1;

select * from person where person_id = @person_id;

On Postgres 9.0, though it already supports anonymous code block, it’s constrained by the fact that the anonymous code block is encapsulated in an invisible void function(Postgres supports many procedural languages, not just its own flavor of procedural language(PL/pgSQL), e.g. Python, Java, Perl, LOLCODE, etc), well you can think of it that way. Hence displaying query results on anonymous code block won’t work out-of-the-box on Postgres. This won’t work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 select * from person
 where person_id = _person_id;
end;
$$;

That will produce this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 8 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "inline_code_block" line 8 at SQL statement

Neither this will work:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 return query select * from person
 where person_id = _person_id;
end;
$$;

That code will produce this error:

ERROR:  cannot use RETURN QUERY in a non-SETOF function
LINE 13:  return query select * from person where person_id = _person...
          ^

********** Error **********

ERROR: cannot use RETURN QUERY in a non-SETOF function
SQL state: 42804
Character: 170

So if you want to test logic on-the-fly on Postgres, create temporary table on anonymous code block:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 drop table if exists _x;
 create temporary table _x as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

Wish for Postgres, make this happen, to make adhoc-y stuff happen :-) 

do returns table (person_id int, lastname text) as
$$
declare
 _person_id int; 
begin
 _person_id = 1;
 
 return query select person_id, lastname from person
 where person_id = _person_id;
end;
$$;

UPDATE: March 27, 2019

Made the code shorter:

do
$$
declare 
 _person_id int; 
begin
 _person_id = 1;

 create temporary table _x on commit drop
 as 
 select * from person
 where person_id = _person_id;
end;
$$;

select * from _x; -- the temporary table will continue to exist on individual Postgres session

With that code, there’s no need to explicitly drop the table. Postgres has an implicit commit whenever queries are executed. on commit drop ensures that the temporary table will not stay in session longer than necessary.

Found out on commit drop here: https://stackoverflow.com/questions/22929365/create-a-temp-table-if-not-exists-for-use-into-a-custom-procedure/22932917#22932917

Источник

Понравилась статья? Поделить с друзьями:
  • Sql error 18456 severity 14 state 38
  • Sql error 42501 ошибка не удалось открыть файл
  • Sql error 42501 error permission denied for schema public позиция 14
  • Sql error 17053
  • Sql error 42501 error permission denied for relation