plpgsql TABLE patch

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

Neil Conway <neilc(at)samurai(dot)com> 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
> (2) RETURNS TABLE (...) syntax sugar for CREATE FUNCTION

I believe that (1) is now committed (renamed to RETURN QUERY),
but what is the status of (2)?

Personally I won't cry if this doesn't make it into 8.3, particularly
since there was some disagreement about it. But if you intend to make
it happen, the days grow short. Very short.

regards, tom lane

On Tue, 2007-25-09 at 22:15 -0400, Tom Lane wrote:
> I believe that (1) is now committed (renamed to RETURN QUERY),
> but what is the status of (2)?
>
> Personally I won't cry if this doesn't make it into 8.3, particularly
> since there was some disagreement about it. But if you intend to make
> it happen, the days grow short.

Sorry, my day job is currently taking up all my spare cycles :( So I
don't think I'll get a chance to wrap this up for 8.3.

My recollection is that the patch was okay as far as it went, but I'm
hesitant to add yet another alternative to the already complex set of
choices for returning composite types and sets from functions. If we
just make TABLE() syntax sugar for the existing OUT function stuff we
would avoid at least some of that complexity, but Pavel still prefers a
distinct proargmode, last I heard.

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> On Tue, 2007-25-09 at 22:15 -0400, Tom Lane wrote:
>> Personally I won't cry if this doesn't make it into 8.3, particularly
>> since there was some disagreement about it. But if you intend to make
>> it happen, the days grow short.

> My recollection is that the patch was okay as far as it went, but I'm
> hesitant to add yet another alternative to the already complex set of
> choices for returning composite types and sets from functions. If we
> just make TABLE() syntax sugar for the existing OUT function stuff we
> would avoid at least some of that complexity, but Pavel still prefers a
> distinct proargmode, last I heard.

OK, let's just hold this item for 8.4 then. It's way past time to get
8.3 out the door ...

regards, tom lane

>
> Sorry, my day job is currently taking up all my spare cycles :( So I
> don't think I'll get a chance to wrap this up for 8.3.
>
> My recollection is that the patch was okay as far as it went, but I'm
> hesitant to add yet another alternative to the already complex set of
> choices for returning composite types and sets from functions. If we
> just make TABLE() syntax sugar for the existing OUT function stuff we
> would avoid at least some of that complexity, but Pavel still prefers a
> distinct proargmode, last I heard.
>

Method isn't important for me - important is semantic. If you
implement TABLE like shortcut to current OUT variables, you have to
have some implicit variables inside function and it is in
contradiction with standard. That's all. So TABLE functions without
SQL/PSM isn't tragedy :), but if we implement SQL/PSM cleanly then we
need table's functions. It's only one way for output set, which is
specified by standard.

Regards
Pavel Stehule

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. +