Returning Plpgsql tables and sets of tables

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$;
postgresql

Edit on github
comments powered by Disqus
Click me