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
FROM pg_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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
