Re: Returning Rows in Procedure

On 24 May 2011, at 10:08, Adarsh Sharma 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');

You're skating on thin ice here, you have a function named "a", a table named "a" and a variable named "a" (that
doesn'tget used BTW) - are you sure they're not mixed up anywhere? 
I also don't quite see the need to use dynamic SQL here for insertions into the "a" table.

Is this your actual function? I don't think it is.

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

I'm not sure the above would work with the dynamic SQL from before. I'd try using either all static SQL in that
functionor all dynamic SQL and see if that makes a difference with respect to the error you're seeing. If you can do
thisin all static SQL it'll probably perform better. 

> END;
> $$ LANGUAGE 'plpgsql' ;
>
> ERROR:  relation "user_news_tmp2" does not exist
> CONTEXT:  compilation of PL/pgSQL function "create_user_report2" near line 22


Well, according to the code you provided your table is named "a", and not "user_news_tmp2". There's obviously something
differentbetween this code and your actual code, and it seems a relevant difference too. Perhaps you could give us a
betterexample, or show us the actual code even? 


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4ddb79f211928090216264!