Re: plpgsql TABLE patch

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Neil Conway wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +