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.
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();
