471,305 Members | 1,412 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,305 software developers and data experts.

Accessing fields in RECORD data type using variables as field names

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
1 3215
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.

Similar topics

4 posts views Thread by Gene | last post: by
4 posts views Thread by Martin Lacoste | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.