I have had no luck trying to make functions and composite return types
work. From the docs I cannot figure out what I'm doing wrong or whether
I've hit some limitations. Following example:
create table T (
a int4,
b int4
);
insert into T values (2,3);
create function tf (int4) returns T as
'
declare
tr record;
r T%rowtype;
begin
select * into tr where a = $1;
r.a = $1;
r.b = tr.b;
return r;
end;
' language 'plpgsql';
create function tfs (int4) returns T as
'select a as a, b as b from T where a = $1;'
language 'sql';
tt=> select tf(2);
ERROR: cannot expand: null p_rtable
tt=> select tfs(2);
tfs
----------
1075737920
(1 row)
I didn't expect either result. So what am I doing wrong? and is there a
better way to copy a record into a T%rowtype structure? As this is the
result of a fairly complex PL function, I would like to do this in PL
and not SQL.
I managed to get this working by returning the oid of the selected row
and then doing another select using the oid, but this requires an
additional select, and with the sizes of some of my tables I would like
to avoid that.
Any help appreciated!
Adriaan
