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.steh...@gmail.com>:
> 2012/4/19 thomas veymont <thomas.veym...@gmail.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.steh...@gmail.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.veym...@gmail.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@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
