Re: Returning Rows in Procedure

Create function a(int)
Returns TABLE(col1 text)
As $$

...

$$
Language 'plpgsql'

On May 24, 2011, at 4:08, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general