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
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
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
Re: [SQL] plpgsql : adding record variable to table
2012/4/19 thomas veymont : > 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 > %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 : >> 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
Re: [SQL] Uniform UPDATE queries
Hello Tom, The example you have given is EXACTLY why something like CURRENT is needed to limit the number of unique queries or prepared statements. (or to do a selection of all values before an update meaning two executed queries.) regards,. Dennis On 04/18/2012 06:24 PM, Tom Lane wrote: Dennis writes: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique queries, a keyword similar to DEFAULT, let's say CURRENT, is required to indicate that the current value must not change. No it isn't. Just write the name of the column, eg update mytable set x = x, y =, z = z where ... There's no reason to invent nonstandard syntax for this. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Uniform UPDATE queries
On 04/19/2012 04:55 AM, Dennis wrote: Hello Tom, The example you have given is EXACTLY why something like CURRENT is needed to limit the number of unique queries or prepared statements. (or to do a selection of all values before an update meaning two executed queries.) regards,. Dennis On 04/18/2012 06:24 PM, Tom Lane wrote: Dennis writes: When a query is written to update a table, the usual process is to list all the columns that need updating. This could imply the creation of many possible queries for many columns. In an effort to keep the UPDATE queries more uniform, less number of unique queries, a keyword similar to DEFAULT, let's say CURRENT, is required to indicate that the current value must not change. No it isn't. Just write the name of the column, eg update mytable set x = x, y =, z = z where ... There's no reason to invent nonstandard syntax for this. regards, tom lane Not if you have all the old and new values: update mytable set x= xval, y=yval, z=zval where yval happens to be a new value and xval, zval are current. Seems your callers knows which have changed. Does it not know which have not (and what their values are)? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
