I have cross-posted this to pgsql-bugs as it relates to a past thread
regarding documentation for PL/PgSQL and %ROWTYPE.
I need help calling functions that accept rows as parameters from plpgsql. It
appears that I can invoke a function that expects a row as a parameter from
a SQL statement, but can't do so from another function. See the following
example.
create table some_numbers (i int);
insert into some_numbers (i) values (1);
insert into some_numbers (i) values (-1);
create or replace function f3(some_numbers) returns text as '
begin
if $1.i < 0 then
return ''negative'';
elsif $1.i > 0 then
return ''positive'';
else
return ''zero'';
end if;
end;
' language 'plpgsql';
db=# select f3(some_numbers) from some_numbers;
f3
----------
positive
negative
(2 rows)
This appears to work (although not sure that the name of the parameter
is intuitive) correctly...
Now, I define another function, f4
create or replace function f4() returns text as '
declare
n some_numbers%rowtype;
t text;
c cursor for select * from some_numbers;
begin
t := '''';
open c;
loop
fetch c into n;
exit when not found;
t := t || f3(n);
end loop;
return t;
end;
' language 'plpgsql';
db=# select f4();
NOTICE: Error occurred while executing PL/pgSQL function f4
NOTICE: line 12 at assignment
ERROR: Attribute 'n' not found
What's up with this?
Some more counter examples that help narrow the problem to the
handling of rows in the calling function follow.
create or replace function f5() returns text as '
declare
n some_numbers%rowtype;
t text;
c cursor for select * from some_numbers;
begin
t := '''';
open c;
loop
fetch c into n;
exit when not found;
if n.i < 0 then
t := t || ''negative '';
elsif n.i > 0 then
t := t || ''positive '';
else
t := t || ''zero '';
end if;
end loop;
return t;
end;
' language 'plpgsql';
create or replace function f6(int) returns text as '
begin
if $1 < 0 then
return ''negative '';
elsif $1 > 0 then
return ''positive '';
else
return ''zero '';
end if;
end;
' language 'plpgsql';
create or replace function f7() returns text as '
declare
declare
n some_numbers%rowtype;
t text;
c cursor for select * from some_numbers;
begin
t := '''';
open c;
loop
fetch c into n;
exit when not found;
t := t || f6(n.i);
end loop;
return t;
end;
' language 'plpgsql';
db=# select f5();
f5
--------------------
positive negative
(1 row)
db=# select f7();
f7
--------------------
positive negative
(1 row)
Thanks,
Lane.
--
Lane Stevens
Terrapin Technologies, Inc.
http://www.cycletime.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
