|
This is suppose to pull all the columns of the table
that initiated the trigger func from the sys catalogs, loop through them and put
everything that has changed between OLD and NEW into a comma delimited string
for input into a log like table for future analysis via middleware
(php,perl..,etc). Here is the problem, OLD.A results in 'old does not have
field A', which is true. I cant get the OLD and NEW record objects to
realize that I want OLD.<string value of A> for the column
name instead of an explicit A as the column name. The only way I can
find to make this work is by using TCL for the procedural language because of
the way it casts the OLD and NEW into an associative array instead of a RECORD
object, but by using TCL I will lose functionallity in the "complete" version of
the following function which has been stripped to show my specific problem
so using TCL is currently not in my list of options. Any insight
will be greatly appreciated. create or replace function hmm() returns TRIGGER as
'
DECLARE table_cols RECORD; attribs VARCHAR; A VARCHAR; BEGIN IF TG_OP = ''UPDATE'' THEN FOR table_cols IN select attname from pg_attribute where attrelid = TG_RELID and attnum > -1 LOOP A := table_cols.attname; IF OLD.A != NEW.A THEN --Begin problem IF attribs != ''''
THEN attribs := attribs || '','' || table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A; ELSE attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A; END IF; END IF; END LOOP; END IF; RAISE EXCEPTION ''%'', attribs; RETURN NULL; END; ' Language 'plpgsql'; |
