Bug #960: Call from functions in plpgsql can't pass a row as parameter

Jaime Cervera (jcervera(at)aq(dot)upm(dot)es) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Call from functions in plpgsql can't pass a row as parameter

Long Description
I've found in Google a post to pgsql-bugs on
Tue, 20 Aug 2002 15:05:49 -0600, from Lane Stevens <lane(at)cycletime(dot)com>
as I searched for a reason to explain why CALLS FROM FUNCTIONS IN 'PLPGSQL'
LANGUAGE COULD'NT PASS A ROW AS PARAMETER.

Lane reports no progress in the matter:
On Wednesday 23 April 2003 18:52, Lane Stevens wrote:
> Unfortunately, we did not solve this problem. We ended up taking a
> different path. I believe that we are passing a cursor instead of a row.
> Not what we wanted :( .
>
> Thanks,
> Lane.

As I've been unable to find a bug-track system for Postgres (status of
old bugs and so on) I report now it as new. I don't know any other
method :( .

The problem is that a plpgsql function that gets a row parameter can't
transmit that row parameter to a new function.

Such call in 'sql' language can be done without problem,
but in the project we are working -a desgin tool for structural mechanics
that, if successfull, should be released to the public domain- we should
prefer the 'plpgsql' flavour, due to the complexity of the functional tree
needed for some of the calculations, aside from performance reasons.

We are using a debian packaged 7.2.1 version, but I've been looking
around on the release comments of newer versions, (up to 7.2.4 or the
7.3.2 in both branches ) and I've not seen any comment about that question.

I think that the examples provided can be useful.

Sample Code
Our example problem:
--------------
IN SQL THING WORKS
--------------
the_database=> create table thetable ( h float8);
CREATE
the_database=> copy thetable from stdin;
620
1000
1000
600
\.
the_database=> create or replace function int_funct(thetable) returns int4 as
' select int4($1.h) ' language 'sql';
CREATE
the_database=> create or replace function ext_function(thetable) returns int4
as ' select int_funct($1) ' language 'sql';
CREATE
the_database=> select ext_function(p) from thetable p;
ext_function
--------------
620
1000
1000
600
(4 rows)
----------------------------
It works also with a PL/PgSQL internal function, as the call does not affect
it's behaviour
-----------------------------
the_database=> create or replace function int_funct(thetable) returns int4 as
'
BEGIN
return (select int4($1.h));
END ' language 'plpgsql';
CREATE
the_database=> select ext_function(p) from thetable p;
ext_function
--------------
620
1000
1000
600
(4 rows)
----------------------------
BUT THE external PL/PgSQL function fails
the call with a $1 as parameter or with an ALIAS
for the row call differ in behavior
----------------------------
the_database=> create or replace function ext_function(thetable) returns int4
as '
BEGIN
return (select int_funct($1));
END ' language 'plpgsql';
CREATE
the_database=> select ext_function(p) from thetable p;
NOTICE: Error occurred while executing PL/pgSQL function ext_function
NOTICE: line 2 at return
ERROR: Parameter '$1' is out of range
the_database=> create or replace function ext_function(thetable) returns int4
as '
DECLARE
tab_row ALIAS FOR $1;
BEGIN
return (select int_funct(tab_row));
END ' language 'plpgsql';
CREATE
the_database=> select ext_function(p) from thetable p;
NOTICE: Error occurred while executing PL/pgSQL function ext_function
NOTICE: line 4 at return
ERROR: Attribute 'tab_row' not found
------------------------------------
BUT the row is well defined inside the external function:
-------------------------------------
the_database=> create or replace function ext_function(thetable) returns int4
as '
DECLARE
tab_row ALIAS FOR $1;
BEGIN
RAISE NOTICE ''Table_row exists: Value of tab_row.h is %'', tab_row.h;
RAISE NOTICE ''(but fails ... see next)'';
return (select int_funct(tab_row));
END ' language 'plpgsql';
CREATE
the_database=> select ext_function(p) from thetable p;
NOTICE: Table_row exists: Value of tab_row.h is 620
NOTICE: (but fails ... see next)
NOTICE: Error occurred while executing PL/pgSQL function ext_function
NOTICE: line 6 at return
ERROR: Attribute 'tab_row' not found

------------------------------------
It seems that the problem resides on the 'plpgsql' parser in the compilation
phase of the first call to the external function.

A similar of the first class of "ERROR" message is found ('plpgsql' or 'sql')
when trying to create an inconsistent function
------------------------------------

the_database=> create or replace function
bad_nr_of_parameters() returns float8 as '
select $1 ' language 'sql';
ERROR: Parameter '$1' is out of range

the_database=> create or replace function
bad_nr_of_parameters() returns float8 as '
BEGIN
return $1;
END;'
language 'plpgsql';
CREATE
the_database=> select bad_nr_of_parameters();
NOTICE: Error occurred while executing PL/pgSQL function bad_nr_of_parameters
NOTICE: line 2 at return
ERROR: Parameter '$1' is out of range

No file was uploaded with this report