Bug #960: Call from functions in plpgsql can't pass a row as parameter - Mailing list pgsql-bugs

Jaime Cervera (jcervera@aq.upm.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@cycletime.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