[SQL] plpgsql : adding record variable to table
hi, say I have the following (simplified for discussion) pl/pgsql function: FUNCTION myfunction ( ...) RETURNS TABLE ( elem1 integer, elem2 text, ...) DECLARE g RECORD BEGIN FOR g in SELECT colum1, column2 FROM someTable LOOP -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql : adding record variable to table
(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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql : adding record variable to table
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.). cheers Tom 2012/4/18 Pavel Stehule : > 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 : >> (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 ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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 (...) 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 : > 2012/4/19 thomas veymont : >> 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 : >>> 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 : >>>> (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 ([email protected]) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
