Postgresql - Stored Procedure that returns a table result set

Question

Im new to PostgreSQL v.13 and want to create a Stored Procedure that will receive an integer value and then return the results from a table.

I have this:

create or replace procedure public.sp_message_template_get(
   templateid int
)  language plpgsql AS $$
   
      
begin
    -- subtracting the amount from the sender's account 
 select * from public.message_template;

    --commit;
end;
$$

Then I try to call it by using:

call public.sp_message_template_get(1);

And I get the following 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 sp_message_template_get(integer) line 6 at SQL statement
SQL state: 42601

Any clue?

Thanks

Stored procedures cannot return things. If you want to do that use a function. See Returning 43.6.1.2. RETURN NEXT and RETURN QUERY. FYI, you got the error message because you ran a SELECT and did not capture the results. To do that you need to use PERFORM 43.5.2. Executing SQL Commands — Adrian Klaver
– Adrian Klaver, Commented Nov 7, 2021 at 17:01
I was confused because in MS SQL Server you normally use SP to return values, so in PostgreSQL you have to use functions instead? So what’s the purpose of SP? — VAAA
– VAAA, Commented Nov 7, 2021 at 17:08
@VAAA Well, Postgres is different then SQL Server, and in Postgres you need to use a function if to return something. You need to migrate your mindset too — user330315
– user330315, Commented Nov 7, 2021 at 17:15
See Procedures. The primary benefit of a procedure is that you can do transaction management inside it. For most things you probably want to use a function. — Adrian Klaver
– Adrian Klaver, Commented Nov 7, 2021 at 17:28

Stefanov.sm · Accepted Answer · 2021-11-07 17:16:26Z

1

Create a sql function.

create or replace function message_template_get(templateid integer)
returns setof message_template language sql as
$$
  -- subtracting the amount from the sender's account (sql query)
  select * from public.message_template;
$$;

If there is a reason to use plpgsql language then use return query.

create or replace function message_template_get(templateid integer)
returns setof message_template language plpgsql as
$$
begin
  -- subtracting the amount from the sender's account
  return query select * from public.message_template;
  -- maybe other statements here
end;
$$;

Please note that commit is not necessary and illegal in a function body. More than one return query or return next statements are possible.

edited Nov 7, 2021 at 17:16

answered Nov 7, 2021 at 17:05

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - Stored Procedure that returns a table result set

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