looping on NEW and OLD in a trigger

"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

2010/8/26 Michael P. Soulier <michael_soulier@mitel.com>

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$;

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

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