CODEWITHSUNDEEP
postgresql
VAAA
VAAA

Reputation: 15049

Postgresql - Stored Procedure that returns a table result set

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

Upvotes: 0

Views: 2336

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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.

Upvotes: 1

Related Questions