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