Re: plpgsql : adding record variable to table

that made it, thank you.
For other readers, here is what I finally did :

CREATE TABLE mytable (...)

CREATE FUNCTION xxxx (...) RETURNS SETOF mytable AS $$
DECLARE
r mytable%rowtype
BEGIN
...
FOR r IN select * from mytable
LOOP
....
RETURN next r;
END LOOP;
RETURN;
END;

I don't know if %rowtype is actually needed. I found this in here :
http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

thanks again
Tom

2012/4/19 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2012/4/19 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
>> hi Pavel,
>>
>> thanks for your answer,
>>
>
>> I don't understand exactly how "y" should be declared, and how it
>> should be returned by the function (as a table,
>> as a "set of record", or maybe as some kind of generic object, I don't
>> know exactly what's possible with pl/psql.).
>>
>
> r must used predeclared type - declared type or table. It doesn't work
> with "record" type.
>
> Any table specifies composite type too:
>
> create table y(a int, b int);
>
> create or replace function foo()
> returns setof y as $$
> declare r y;
> begin
>  for r in select * from y
>  loop
>    return next r;
>  end loop;
>  return;
> end;
>
> you can declare composite type via command CREATE TYPE
>
> create type y as (a int, b int)
>
> Regards
>
> Pavel Stehule
>
>> cheers
>> Tom
>>
>> 2012/4/18 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>> Hello
>>>
>>> please try:
>>>
>>> postgres=# create or replace function foo()
>>> returns void as $$
>>> declare r x;
>>> begin
>>>  for r in select * from x
>>>  loop
>>>    insert into y values(r.*);
>>>  end loop;
>>> end;
>>> $$ language plpgsql;
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>> 2012/4/18 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
>>>> (sorry my previous email was truncated)
>>>>
>>>> hi,
>>>>
>>>> Here is what I want to do :
>>>>
>>>> I want to check each row of a table against some conditions (this
>>>> check needs some
>>>> processing stuff I can easily code with pl/pgsql).
>>>>
>>>> If the row is OK, I want to add it in a "resulting table",
>>>> else I just ignore the current row and go to next one.
>>>>
>>>> My function looks like this : (simplified)
>>>>
>>>> FUNCTION myfunction (...)  RETURNS TABLE ( elem1 , elem2, elem3 ...)
>>>> DECLARE
>>>>   g RECORD
>>>> BEGIN
>>>>  FOR g in SELECT colum1, column2, ... FROM someTable
>>>>   LOOP
>>>>      -- do some processing on "g", then decide wheter I want to
>>>> select it or not
>>>>     IF (g is selected) THEN >>add g to resulting_table<<
>>>>   END LOOP
>>>>  RETURN resulting_table
>>>>
>>>> How should I write the "add g to resulting table" part ?
>>>>
>>>> thanks,
>>>> Tom
>>>>
>>>> --
>>>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-sql