Re: 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(at)krosing(dot)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(at)krosing(dot)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(at)krosing(dot)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(at)gmail(dot)com>:
> On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing <hannu(at)krosing(dot)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(at)krosing(dot)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(at)krosing(dot)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(at)krosing(dot)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(at)krosing(dot)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(at)krosing(dot)net>:
> On Wed, 2008-05-21 at 23:06 +0200, Pavel Stehule wrote:
>> 2008/5/21 Hannu Krosing <hannu(at)krosing(dot)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(at)krosing(dot)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(dot)stehule(at)gmail(dot)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
>