Re: [HACKERS] tuple return from function - Mailing list pgsql-hackers

> I have felt that the 'return set' has only worked in limited cases.
> Vadim seems to know there are problems, particularly with free'ing
> memory.
>
> My question is whether this relates to the rules system rewrite or the
> PL/SQL language module?
>
> --
> Bruce Momjian                          |  830 Blythe Avenue

    It does not relate to the rule system.

    But it relates to the procedural language functions. It would
    be nice to enable tuples and setof tuples as return type  for
    PL/pgSQL and other languages. Currently this is restricted to
    'sql' functions and the return type of tuple (not  setof)  is
    broken.

    In  the  case  of  a  function  returning SETOF tupletype, it
    works.  The function

        create function f1(int4) returns setof t1 as '
            insert into t2 values (''now'');
            select * from t1 where a < $1;
        ' language 'sql';

    when called as

        select a(f1(5));

    produces a RESULT plan with one  entry  in  the  qptargetlist
    that  contains  an ITER->EXPR->FUNC where the FUNC node has a
    targetlist with one entry for attribute a.  The  function  is
    only executed once.

    But when called as

        select a(f1(5)), b(f1(5));

    produces  a  RESULT plan with two entries in the qptargetlist
    where each contains an ITER->EXPR->FUNC where the  FUNC  node
    has  again  a  targetlist  with  one  entry. The result looks
    right, but the function is executed two times (this time  two
    new  entries  in  t2).  This  might  be  right,  because  the
    arguments to f1() could differ. What I haven't found  is  the
    ability  to  evaluate  the  function  once  and  use multiple
    attributes from the return set.

    So you cannot do something like

        insert into t3 select f1(5);

    What this demonstrates clearly to me is that return types  of
    tuple or sets don't really do what they should.

    First I think the call to a function returning one tuple with
    attribute specification must be fixed. It must  not  generate
    the  same  function  call twice (one time inside the argument
    list of the outer one).

    Second the plain tuple return value must  be  useful  in  any
    way.  Currently you cannot have functions f1(int4) returns t1
    and f2(t1) and then do something like f2(f1(5)). It tells 'f2
    has bad return type ...'.

    Third  there  should be an enhancement to specify some or all
    of the attributes like

        select F.a, F.c, F.e from f1(5) F;

    or

        select * from f1(5);

    If this is done, ExecMakeFunctionResult() can be extended. If
    there  is  a targetlist in the func node of a C function (for
    ExecMake...  there is no  difference  between  C,  PL/TCL  or
    PL/pgSQL),  it  knows  that  the  return  value is a tuple or
    tupletable or temp relation or whatever and it can manage for
    the  requested  projection and for the iteration (if function
    isset).

    But should we do that all (and the rule stuff) before 6.4?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #