From the PostgreSQL documentation here:
There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE(columns). ... This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.
This sounds as if
RETURNS TABLE is a newer, more portable style to return multiple rows. But I am not sure if the two syntaxes are equivalent.
I was wondering if we can actually use
RETURNS TABLE to replace
In particular, one case I haven't figured out is: if we have an existing table
foo and its associated composite type, how can we use it in
Using the example for the above link, can the following be rewritten using
CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL;
So far, I tried to use
RETURNS TABLE (foo.*) and
RETURNS TABLE (foo), which didn't work.