tuple return from function - Mailing list pgsql-hackers

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