I wanted to setup a simple "generic" type trigger. What a mean by generic is that i don't want to hardcode the NEW.<column> or OLD.<column> calls, i searched for a way to loop over the NEW/OLD rowtypes but could not figure out how it should be done.
This led me to attempt to do it in a way which i think is incorrect, first off i extract the column names for the given table (TG_RELNAME) from the information schema like so
-- extract the column names from tab FOR colname IN (SELECT column_name FROM information_schema.columns WHERE table_name = tab) LOOP colnames[counter] := colname.column_name; counter := counter + 1; END LOOP;
Where tab is TG_RELNAME, i then store them in a varchar array (i have verified that the values contained are correct).
If the TG_OP is 'UPDATE' i then wanted to figure out what column had been changed (at a later date i might also list the new and old vaules), so the plan was to do something like this:
ELSIF (TG_OP = 'UPDATE') THEN FOR i IN 1 .. counter LOOP IF(NEW.colnames[i] <> OLD.colnames[i])THEN -- only for testing, to see if a change in a column can be detected values_changed := values_changed || colnames[i] || ' was changed'; END IF; END LOOP;
-- values_changed currently contains rubbish INSERT INTO audit VALUES (audit_id, 'UPDATE', values_changed, '', now(), TG_RELNAME); ELSE
The above code does nothing useful and is only to test out the concept. The problem comes from NEW.colnames[i] because the NEW rowtype does not have a column called colnames. Can anyone tell me how to append the value of colnames[i] to NEW so it would work, or is it even possible to iterate of the NEW/OLD rowtype without having to know the column names ?