Re: Returning Rows in Procedure

Hello

you have to use a dynamic sql

look on statement

FOR r IN EXECUTE
or RETURN QUERY EXECUTE

Regards

Pavel Stehule

2011/5/24 Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>:
> 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
>