Returning Rows in Procedure

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Returning Rows in Procedure
Date: 2011-05-24 08:08:39
Message-ID: 4DDB6787.3060807@orkash.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear all,

I need to return the rows of a table which was also created in that
procedure.

I know it is very easy when the table is existed before and we can
specify like this to return

create function a(integer) returns setof exist_table as $$

But it gives error when the table is also created in the procedure like
below :

create function a(integer) returns setof record as $$
declare
a text;
begin
execute 'insert into a values('asdd');
execute 'insert into a values('affffsdd');
execute 'insert into a values('affsdd');
execute 'insert into a values('ashjgdd');
execute 'insert into a values('asfjfgddd');

---Now i want to return the rows of a
DECLARE
r a%ROWTYPE;
BEGIN
FOR r in SELECT * FROM a
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
END;
$$ LANGUAGE 'plpgsql' ;

ERROR: relation "user_news_tmp2" does not exist
CONTEXT: compilation of PL/pgSQL function "create_user_report2" near
line 22

How to achieve this ?

Thanks & best Regards,
Adarsh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2011-05-24 08:12:26 Re: Returning Rows in Procedure
Previous Message jun yang 2011-05-24 08:02:52 Re: how to start a procedure after postgresql started.