Thread: Returning Rows in Procedure

Returning Rows in Procedure

From
Adarsh Sharma
Date:
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

Re: Returning Rows in Procedure

From
Pavel Stehule
Date:
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.sharma@orkash.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Returning Rows in Procedure

From
Adarsh Sharma
Date:
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.sharma@orkash.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>


Re: Returning Rows in Procedure

From
Pavel Stehule
Date:
Hello

2011/5/24 Adarsh Sharma <adarsh.sharma@orkash.com>:
> 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.
>

a) is not good idea to write too general functions
b) when function returns setof record, you have to describe return type in query


create or replace function foo(c int)
returns setof record as $$
begin
  return query execute 'SELECT ' || repeat(' i,', c - 1) || 'i FROM
generate_series(1,3) g(i)';
 end
$$ language plpgsql;

postgres=# select * from foo(2) x(a int,b int);
 a │ b
───┼───
 1 │ 1
 2 │ 2
 3 │ 3
(3 rows)

postgres=# select * from foo(3) x(a int,b int, c int);
 a │ b │ c
───┼───┼───
 1 │ 1 │ 1
 2 │ 2 │ 2
 3 │ 3 │ 3
(3 rows)

Regards

Pavel Stehule

> Thanks
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2011/5/24 Adarsh Sharma <adarsh.sharma@orkash.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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>
>

Re: Returning Rows in Procedure

From
Alban Hertroys
Date:
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!



Re: Returning Rows in Procedure

From
David Johnston
Date:
Create function a(int)
Returns TABLE(col1 text)
As $$

...

$$
Language 'plpgsql'



On May 24, 2011, at 4:08, Adarsh Sharma <adarsh.sharma@orkash.com> 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');
>
> ---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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general