Re: plpgsql : adding record variable to table

2012/4/19 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
> 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
>

%rowtype is not required - in pg (it is syntax from Oracle), but it is
good to use it to increase readability.

Regards

Pavel

> 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