Jason Tesser <jtesser@nbbc.edu> writes:
> I might be missing it but how does this help me. What I would like is to be
> able to return multiple records from a select statement that return multiple
> columns from different tables without having to create a type.
You mean like this?
regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# insert into t1 values(1, 'one');
INSERT 0 1
regression=# insert into t1 values(2, 'two');
INSERT 0 1
regression=# create table t2 (k1 int, k2 text);
CREATE TABLE
regression=# insert into t2 values(1, 'uno');
INSERT 0 1
regression=# insert into t2 values(2, 'dos');
INSERT 0 1
regression=# create function countem(lim int, out n int, out en text,
regression(# out es text) returns setof record as $$
regression$# declare r record;
regression$# begin
regression$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop
regression$# n := r.f1;
regression$# en := r.f2;
regression$# es := r.k2;
regression$# return next;
regression$# end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from countem(2);
n | en | es
---+-----+-----
1 | one | uno
2 | two | dos
(2 rows)
regards, tom lane