Обсуждение: 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 BEGINFOR g in SELECT colum1, column2, ... FROM someTable LOOP -- do some processing on "g", then decide wheter I wantto 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
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.veymont@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
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 <pavel.stehule@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.veymont@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
2012/4/19 thomas veymont <thomas.veymont@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.stehule@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.veymont@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
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.stehule@gmail.com>: > 2012/4/19 thomas veymont <thomas.veymont@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.stehule@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.veymont@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
2012/4/19 thomas veymont <thomas.veymont@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.stehule@gmail.com>: >> 2012/4/19 thomas veymont <thomas.veymont@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.stehule@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.veymont@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