plpgsql TABLE patch

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