3

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 RETURNS SETOF?

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 RETURNS TABLE?

Using the example for the above link, can the following be rewritten using RETURNS TABLE:

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.

2

The different modes are interchangeable in some circumstances, but are different. What RETURNS TABLE is good for is described in the sentence you removed from your quote above:

This is equivalent to using one or more OUT parameters plus marking the function as returning SETOF record (or SETOF a single output parameter's type, as appropriate).

This means that CREATE FUNCTION ... RETURNS TABLE is intended to specify a custom return type, i.e. it is equivalent to CREATE TYPE xxx_t AS ([fields...,]); CREATE FUNCTION xxx ... RETURNS SETOF xxx_t, and also CREATE FUNCTION xxx ([in params...,] out [fields...,]) RETURNS SETOF RECORD.

I.e., a function cannot return a table of some other table's type: use RETURNS SETOF directly for that (as you did in the snippet in your question).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.