proposal: table functions and plpgsql
Hello
I am returning back to my patch and older proposal
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php .
Some work did Neil Conway
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00501.php and
he commited half of this patch - RETURN QUERY part.
Problematic part of my patch is implementation. Tom Lane proposal
implenation RETURNS TABLE only as syntactic sugar for RETURNS SETOF
RECORD. This is not comaptible with potential implementation, because
it adds some default variables. My solution was special argmode, so I
was able don't create default variables for output. My solution wasn't
best too. It was ugly for current plpgsql where is often used RETURN
NEXT statement (PSM doesn't know similar statement). I unlike default
variables - it simply way to variables and column names collision.
I propose following syntax for plpgsql:
CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
DECLARE r foo; -- same name as function, this type has local visibility
BEGIN
FOR i IN 1..m LOOP
r.a := i; r.b := i + 1;
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.
What do you thing about?
Regards
Pavel Stehule
On Wed, 2008-05-21 at 18:12 +0200, Pavel Stehule wrote:
Hello
...
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.
How is this different from using OUT params and RETURNS SETOF RECORD ?
hannu=# CREATE FUNCTION outsetof2py(n integer, OUT i integer, OUT j
integer) RETURNS SETOF record
AS $$
for i in xrange(n):
yield {'i':i,'j':i*i}
$$
LANGUAGE plpythonu;
CREATE FUNCTION
hannu=# select * from outsetof2py(3);
i | j
---+---
0 | 0
1 | 1
2 | 4
(3 rows)
btw, this currently works for pl/python only in my local copy (still
testing for bugs), but similar declaration works fine for pl/pgsql
--------------
Hannu
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
*) seems cleaner to separate in/out variables so add/drop function are
symmetric.
Also,
What about:
CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
-- DECLARE r foo; -- make alias of r to foo optional
BEGIN
FOR i IN 1..m LOOP
foo.a := i; foo.b := i + 1;
[...]
or
RETURNS TABLE r(a integer, b integer) AS $$
merlin
On Wed, 2008-05-21 at 13:31 -0400, Merlin Moncure wrote:
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
As I'm currently working on updating another pl (pl/python), I'd like to
know how will this affect get_call_result_type() defined in funcapi.h.
will there be an extra parameter for record name there ?
*) seems cleaner to separate in/out variables so add/drop function are
symmetric.
they are kind of symmetric already :)
hannu=# drop function outsetof2py(n integer, OUT i integer, OUT j
integer);
DROP FUNCTION
Also,
What about:CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
-- DECLARE r foo; -- make alias of r to foo optional
BEGIN
FOR i IN 1..m LOOP
foo.a := i; foo.b := i + 1;
[...]or
RETURNS TABLE r(a integer, b integer) AS $$
rather "..FUNCTION foo(...) ... RETURNS TABLE r(..." as else it will be
hard to do recursive functions.
Show quoted text
merlin
2008/5/21 Hannu Krosing <hannu@krosing.net>:
On Wed, 2008-05-21 at 18:12 +0200, Pavel Stehule wrote:
Hello
...
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
little - it's ANSI SQL compatible and I hope, it's less cryptic for beginers.
hannu=# CREATE FUNCTION outsetof2py(n integer, OUT i integer, OUT j
integer) RETURNS SETOF record
AS $$
for i in xrange(n):
yield {'i':i,'j':i*i}
$$
LANGUAGE plpythonu;
CREATE FUNCTION
hannu=# select * from outsetof2py(3);
i | j
---+---
0 | 0
1 | 1
2 | 4
(3 rows)btw, this currently works for pl/python only in my local copy (still
testing for bugs), but similar declaration works fine for pl/pgsql--------------
My proposal is less relevant to external languages - there isn't
problem with name collisions
Pavel
Show quoted text
Hannu
2008/5/21 Merlin Moncure <mmoncure@gmail.com>:
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
*) seems cleaner to separate in/out variables so add/drop function are
symmetric.Also,
What about:CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
-- DECLARE r foo; -- make alias of r to foo optional
BEGIN
FOR i IN 1..m LOOP
foo.a := i; foo.b := i + 1;
[...]
I though about it - but there I specify only one result variable and I
directly specify name of variable to programmer. I thing so type
specification is less limited.
or
RETURNS TABLE r(a integer, b integer) AS $$
It's not ANSI compatible
Pavel
Show quoted text
merlin
2008/5/21 Hannu Krosing <hannu@krosing.net>:
On Wed, 2008-05-21 at 13:31 -0400, Merlin Moncure wrote:
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
As I'm currently working on updating another pl (pl/python), I'd like to
know how will this affect get_call_result_type() defined in funcapi.h.
will there be an extra parameter for record name there ?
no
Show quoted text
*) seems cleaner to separate in/out variables so add/drop function are
symmetric.they are kind of symmetric already :)
hannu=# drop function outsetof2py(n integer, OUT i integer, OUT j
integer);
DROP FUNCTIONAlso,
What about:CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
-- DECLARE r foo; -- make alias of r to foo optional
BEGIN
FOR i IN 1..m LOOP
foo.a := i; foo.b := i + 1;
[...]or
RETURNS TABLE r(a integer, b integer) AS $$rather "..FUNCTION foo(...) ... RETURNS TABLE r(..." as else it will be
hard to do recursive functions.merlin
On Wed, 2008-05-21 at 23:06 +0200, Pavel Stehule wrote:
2008/5/21 Hannu Krosing <hannu@krosing.net>:
On Wed, 2008-05-21 at 13:31 -0400, Merlin Moncure wrote:
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
As I'm currently working on updating another pl (pl/python), I'd like to
know how will this affect get_call_result_type() defined in funcapi.h.
will there be an extra parameter for record name there ?no
why not ?
do you think that other pl languages won't need it ?
---------------
Hannu
2008/5/22 Hannu Krosing <hannu@krosing.net>:
On Wed, 2008-05-21 at 23:06 +0200, Pavel Stehule wrote:
2008/5/21 Hannu Krosing <hannu@krosing.net>:
On Wed, 2008-05-21 at 13:31 -0400, Merlin Moncure wrote:
On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu@krosing.net> wrote:
In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.How is this different from using OUT params and RETURNS SETOF RECORD ?
*) you reference output variables via rowtype (r.var vs. var)
As I'm currently working on updating another pl (pl/python), I'd like to
know how will this affect get_call_result_type() defined in funcapi.h.
will there be an extra parameter for record name there ?no
why not ?
do you think that other pl languages won't need it ?
no, I don't thing it. But I don't need to solve problem with
identifier colissions in external languages, because SQL is separated
from language. So there will not be changes for these languages.
I plan modify build_function_result_tupdesc_d function, but an changes
will not be visible from outside.
But there isn't any breaks to use this information (argmode) for pl
languages. Only I havn't any idea about it.
Regards
Pavel
Show quoted text
---------------
Hannu
Hello
After some days I thing, so idea of local types is wrong. Maybe we can
register output types for or SRF functions (maybe only for table
functions), but this mechanism is redundant to explicit custom types.
Local functions types are nice, they allows better compile time check,
but they are unnecessary.
Sample:
CREATE OR REPLACE FUNCTION foo(a integer)
RETURNS TABLE(a integer, b integer) AS $$
DECLARE r record;
BEGIN
FOR i IN 1..a LOOP
r := ROW(i, i+1);
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
or
-- more in SQL/PSM character
CREATE OR REPLACE FUNCTION foo(a integer)
RETURNS TABLE(a integer, b integer) AS $$
BEGIN
RETURN TABLE SELECT i, i+1
FROM generate_series(1,a) g(i);
RETURN;
END;
$$ LANGUAGE plpgsql;
any comments??
Regards
Pavel Stehule
2008/5/21 Pavel Stehule <pavel.stehule@gmail.com>:
Show quoted text
Hello
I am returning back to my patch and older proposal
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php .Some work did Neil Conway
http://archives.postgresql.org/pgsql-hackers/2007-07/msg00501.php and
he commited half of this patch - RETURN QUERY part.Problematic part of my patch is implementation. Tom Lane proposal
implenation RETURNS TABLE only as syntactic sugar for RETURNS SETOF
RECORD. This is not comaptible with potential implementation, because
it adds some default variables. My solution was special argmode, so I
was able don't create default variables for output. My solution wasn't
best too. It was ugly for current plpgsql where is often used RETURN
NEXT statement (PSM doesn't know similar statement). I unlike default
variables - it simply way to variables and column names collision.I propose following syntax for plpgsql:
CREATE OR REPLACE FUNCTION foo(m integer)
RETURNS TABLE (a integer, b integer) AS $$
DECLARE r foo; -- same name as function, this type has local visibility
BEGIN
FOR i IN 1..m LOOP
r.a := i; r.b := i + 1;
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;In my proposal I don't create any default variables. Result type is
only virtual - I don't need write it to system directory. I thing it's
better than using some specific predeclared type as RESULTTYPE OR
RESULTSET.What do you thing about?
Regards
Pavel Stehule