Re: [PATCHES] Table function support
Hello I searched some notes about this topic. I didn't find any usefull sample. Lot of samples are about external stored procedures and others about using table expression like create function foo(i1) returns table (a1 int) as return table(select a1 from tab) isn't clear if table attributes are related to output variables, but nobody join it together. SQL/PSM sample: create function accounts_of (customer_name char(20)) returns table ( account_number char(10), branch_name char(15) balance numeric(12,2)) return table (select account_number, branch_name, balance from account A where exists ( select * from depositor D where D.customer_name = accounts_of.customer_name and D.account_number = A.account_number )) correct calling of it is: select * from table (accounts_of (Smith)) next sample: CREATE FUNCTION filmtyp (art CHAR(2)) RETURNS TABLE (titel VARCHAR(75), jahr INTEGER) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT titel, jahr FROM film WHERE film.art = filmtyp.art Table functions are named as parametrised views too. I don't thing using OUT variables is good idea, because you will have problems with colum's names, which is problem for plpgsql. http://www.wiscorp.com/SQL2003Features.pdf http://wwwdvs.informatik.uni-kl.de/courses/NEDM/SS2004/Vorlesungsunterlagen/NEDM.Chapter.03.User-defined_Routines_and_Object_Behavior.pdf Regards Pavel Stehule From: Tom Lane <[EMAIL PROTECTED]> To: "Pavel Stehule" <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [PATCHES] Table function support Date: Tue, 10 Apr 2007 18:17:14 -0400 "Pavel Stehule" <[EMAIL PROTECTED]> 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 _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Table function support
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Table function support
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Table function support
"Pavel Stehule" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Table function support
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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] 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:
T326Table 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/
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-02-11 12:01:48.0 +0100
--- ./doc/src/sgml/plpgsql.sgml 2007-02-11 18:54:16.0 +0100
***
*** 1567,1575
RETURN
!
! RETURN expression;
!
RETURN with an expression terminates the
--- 1567,1580
RETURN
!
!
! RETURN expression;
!
!
! RETURN TABLE ( query );
!
!
RETURN with an expression terminates the
***
*** 1594,1599
--- 1599,1613
+ If your function returns a set, you can use table expression.
+ An query is evaluated and result set
+ is returned to the caller. You cannot mix RETURN TABLE
+ and RETURN NEXT statements in one function.
+ Performance might be poor, if result set is very large. The reason
+ is described below.
+
+
+
If you declared the function to return void, a
RETURN statement can be used to exit the function
early; but do not write an expression following
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-02-11 09:55:06.0 +0100
--- ./doc/src/sgml/ref/create_function.sgml 2007-02-11 10:28:08.0 +0100
***
*** 21,27
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
! [ RETURNS rettype ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
! [ RETURNS rettype
! | RETURNS TABLE ( colname coltype [, ...] ) ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 387,392
--- 388,427
+
+ colname
+
+
+
+The name of an output table column.
+
+
+
+
+
+ coltype
+
+
+
+The data type(s) of output table column.
+
+
+Depending on the implementation language it might also be allowed
+to specify pseudotypes such as cstring.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+
+
+The type of a column is referenced by writing
+tablename.columnname%TYPE.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+
+
+
+
***
*** 516,522
A CREATE FUNCTION command is defined in SQL:1999 and later.
The PostgreSQL version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages.
--- 551,557
A CREATE FUNCTION command is defined in SQL:1999 and later.
The PostgreSQL version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages. TABLE functions are defined in SQL:2003.
*** ./doc/src/sgml/xfunc.sgml.orig 2007-02-11 09:18:54.0 +0100
--- ./doc/src/sgml/xfunc.sgml 2007-02-11 09:45:11.0 +0100
***
*** 102,107
--- 102,115
+ TABLEfunctionAn SQL function can
+ declared to return table specified by function's retun table as
+ TABLE(somecolumns). In this case
+ all rows of the last query's result are returned. Furteher details
+ appear bellow.
+
+
+
The body of an SQL function must be a list of SQL
statements separated by semicolons. A semicolon after the last
statement is optional. Unless the function is declared to return
***
*** 713,718
--- 721,784
+ SQL Table Functions
+
+
+ When an SQL function is declared as returning
+ TABLE(somecolumns), the function's final
+ SELECT query is executed to completion, a
