CODEWITHSUNDEEP
postgresqlstored-procedures
Krunal Pandya
Krunal Pandya

Reputation: 234

Return table in stored procedure postgresql

I want to return the whole table with stored procedure not function

my code is:

CREATE OR REPLACE PROCEDURE public.test()

RETURN TABLE (
     id   numeric
   , test varchar
   ) AS  -- Results  -- remove this
$func$

BEGIN
    RETURN QUERY
    SELECT * 
    FROM public.test

END
$func$ LANGUAGE plpgsql;

but its not works.

Upvotes: 2

Views: 29523

Answers (1)

user330315
user330315

Reputation:

Stored procedures aren't meant to return anything, use a function. And you don't need PL/pgSQL for that either:

create or replace FUNCTION public.test()
  returns TABLE (id   numeric, test varchar) 
AS
$func$
  SELECT * 
  FROM public.test;
$func$ 
LANGUAGE sql;

As you return all columns of one table, you can also use returns setof

create or replace FUNCTION public.test()
  returns setof public.test
AS
$func$
  SELECT * 
  FROM public.test;
$func$ 
LANGUAGE sql;

Then use it like a table:

select *
from test();

Upvotes: 11

Related Questions