Thread: 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 | 01 | 12 | 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 BEGINFOR 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. > 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
> 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 > 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 > >> *) 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. > >> 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 > > --------------- > 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>: > 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 >