Re: RETURN QUERY in PL/PgSQL?

On Tue, 2007-04-24 at 07:58 +0200, Pavel Stehule wrote:
> It is RETURN TABLE(SQL) via ANSI SQL 2003

I think there are two basically orthogonal features in the patch: the
"RETURNS TABLE" addition to CREATE FUNCTION, and the "RETURN TABLE"
statement in PL/PgSQL. The former is specified by the SQL standard and
is applicable to all PLs, while the latter is syntax sugar for PL/PgSQL.
I think it would make sense to split the patch into two separate
patches, one for each feature.

I'm inclined to agree with Tom that adding PROARGMODE_TABLE isn't worth
the trouble: making RETURNS TABLE(...) equivalent to RETURNS SETOF
RECORD with OUT parameters strikes me as more elegant. I didn't really
understand the "name collision" argument you made earlier[1]; can you
elaborate?

Another question is how RETURN NEXT and RETURN TABLE should interact (in
PL/PgSQL). I think the two sensible choices are to either disallow a
function from using both statements (which is what the patch currently
does), or allow both statements to be used, and have RETURN TABLE *not*
return from the function -- both RETURN TABLE and RETURN NEXT would
append results to the function's result tuplestore. The latter seems
more flexible.

Do we need the extra set of parentheses in RETURN TABLE? To use one of
your earlier examples:

CREATE FUNCTION fooff(a int)
RETURNS TABLE(a int, b int) AS $$
BEGIN
RETURN TABLE(SELECT * FROM Foo WHERE x < a);
END; $$ LANGUAGE plpgsql;

"RETURN TABLE SELECT ... ;" should be sufficient to allow correct
parsing, and is more consistent with the lack of parentheses in the
other RETURN variants.

-Neil

[1] http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php