Re: [HACKERS] tuple return from function

> 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(at)debis(dot)com (Jan Wieck) #