Re: Returning composite types from functions

Adam Witney wrote:
> There have been a few emails recently concerning using functions. However I
> am a little confused as to their use with composite types. I can see how to
> return a whole row from a table, but is it possible to return multiple
> fields that do not originate from the same table?

Sure. But you either need a named composite type that matches the row you want
to return, or you can use a record datatype and specify the column definitions
in the sql statement at run time.

A composite type exists for each table and view in your database, as well as
any stand-alone composite types you define. So, for example:

test=# create table foo (f1 int,f2 text);
CREATE TABLE
test=# create table bar (f3 int,f4 text);
CREATE TABLE
test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3;
CREATE VIEW
test=# insert into foo values(1,'a');
INSERT 1105496 1
test=# insert into foo values(2,'b');
INSERT 1105497 1
test=# insert into bar values(1,'c');
INSERT 1105498 1
test=# insert into bar values(2,'d');
INSERT 1105499 1

-- This uses a named composite type based on the view
test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4
from foo, bar where f1=f3' language sql;
CREATE FUNCTION
test=# select * from getfoobar1();
f1 | f2 | f4
----+----+----
1 | a | c
2 | b | d
(2 rows)

-- This uses an anonymous composite type specified at runtime
test=# create function getfoobar2() returns setof record as 'select f1,f2,f4
from foo, bar where f1=f3' language sql;
CREATE FUNCTION
test=# select * from getfoobar2() as (f1 int,f2 text,f4 text);
f1 | f2 | f4
----+----+----
1 | a | c
2 | b | d
(2 rows)

HTH,

Joe