Re: Returning Rows in Procedure

Hello

2011/5/24 Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)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(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
>>>
>>>
>
>