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't get 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 function or all dynamic SQL and see if that makes a difference with respect to the error you're seeing. If you can do this in 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 different between this code and your actual code, and it seems a relevant difference too. Perhaps you could give us a better example, 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!