iterating over all NEW.* values in a trigger?

From: Louis-David Mitterrand <cunctator(at)apartia(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: iterating over all NEW.* values in a trigger?
Date: 2001-02-22 15:44:23
Message-ID: 20010222164423.A1131@apartia.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Inside a plpgsql function trigger, is it possible to a loop over all
fields of the NEW record (and inspect their value) without knowing in
advance from which table NEW will come?

I am trying the following:

DROP FUNCTION arch_func();
CREATE FUNCTION arch_func() RETURNS opaque AS '
DECLARE
rec ecord;
BEGIN
FOR rec IN SELECT a.attname,t.typname FROM pg_attribute a JOIN pg_class c
ON (a.attrelid = c.oid AND c.relname = TG_RELNAME) JOIN pg_type t
ON (t.oid = a.atttypid) LOOP
RAISE NOTICE ''Hello %: %!'', rec.attname, rec.typname;
--
-- here I would like to access NEW.rec.attname in order to be able
-- to:
-- 1) compare it to OLD.rec.attname,
-- 2) check if there is a change,
-- 3) save a changed value in another "archive" table
--
END LOOP;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER arch_after ON auction;
CREATE TRIGGER arch_after AFTER UPDATE OR DELETE ON auction FOR each ROW EXECUTE PROCEDURE arch_func();

--
PHEDRE: Dans mes jaloux transports je le veux implorer.
Que fais-je ? O ma raison va-t-elle s'garer ?
(Phdre, J-B Racine, acte 4, scne 6)

Browse pgsql-general by date

  From Date Subject
Next Message Renaud Tthonnart 2001-02-22 16:10:51 Query with multiples operators BETWEEN
Previous Message Bruce Momjian 2001-02-22 15:38:32 Re: Chinese patch for Pgaccess