Thread: 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@samurai.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@samurai.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@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +