plpgsql TABLE patch - Mailing list pgsql-hackers

To review, Pavel Stehule submitted a proposal and patch to add support
for "table functions" a few months back:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
http://archives.postgresql.org/pgsql-patches/2007-05/msg00054.php

Pavel proposed two basically independent features:

(1) RETURN TABLE syntax sugar for PL/PgSQL

This allows you to return the result of evaluating a SELECT query as the
result of a SETOF pl/pgsql function. I don't like the RETURN TABLE
syntax, because TABLE (...) is defined as part of SQL (6.39 in SQL:2003,
as one of the variants of <multiset value constructor>). If we're going
to implement TABLE (...), the right place to do that is in the Postgres
backend proper (presumably as part of a larger effort to implement
multisets). Therefore I'd like to rename the PL/PgSQL syntax sugar to
RETURN QUERY (I'm open to other suggestions for the name).

Another question is whether it is sensible to allow RETURN QUERY and
RETURN NEXT to be combined in a single function. That is, whether RETURN
QUERY should be more like RETURN (and return from the function
immediately), or more like RETURN NEXT (just append a result set to the
SRF's tuplestore and continue evaluating the function). I think making
it behave more like RETURN NEXT would be more flexible, but perhaps it
would be confusing for users to see a "RETURN QUERY" statement that does
not in fact return control to the caller of the function... (Is RETURN
NEXT QUERY too ugly a name?)

(2) RETURNS TABLE (...) syntax sugar for CREATE FUNCTION

This lets you write "CREATE FUNCTION ... RETURNS TABLE (x int, y int)"
as essentially syntax sugar for OUT parameters. The syntax is specified
by SQL:2003, so I think this feature is worth implementing.

When Pavel proposed this, the sticking point is whether RETURNS TABLE
(...) is truly just syntax sugar for OUT parameters, or whether it
should behave differently with regard to variables with the same name in
the function body:[1]

CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (cust_id int) AS
$$
BEGIN RETURN QUERY (SELECT cust_id FROM tab WHERE some = arg);
END; $$ LANGUAGE plpgsql;

would cause a name collision if RETURNS TABLE were treated as syntax
sugar for OUT parameters. Pavel's patch fixes this by introducing a new
proargmode for RETURNS TABLE parameters. Tom objected to this on the
grounds that it could break user code that examines pg_proc.proargmode,
but I'm inclined to think that it is worth the trouble to avoid what
could be a common source of confusion.

Comments welcome; I'll submit revised patches for these features
shortly.

-Neil

[1] example stolen shamelessly from a prior mail from Pavel