Thread: tuple return from function
Hi,
I really wonder if anybody ever used functions returning
tuples or sets of tuples. If so, be careful!
Let's have a look at the following test:
create table emp (name text, salary money);
create table sal_log (empname text, salchg money, who text, when datetime);
create function change_sal(text, money) returns emp as '
update emp set salary = salary + $2 where name = $1;
insert into sal_log values ($1, $2, getpgusername(), ''now'');
select * from emp where name = $1;' language 'sql';
-- **** ^^^
-- **** |||
insert into emp values ('Jan', '0.00');
select change_sal('Jan', '1000.00');
CREATE
CREATE
CREATE
INSERT 18423 1
change_sal
----------
136044880
(1 row)
-- **** 136044880 is the memory address of the tuple table slot
-- **** returned by the function change_sal() - very useful :-)
select * from emp;
name|salary
----+---------
Jan |$1,000.00
(1 row)
select * from sal_log;
empname|salchg |who |when
-------+---------+-----+--------------------------------
Jan |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
(1 row)
-- **** But the result is OK so far
-- **** Now we use the so called nested dot notation to get
-- **** the name from the returned tuple
select name(change_sal('Jan', '1000.00'));
name
----
Jan
(1 row)
-- **** That's right
select * from emp;
name|salary
----+------
Jan |
(1 row)
select * from sal_log;
empname|salchg |who |when
-------+---------+-----+--------------------------------
Jan |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
Jan |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
Jan | |pgsql|Thu Aug 13 12:46:28 1998 MET DST
(3 rows)
EOF
-- **** But this isn't what anyone would expect
Background:
I'm not really sure if I used the correct syntax to access a
field from the returned tuple. If not, please correct me.
The reason for the behaviour is the generated querytree. It
is a nested function call of two times change_sal(). There
is a func node for change_sal() with one argument that is
change_sal('Jan', '1000.0') func node. Both func nodes have
the same targetlist about 'name'.
At the beginning of ExecMakeFunctionResult() the argument
from the outer is evaluated and causes the call of the inner
one. When the inner one finishes, it returns the name (due to
the target list). This single return value then replaces the
argument vector for the outer func node. Now
postquel_function() is called but with only one argument, the
second expected argument now is a NULL value. That explains
the behaviour above.
But don't expect
select salary(change_sal('Jan', '1000.00'))
to work. It causes an
ERROR: Tuple is too big: size 200064
Even if you have a function not updating or inserting
something, the generated querytree causes the function to be
called twice. So a simple function just doing one select and
returning a tuple type causes two scans.
What I absolutely don't know is, what is it good for? How is
the correct syntax to access more than one field from the
returned tuple?
Shouldn't the call of a function returning a tuple without
the surrounding attrname(...) have a targetlist too? If so,
the complete targetlist must be used when building the
projection tuple, not only the first TLE as it is implemented
now.
And I think the parser/planner must not generate nested func
nodes.
I'm really willing to dive into, but should I do it before or
after 6.4? Doing it before would mean 6.4 NOT AT THE END OF
THIS MONTH!
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) #
>
> But don't expect
>
> select salary(change_sal('Jan', '1000.00'))
>
> to work. It causes an
>
> ERROR: Tuple is too big: size 200064
>
> Even if you have a function not updating or inserting
> something, the generated querytree causes the function to be
> called twice. So a simple function just doing one select and
> returning a tuple type causes two scans.
>
> What I absolutely don't know is, what is it good for? How is
> the correct syntax to access more than one field from the
> returned tuple?
>
> Shouldn't the call of a function returning a tuple without
> the surrounding attrname(...) have a targetlist too? If so,
> the complete targetlist must be used when building the
> projection tuple, not only the first TLE as it is implemented
> now.
>
> And I think the parser/planner must not generate nested func
> nodes.
>
> I'm really willing to dive into, but should I do it before or
> after 6.4? Doing it before would mean 6.4 NOT AT THE END OF
> THIS MONTH!
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
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
> 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) #
> 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? Sorry to say this, but I think we need the rewrite stuff done for 6.4. Too many bugs and limited features. The PL/pgSQL perhaps can be started now, but not ready until 6.5? I don't think we should delay 6.4 for PL/pgSQL, do you? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Bruce Momjian wrote:
> Sorry to say this, but I think we need the rewrite stuff done for 6.4.
> Too many bugs and limited features.
Thats right. Do you also agree in that only the instead rules
should be left?
>
> The PL/pgSQL perhaps can be started now, but not ready until 6.5? I
> don't think we should delay 6.4 for PL/pgSQL, do you?
It is in a good, stable state now. I would like to have it
shipped with 6.4, because removing the non-instead rules is a
loss of functionality and at least we must provide another
way. Someone might currently use rules for some extra
actions. But this is subject to trigger procedures. Up to now
there's only C and Tcl available for it. Not anyone likes Tcl
- and writing the triggers in C isn't fun for SQL
programmers.
I'll pack it and send it to you after a last check of the
code.
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) #
> Bruce Momjian wrote: > > > Sorry to say this, but I think we need the rewrite stuff done for 6.4. > > Too many bugs and limited features. > > Thats right. Do you also agree in that only the instead rules > should be left? Beats me. Whatever the other developers think. > > > > > The PL/pgSQL perhaps can be started now, but not ready until 6.5? I > > don't think we should delay 6.4 for PL/pgSQL, do you? > > It is in a good, stable state now. I would like to have it > shipped with 6.4, because removing the non-instead rules is a > loss of functionality and at least we must provide another > way. Someone might currently use rules for some extra > actions. But this is subject to trigger procedures. Up to now > there's only C and Tcl available for it. Not anyone likes Tcl > - and writing the triggers in C isn't fun for SQL > programmers. > > I'll pack it and send it to you after a last check of the > code. Sure, we will ship whatever you have, even if it is not perfect. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)