Table function support

Hello

this patch allows using SQL2003 syntax for set returning functions. It is
based on using new type of argmode - PROARGMODE_TABLE.

Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php

Sample:

CREATE FUNCTION foof(a int)
RETURNS TABLE(a int, b int) AS
$$ SELECT x, y FROM Foo WHERE x < a $$ LANGUAGE sql;

CREATE FUNCTION fooff(a int)
RETURNS TABLE(a int, b int) AS $$
BEGIN
RETURN TABLE(SELECT * FRON Foo WHERE x < a);
END; $$ LANGUAGE plpgsql;

This patch enhance plpgsql stmt return too (table expression support).

Conformance with SQL2003:
T326 Table functions

Description: SIGMOD Record, Vol. 33, No. 1, March 2004

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

Your patch has been added to the PostgreSQL unapplied patches list at:

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

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

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

Pavel Stehule wrote:
> Hello
>
> this patch allows using SQL2003 syntax for set returning functions. It is
> based on using new type of argmode - PROARGMODE_TABLE.
>
> Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
>
> Sample:
>
> CREATE FUNCTION foof(a int)
> RETURNS TABLE(a int, b int) AS
> $$ SELECT x, y FROM Foo WHERE x < a $$ LANGUAGE sql;
>
> CREATE FUNCTION fooff(a int)
> RETURNS TABLE(a int, b int) AS $$
> BEGIN
> RETURN TABLE(SELECT * FRON Foo WHERE x < a);
> END; $$ LANGUAGE plpgsql;
>
> This patch enhance plpgsql stmt return too (table expression support).
>
> Conformance with SQL2003:
> T326 Table functions
>
> Description: SIGMOD Record, Vol. 33, No. 1, March 2004
>
> Regards
> Pavel Stehule
>
> _________________________________________________________________
> Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
> http://messenger.msn.cz/

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

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

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

"Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> writes:
> this patch allows using SQL2003 syntax for set returning functions. It is
> based on using new type of argmode - PROARGMODE_TABLE.

I've been looking at this, and my feeling is that we should drop the
PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int)
as exactly equivalent to RETURNS SETOF RECORD with x and y treated as
OUT parameters. There isn't any advantage to distinguishing the cases
that outweighs breaking client code that looks at pg_proc.proargmodes.
I don't believe that the SQL spec prevents us from exposing those
parameter names to PL functions, especially since none of our PLs are
in the standard at all.

regards, tom lane

>I've been looking at this, and my feeling is that we should drop the
>PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int)
>as exactly equivalent to RETURNS SETOF RECORD with x and y treated as
>OUT parameters. There isn't any advantage to distinguishing the cases
>that outweighs breaking client code that looks at pg_proc.proargmodes.
>I don't believe that the SQL spec prevents us from exposing those
>parameter names to PL functions, especially since none of our PLs are
>in the standard at all.
>

Reason for PROARGMODE_TABLE was protection before name's collision, and x,
and y are table attributies (not variables) and then we are protected before
collision. It's shortcut for

create function foo() returns setof record as ...
select * from foo() as (x int, y int);

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

>I've been looking at this, and my feeling is that we should drop the
>PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int)
>as exactly equivalent to RETURNS SETOF RECORD with x and y treated as
>OUT parameters. There isn't any advantage to distinguishing the cases
>that outweighs breaking client code that looks at pg_proc.proargmodes.
>I don't believe that the SQL spec prevents us from exposing those
>parameter names to PL functions, especially since none of our PLs are
>in the standard at all.
>

Reason for PROARGMODE_TABLE was protection before name's collision, and x,
and y are table attributies (not variables) and then we are protected before
collision. It's shortcut for

create function foo() returns setof record as ...
select * from foo() as (x int, y int);

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/