Thanks for all your replies.
Actually I don't know the number of columns that I am going to return.
I have 2 tables. For a single entry E1 in one table(t1), I have to fetch all the matching entries for E1 from the other table(t2), K1,..Kn.
and finally the function should return E1, K1..Kn. So I don't know the number of columns that I am going to get.
Is it possible to write a function that returns this kind of result?
Please help.
Thanks,
maria
On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <
mail@webthatworks.it> wrote:
On Tue, 3 Jun 2008 09:01:02 -0400
"maria s" <
psmg01@gmail.com> wrote:
> Hi Friends,
> Thanks for all your for the reply.
>
> I tried the function and when I execute it using
> select * from myfunction()
> it says
> ERROR: a column definition list is required for functions
> returning "record"
>
> Could you please help me to fix this error?
>
> Thanks so much for your help.
function or you can specify the returned type in the function itself.
CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2
varchar(32), out ...)
RETURNS
SETOF
RECORD
AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
col1:=rec.col1;
col2:=rec.col2;
-- col3:=...;
RETURN NEXT;
END LOOP;
RETURN;
END;
$body$
> > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> > $body$
> > DECLARE
> > rec record;
> > BEGIN
> > FOR rec IN (
> > SELECT * FROM sometable)
> > LOOP
> > RETURN NEXT rec;
> > END LOOP;
> > RETURN;
> > END;
> > $body$
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
