Re: Returning Rows in Procedure

Pavel Stehule wrote:
> Hello
>
> you have to use a dynamic sql
>
> look on statement
>
> FOR r IN EXECUTE
> or RETURN QUERY EXECUTE
>

Can u explain in the example, I find it difficult to understand .

I think we have to specify return type while creating procedures.

Thanks
> 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
>>
>>