Thread: Proposal: TABLE functions
Hello, Currently PostgreSQL support set returning functions. ANSI SQL 2003 goes with new type of functions - table functions. With this syntax CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... ) PostgreSQL equal statements are: CREATE TYPE tmptype AS (c1 t1, ...) CREATE FUNCTION ... RETURNS SETOF tmptype AS ... All necessary infrastructure is done. Implementation needs propably only small changes in parser. This feature doesn't need any changes in SQL functions. I expect so they will be more readable and consistent. CREATE OR REPLACE FUNCTION foo(f integer) RETURNS TABLE(a int, b int) AS $$ SELECT a, b FROM FROM footab WHERE a < f; $$ LANGUAGE sql; plpgpsql RETURN have to be enhanced for table expressions. CREATE OR REPLACE FUNCTION foo(f integer) RETURNS TABLE(a int, b int) AS -- they are not variables! $$ BEGIN RETURN TABLE(SELECT a, b -- it's secure, a,b are not variables FROM footab WHERE a < f); END; $$ LANGUAGE plpgsql; RETURN NEXT can be used without changes. This feature doesn't allow combination of RETURN TABLE and RETURN NEXT statement. Table functions can have only IN arguments. Advances: * conformance with ansi sql 2003 * less propability of colision varnames and colnames Regards Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
On Tue, 6 Feb 2007, Pavel Stehule wrote: > Hello, > > Currently PostgreSQL support set returning functions. > > ANSI SQL 2003 goes with new type of functions - table functions. With this > syntax > > CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... ) > > PostgreSQL equal statements are: > > CREATE TYPE tmptype AS (c1 t1, ...) > CREATE FUNCTION ... RETURNS SETOF tmptype AS ... or you can do CREATE FUNCTION foo(OUT c1 t1, OUT ...) RETURNS SETOF record AS ... But I think this would be nice, I think the OUT parameters make less sense than saying RETURNS TABLE(...). But what about functions not returning SETOF? -- The Schwine-Kitzenger Institute study of 47 men over the age of 100 showed that all had these things in common: (1) They all had moderate appetites.(2) They all came from middle class homes(3) All but two of them were dead.
Pavel Stehule wrote: > Hello, > > Currently PostgreSQL support set returning functions. > > ANSI SQL 2003 goes with new type of functions - table functions. With > this syntax > > CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... ) > Yeah this should be pretty easy because a table is just a composite type. You can already do this: CREATE TABLE foo (id bigint, first_name text); CREATE FUNCTION foo() RETURNS SET OF foo... > PostgreSQL equal statements are: > > CREATE TYPE tmptype AS (c1 t1, ...) > CREATE FUNCTION ... RETURNS SETOF tmptype AS ... > > All necessary infrastructure is done. Implementation needs propably only > small changes in parser. > > This feature doesn't need any changes in SQL functions. I expect so they > will be more readable and consistent. > > CREATE OR REPLACE FUNCTION foo(f integer) > RETURNS TABLE(a int, b int) AS > $$ > SELECT a, b FROM > FROM footab > WHERE a < f; > $$ LANGUAGE sql; > > plpgpsql RETURN have to be enhanced for table expressions. > > CREATE OR REPLACE FUNCTION foo(f integer) > RETURNS TABLE(a int, b int) AS -- they are not variables! > $$ > BEGIN > RETURN TABLE(SELECT a, b -- it's secure, a,b are not variables > FROM footab > WHERE a < f); > END; > $$ LANGUAGE plpgsql; > > RETURN NEXT can be used without changes. This feature doesn't allow > combination of RETURN TABLE and RETURN NEXT statement. > > Table functions can have only IN arguments. > > Advances: > * conformance with ansi sql 2003 > * less propability of colision varnames and colnames > > Regards > Pavel Stehule > > _________________________________________________________________ > Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. > http://messenger.msn.cz/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
> > Hello, > > > > Currently PostgreSQL support set returning functions. > > > > ANSI SQL 2003 goes with new type of functions - table functions. With >this > > syntax > > > > CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... ) > > > > PostgreSQL equal statements are: > > > > CREATE TYPE tmptype AS (c1 t1, ...) > > CREATE FUNCTION ... RETURNS SETOF tmptype AS ... > >or you can do >CREATE FUNCTION foo(OUT c1 t1, OUT ...) RETURNS SETOF record AS ... > >But I think this would be nice, I think the OUT parameters make less sense >than saying RETURNS TABLE(...). But what about functions not returning >SETOF? > This feature doesn't change current behaviour. And using TABLE function means using SETOF. Regards Pavel Stehule _________________________________________________________________ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
On Tue, 2007-02-06 at 23:43 +0100, Pavel Stehule wrote: > ANSI SQL 2003 goes with new type of functions - table functions. With this > syntax ... > All necessary infrastructure is done. Implementation needs propably only > small changes in parser. ... > * conformance with ansi sql 2003 Sounds good to me. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Hello, it can by more simple than I though. I need only one flag, and if its true then I don't create language variables for OUT params. But I need one next column in pg_proc. Currently a lot of columns in pg_proc is bool. What about one binary columns for other options? I hope so next versions can support autonomous transaction, which need flag too. Regards Pavel Stehule > >Pavel Stehule wrote: > > Hello, > > > > Currently PostgreSQL support set returning functions. > > > > ANSI SQL 2003 goes with new type of functions - table functions. With > > this syntax > > > > CREATE FUNCTION foo() RETURNS TABLE (c1 t1, ... ) > > > >Yeah this should be pretty easy because a table is just a composite >type. You can already do this: > >CREATE TABLE foo (id bigint, first_name text); > >CREATE FUNCTION foo() RETURNS SET OF foo... > > > PostgreSQL equal statements are: > > > > CREATE TYPE tmptype AS (c1 t1, ...) > > CREATE FUNCTION ... RETURNS SETOF tmptype AS ... > > > > All necessary infrastructure is done. Implementation needs propably only > > small changes in parser. > > > > This feature doesn't need any changes in SQL functions. I expect so they > > will be more readable and consistent. > > > > CREATE OR REPLACE FUNCTION foo(f integer) > > RETURNS TABLE(a int, b int) AS > > $$ > > SELECT a, b FROM > > FROM footab > > WHERE a < f; > > $$ LANGUAGE sql; > > > > plpgpsql RETURN have to be enhanced for table expressions. > > > > CREATE OR REPLACE FUNCTION foo(f integer) > > RETURNS TABLE(a int, b int) AS -- they are not variables! > > $$ > > BEGIN > > RETURN TABLE(SELECT a, b -- it's secure, a,b are not >variables > > FROM footab > > WHERE a < f); > > END; > > $$ LANGUAGE plpgsql; > > > > RETURN NEXT can be used without changes. This feature doesn't allow > > combination of RETURN TABLE and RETURN NEXT statement. > > > > Table functions can have only IN arguments. > > > > Advances: > > * conformance with ansi sql 2003 > > * less propability of colision varnames and colnames > > > > Regards > > Pavel Stehule > > > > _________________________________________________________________ > > Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. > > http://messenger.msn.cz/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > >-- > > === The PostgreSQL Company: Command Prompt, Inc. === >Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 >Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > >Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate >PostgreSQL Replication: http://www.commandprompt.com/products/ > _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> it can by more simple than I though. I need only one flag, and if its true
> then I don't create language variables for OUT params. But I need one next
> column in pg_proc.
I thought you said this was just syntactic sugar for capabilities we
already had?
> Currently a lot of columns in pg_proc is bool. What about one binary columns
> for other options? I hope so next versions can support autonomous
> transaction, which need flag too.
I think stored procedures of that sort aren't functions at all, and
probably don't belong in pg_proc.
regards, tom lane
> >"Pavel Stehule" <pavel.stehule@hotmail.com> writes: > > it can by more simple than I though. I need only one flag, and if its >true > > then I don't create language variables for OUT params. But I need one >next > > column in pg_proc. > >I thought you said this was just syntactic sugar for capabilities we >already had? > My mistake. I am sorry. I have to store somewhere flag. One bit, which signalise "don't use OUT arguments as function's parameters". Other is only game in parser. > > Currently a lot of columns in pg_proc is bool. What about one binary >columns > > for other options? I hope so next versions can support autonomous > > transaction, which need flag too. > >I think stored procedures of that sort aren't functions at all, and >probably don't belong in pg_proc. > Why not? Some people use "ugly" implementation of it in plperlu and DBI. pg_proc and related infrastructure works well. It miss only little bit bigger adaptability. I thing so can be interesting one general option byte, and one byte reservated for language handlers. Regards Pavel Stehule _________________________________________________________________ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
>> I thought you said this was just syntactic sugar for capabilities we
>> already had?
> My mistake. I am sorry. I have to store somewhere flag. One bit, which
> signalise "don't use OUT arguments as function's parameters".
Huh? What exactly is the meaning of the arguments then?
It sounds to me like this might be better thought of as a new
proargmode value, but I'm quite unsure what you're talking about ...
regards, tom lane
> >> I thought you said this was just syntactic sugar for capabilities we > >> already had? > > > My mistake. I am sorry. I have to store somewhere flag. One bit, which > > signalise "don't use OUT arguments as function's parameters". > >Huh? What exactly is the meaning of the arguments then? > >It sounds to me like this might be better thought of as a new >proargmode value, but I'm quite unsure what you're talking about ... > My basic idea was: CREATE FUNCTION aaa(IN a1, OUT a, OUT b) RETURNS SETOF RECORD AS $$ .. is similar CREATE FUNCTION aaa(IN a1) RETURNS SETOF RECORD AS $$ from executor perspective there isn't any difference. But PL languages have to create only IN variables. It's protection before identifier's name colision. With special flag I don't need any changes in executor. And small change in PL compile rutines. Special proargmode can be solution too. I don't need new column in pg_proc, but have to modify executor and need more changes in output rutines in PL. I'll go on the way to spec. proargmode. It's good idea. Thank You Pavel Stehule _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/