By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Accessing fields in RECORD data type using variables as field names

P: n/a
Hi,

I am trying to write a generic audit-trail trigger function which will
record changes on a field-by-field basis to a single table for all audited
tables.

However, I find that I can only access a field in OLD and NEW if I know the
name in advance. Is there any way I can access the fields when I only have
a variable containing the name of the field?

If I can do this, I can make a really simple, schema-change-resistent, low
storage size audit trail for high read / low change databases.

Alistair

this is what I want to do:

FOR mcolumn IN EXECUTE ''SELECT col FROM v_cols_tables WHERE tbl = '' ||
quote_literal(TG_RELNAME) LOOP
vold = OLD.mcolumn.col; <--THIS BREAKS
vnew = NEW.mcolumn.col; <--THIS TOO!`
IF vold <> vnew THEN
--do audit trail stuff in here...
END IF;
END LOOP;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Wednesday 03 December 2003 13:36, Alistair Hopkins wrote:
Hi,

I am trying to write a generic audit-trail trigger function which will
record changes on a field-by-field basis to a single table for all audited
tables.

However, I find that I can only access a field in OLD and NEW if I know the
name in advance. Is there any way I can access the fields when I only have
a variable containing the name of the field?

If I can do this, I can make a really simple, schema-change-resistent, low
storage size audit trail for high read / low change databases.


Not in plpgsql, but I believe you can in pl/tcl, which I think is quite
mature.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.