Re: [GENERAL] looping on NEW and OLD in a trigger
On Sat, Aug 28, 2010 at 6:23 PM, Fabrízio de Royes Mello wrote: > 2010/8/28 Dmitriy Igrishin >> >> Hey Michael, >> >> As of PostgreSQL 9.0 you can do it from PL/pgSQL by >> using hstore module >> (http://www.postgresql.org/docs/9.0/static/hstore.html) >> >> I wrote an example for you: >> >> >> > > Another way to do that is create a temp table from NEW or OLD record and > loop over the fields using system catalog. > > CREATE TABLE person(id integer, fname text, lname text, birthday date); > > CREATE OR REPLACE FUNCTION test_dynamic() > RETURNS trigger > LANGUAGE plpgsql > AS $func$ > DECLARE > _field text; > BEGIN > CREATE TEMP TABLE tmp_new AS SELECT NEW.*; > FOR _field IN SELECT column_name FROM information_schema.columns WHERE > table_name = 'tmp_new' AND table_schema ~ '^pg_temp' LOOP > RAISE NOTICE '%', _field; > END LOOP; > > RETURN NEW; > END; > $func$; If you're going to do it that way -- I'd greatly prefer using TG_TABLE_NAME/TG_TABLE_SCHEMA. These are directly intended for this kind of purpose. Temporary tables are a bit of of a bugaboo in terms of pl/pgsql performance...especially in high traffic functions like per row triggers...double especially 'on commit drop' temp tables. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looping on NEW and OLD in a trigger
2010/8/28 Dmitriy Igrishin > Hey Michael, > > As of PostgreSQL 9.0 you can do it from PL/pgSQL by > using hstore module > (http://www.postgresql.org/docs/9.0/static/hstore.html) > > I wrote an example for you: > > > > Another way to do that is create a temp table from NEW or OLD record and loop over the fields using system catalog. CREATE TABLE person(id integer, fname text, lname text, birthday date); CREATE OR REPLACE FUNCTION test_dynamic() RETURNS trigger LANGUAGE plpgsql AS $func$ DECLARE _field text; BEGIN CREATE TEMP TABLE tmp_new AS SELECT NEW.*; FOR _field IN SELECT column_name FROM information_schema.columns WHERE table_name = 'tmp_new' AND table_schema ~ '^pg_temp' LOOP RAISE NOTICE '%', _field; END LOOP; RETURN NEW; END; $func$; CREATE TRIGGER person_test_trigger BEFORE INSERT ON person FOR EACH ROW EXECUTE PROCEDURE test_dynamic(); INSERT INTO person VALUES (1, 'Fabrizio', 'Mello', '1979-08-08'); This example works more then one version of PostgreSQL. Best regards, -- Fabrízio de Royes Mello >> Blog sobre TI: http://fabriziomello.blogspot.com
Re: [GENERAL] looping on NEW and OLD in a trigger
Hey Michael, As of PostgreSQL 9.0 you can do it from PL/pgSQL by using hstore module (http://www.postgresql.org/docs/9.0/static/hstore.html) I wrote an example for you: CREATE TABLE person(id integer, fname text, lname text, birthday date); CREATE TRIGGER person_test_trigger BEFORE INSERT ON person FOR EACH ROW EXECUTE PROCEDURE test_dynamic(); CREATE OR REPLACE FUNCTION test_dynamic() RETURNS trigger LANGUAGE plpgsql AS $func$ DECLARE _newRec hstore := hstore(NEW); _field text; BEGIN FOR _field IN SELECT * FROM skeys(_newRec) LOOP RAISE NOTICE '%', _field; END LOOP; RETURN NEW; END; $func$; Regards, Dmitriy 2010/8/26 Michael P. Soulier > Hi, > > I'm very new to writing postgres procedures, and I'm trying to loop over > the fields in the NEW and OLD variables available in an after trigger, > and I can't quite get the syntax correct. > > Could someone point me at an example? > > Thanks, > Mike > -- > Michael P. Soulier , 613-592-2122 x2522 > "Any intelligent fool can make things bigger and more complex... It takes a > touch of genius - and a lot of courage to move in the opposite direction." > --Albert Einstein > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] looping on NEW and OLD in a trigger
On Thu, Aug 26, 2010 at 12:59 PM, Michael P. Soulier wrote: > On 26/08/10 Tom Lane did say: > >> If you're trying to do this in plpgsql, the answer is you can't. >> plpgsql doesn't support dynamic field references, which is what you'd >> need for what (I think) you're trying to do. >> >> You can do it in pltcl or plperl, and probably also plpython though >> I don't know enough python to be sure. > > Ok, I'll try plpython then. For a more in-depth treatment of the topic, see: http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg139182.html (the entire thread is worth reading IMO if you want to really understand dynamic record inspection really well, both in pl/pgsql and other PLs). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looping on NEW and OLD in a trigger
On 26/08/10 Tom Lane did say: > If you're trying to do this in plpgsql, the answer is you can't. > plpgsql doesn't support dynamic field references, which is what you'd > need for what (I think) you're trying to do. > > You can do it in pltcl or plperl, and probably also plpython though > I don't know enough python to be sure. Ok, I'll try plpython then. Thanks, Mike -- Michael P. Soulier , 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looping on NEW and OLD in a trigger
"Michael P. Soulier" writes: > I'm very new to writing postgres procedures, and I'm trying to loop over > the fields in the NEW and OLD variables available in an after trigger, > and I can't quite get the syntax correct. If you're trying to do this in plpgsql, the answer is you can't. plpgsql doesn't support dynamic field references, which is what you'd need for what (I think) you're trying to do. You can do it in pltcl or plperl, and probably also plpython though I don't know enough python to be sure. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general