From: | "Jason Tesser" <JTesser(at)nbbc(dot)edu> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: COnsidering a move away from Postgres |
Date: | 2005-06-30 22:09:20 |
Message-ID: | AA9E9C9ACAFCA545A33A9F6E1E88353E069674@titus.nmi.northlandministries.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, June 30, 2005 3:57 PM
To: Jason Tesser
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] COnsidering a move away from Postgres
Jason Tesser <jtesser(at)nbbc(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Tesser | 2005-06-30 22:19:29 | Re: COnsidering a move away from Postgres |
Previous Message | Ciprian Popovici | 2005-06-30 21:29:09 | Re: Recovering a broken database |