Message-ID: <47FA034B.7090201@fortech.cz>
Date: Mon, 07 Apr 2008 13:19:39 +0200
From: Martin Edlman <edlman@fortech.cz>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: pl/PgSQL, variable names in NEW
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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