2012/4/19 thomas veymont <thomas.veym...@gmail.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.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
