query has no destination for result data in POSTGRESQL for stored procedure

Question

having a slight issue here, trying to call this function, but getting this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

used perform but that is not what I need. I don't understand the error message, do i need to return a table or something?

create or replace procedure myProc(colkey varchar, colvalue varchar)
language plpgsql
as $$
declare
old varchar(250);
keyId bigint;

begin

select @keyid= id
from conkey
where conkey.key like colkey
limit 1;



if not exists (select * from ConValu where conkeyId= @keyid and appId is null) THEN 

else 

select @old_value_for_update = value 
from ConValu
where conkeyId= @conkeyId and appId is null
limit 1; 

update ConValu  set Value = colvalue where ConkeyId= @conkeyID and appId is null;

end if;

end; $$

As documented in the manual parameters are not prefixed with @ in PL/pgSQL. When you run a query you need to store the result somewhere — user330315
– user330315, Commented Jun 21, 2021 at 12:17

sticky bit · Accepted Answer · 2021-06-21 12:39:19Z

You seem to try to use T-SQL syntax in PL/pgSQL. But in PL/pgSQL variables aren't and cannot be prefixed with @. But you can and should prefix them with an underscore (_) (or some other valid prefix) to avoid name clashes with column names which can result in pretty hard to find errors.

Either way they need to be declared in the DECLARE block. You also missed that for that variables now beginning with @.

And they cannot be filled in a query with the <variable name> = <column expression> syntax.

You need to either use SELECT ... INTO ... or a subquery and an assignment.

Like

SELECT value 
       INTO _old_value_for_update
       FROM convalu
       WHERE conkeyid = _conkeyid
             AND appid IS NULL
       LIMIT 1;

or:

_old_value_for_update := (SELECT value 
                                 FROM convalu
                                 WHERE conkeyid = _conkeyid
                                       AND appid IS NULL
                                 LIMIT 1);

Oh and one thing I already forgot: If you use LIMIT without an ORDER BY the rows chosen are expected to be random. I couldn't introduce an ORDER BY because I don't know what could make sense in your case.

Collectives™ on Stack Overflow

query has no destination for result data in POSTGRESQL for stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related