Hello,
is it possible to use variables as field names in the NEW record? Let's
suppose I have a varchar attname containg the name of the field and I want
to know a value that field of the NEW record.
Problem is that I get an error 'record "new" has no field "attname"'. Of
course I want to use a value of NEW.author when col.attname = attname =
'author'.
Is there a solution?
Example trigger function. It finds all columns in the table which are
referenced in other tables and checks if the value of the column has
changed. If yes, then invoke some other function. The problem is that the
column name is in the 'col' record and is different during the loop and at
each function call.
CREATE OR REPLACE FUNCTION replace_values() RETURNS trigger AS
$BODY$DECLARE col record; attname varchar;BEGIN FOR col IN SELECT DISTINCT pgaf.attname, pgaf.attnum
FROMpg_constraint, pg_attribute AS pgaf WHERE pg_constraint.contype = 'f' -- fkey AND pg_constraint.confrelid
=TG_RELID -- table oid AND pgaf.attrelid = TG_RELID AND pgaf.attnum = ANY(pg_constraint.confkey) LOOP
attname := col.attname; IF NEW.attname <> OLD.attname THEN RAISE NOTICE ' value changed from
"%"to "%"', OLD.attname, NEW.attname; -- INVOKE OTHER FUNCTION END IF; END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
--
Martin Edlman
Fortech Ltd.
57001 Litomysl, CZ
