Thread: looping on NEW and OLD in a trigger
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 <michael_soulier@mitel.com>, 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
"Michael P. Soulier" <michael_soulier@mitel.com> 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
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 <michael_soulier@mitel.com>, 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
On Thu, Aug 26, 2010 at 12:59 PM, Michael P. Soulier <michael_soulier@mitel.com> 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-hackers@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
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
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 <michael_soulier@mitel.com>
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 <michael_soulier@mitel.com>, 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
2010/8/28 Dmitriy Igrishin <dmitigr@gmail.com>
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:
<cut>
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 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$;
END LOOP;
RETURN NEW;
END;
$func$;
CREATE TRIGGER person_test_trigger BEFORE INSERT
ON person FOR EACH ROW
EXECUTE PROCEDURE test_dynamic();
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,
On Sat, Aug 28, 2010 at 6:23 PM, Fabrízio de Royes Mello <fabriziomello@gmail.com> wrote: > 2010/8/28 Dmitriy Igrishin <dmitigr@gmail.com> >> >> 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: >> >> <cut> >> > > 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