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!
