pl/PgSQL, variable names in NEW
От | Martin Edlman |
---|---|
Тема | pl/PgSQL, variable names in NEW |
Дата | |
Msg-id | 47FA034B.7090201@fortech.cz обсуждение исходный текст |
Ответы |
Re: pl/PgSQL, variable names in NEW
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: