Iterating through individual fields of OLD/NEW records in plpgsql trigger

Started by Nonamealmost 23 years ago3 messagesgeneral
Jump to latest
#1Noname
j.sachanbinski@coroplast.de

Hello everyone,

is there a way to loop/iterate through every individual field of the
OLD/NEW records in plpgsql trigger function?

I'm attempting to write a function that logs all the changes the user makes
to the table, sth. like: (sketch only)

CREATE TABLE data (
id integer,
value integer
);

CREATE TABLE log (
id integer,
changes text,
user text,
timestamp timestamptz
);

CREATE FUNCTION log_changes RETURNS trigger AS '
DECLARE
changes text;
BEGIN
changes := '''';

IF OLD.value <> NEW.value THEN
changes := changes || '' field value changed from: '' ||
OLD.value || ''to: '' NEW.value;
END IF;

INSERT INTO log VALUES (NEW.id, changes, session_user,
current_timestamp);

RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER log_changes BEFORE UPDATE ON data FOR EACH ROW EXECUTE
PROCEDURE log_changes();

Whilst this works as expected the function is completely table-dependent.

I would like to make it more generic by comparing all attributes of OLD and
NEW in a loop, sth. like

FOR field IN ??? LOOP
IF OLD.field <> NEW.field THEN
...
END IF;
END LOOP;

Combined with TG_RELNAME this could make such function reusable across
entire database.

Thanks in advance!
Jacek Sachanbinski

#2Larry Rosenman
ler@lerctr.org
In reply to: Noname (#1)
Re: Iterating through individual fields of OLD/NEW

--On Friday, May 23, 2003 16:34:58 +0200 j.sachanbinski@coroplast.de wrote:

Hello everyone,

is there a way to loop/iterate through every individual field of the
OLD/NEW records in plpgsql trigger function?

I'm attempting to write a function that logs all the changes the user
makes to the table, sth. like: (sketch only)

See a similar thread I had on the SQL list last week. Joe Conway posted a
wonderful solution using contrib/dblink.

I've asked this before, and have had no luck. Joe's solution logs the
exact SQL statement used.

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Noname (#1)
Re: Iterating through individual fields of OLD/NEW records in plpgsql trigger

j.sachanbinski@coroplast.de writes:

is there a way to loop/iterate through every individual field of the
OLD/NEW records in plpgsql trigger function?

No, plpgsql isn't designed to treat field names as data. You could do
it in pltcl though. Maybe plpython too; I'm not familiar with that.

regards, tom lane