If you want to RETURN
a table define that TABLE
's columns in the return statement. You then issue RETURN QUERY
when returning.
CREATE OR REPLACE FUNCTION something(mytext text)
RETURNS TABLE(aname text, anumber int)
LANGUAGE plpgsql
AS $body$
BEGIN
return query select mytext::text, 100::int;
END;
$body$;
I explicitly cast mytext
and 100
to text
and int
to be sure they match the function's return type.
If you don't want to define your functions table return type, but use an existing table, you can use SETOF
.
CREATE OR REPLACE FUNCTION something(mytext text)
RETURNS SETOF mytablename
LANGUAGE plpgsql
AS $body$
BEGIN
return query select * from mytablename;
END;
$body$;