Re: looping on NEW and OLD in a trigger

2010/8/28 Dmitriy Igrishin <dmitigr(at)gmail(dot)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
>> Blog sobre TI: http://fabriziomello.blogspot.com