FAQ
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

Search Discussions

  • Hannu Krosing at May 21, 2008 at 5:28 pm

    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
  • Merlin Moncure at May 21, 2008 at 5:31 pm

    On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing 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
  • Hannu Krosing at May 21, 2008 at 6:15 pm

    On Wed, 2008-05-21 at 13:31 -0400, Merlin Moncure wrote:
    On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing 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
  • Pavel Stehule at May 21, 2008 at 9:06 pm

    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 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
  • Hannu Krosing at May 22, 2008 at 1:02 pm

    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 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
  • Pavel Stehule at May 22, 2008 at 1:36 pm

    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 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


  • Pavel Stehule at May 21, 2008 at 9:03 pm

    2008/5/21 Merlin Moncure <mmoncure@gmail.com>:
    On Wed, May 21, 2008 at 1:28 PM, Hannu Krosing 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
  • Pavel Stehule at May 21, 2008 at 9:01 pm

    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

  • Pavel Stehule at Jun 1, 2008 at 6:53 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 21, '08 at 4:12p
activeJun 1, '08 at 6:53p
posts10
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase