Thread: PL and composite return types

PL and composite return types

From
Adriaan Joubert
Date:
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