That is very similar to what I have been trying to do. I have 1 question and one problem though. Question: DO I have to define every column I am returning as an out going parameter? Problem I ran your test and I am getting as error see below test=# create function countum(lim int, out n int, out en text, out es text) returns setof record as $$ test$# declare r record; test$# begin test$# for r in select * from t1 join t2 on f1=k1 where f1 <= lim loop test$# n := r.f1; test$# en := r.f2; test$# es := r.k2; test$# return next; test$# end loop; test$# end $$ language plpgsql; ERROR: CREATE FUNCTION / OUT parameters are not implemented -----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Thursday, June 30, 2005 3:57 PM To: Jason Tesser Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: COnsidering a move away from Postgres Jason Tesser <jtesser@xxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
