PostgreSQL - select data from stored procedure

Question

I working with creating new SP which will insert or do a select and return ID of the founded/inserted record. Parameter ID is inout parameter of stored procedure.

I was helped with https://stackoverflow.com/questions/57476508/invoke-stored-procedure-and-return-id

I have prepared table , stored procedure and select statement: https://dbfiddle.uk/XHPxeW_b

but getting error, i nearly come to end of the internet and did not found a proper solution. Error:

     do $$
  declare _id int;
    Begin
    call InsertSelectCategory('Add-Hoc', _id);
    select _id
    end; 
      $$ LANGUAGE plpgsql;
    
    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 6 at SQL statement

Thanks for helping me.

Unrelated, but: your INSERT could be made more efficient (and safe for concurrent inserts), by defining a unique constraint on name and using INSERT ... ON CONFLICT. Also: you don't need to make id unique if it's already the the primary key. dbfiddle.uk/a5dTqxN6 — user1822
– user1822, Commented Jan 26, 2023 at 9:41

user1822 · Accepted Answer · 2023-01-26 09:34:45Z

You can't just put a "SELECT" into a procedure and expect the output to show up "somewhere". If you want to print something, use RAISE inside your DO block:

do $$
declare 
  _id int;
begin
  call InsertSelectCategory('Add-Hoc', _id);
  raise notice 'New ID: %', _id;  
end; 
$$ 
LANGUAGE plpgsql;

However, procedures aren't meant to return results, using a function is a better choice.

CREATE OR REPLACE function InsertSelectCategory( _name varchar) 
  returns integer
  LANGUAGE sql
AS $procedure$
with vals as (
  select _name as name
)
insert into Category (name)
select v.name
from vals as v
where not exists (select * from Category as c where c.name = v.name)
returning id;
$procedure$
;

Then use it like this:

do $$
declare 
  _id int;
begin
  _id := InsertSelectCategory('Add-Hoc');
  raise notice 'New ID: %', _id;
end; 
$$ 
LANGUAGE plpgsql;

Stack Exchange Network

PostgreSQL - select data from stored procedure

1 Answer 1

Hot Network Questions

PostgreSQL - select data from stored procedure

1 Answer 1

Related

Hot Network Questions