public inbox for [email protected]  

help / color / mirror / Atom feed

From: Martin Edlman <[email protected]>
To: [email protected]
Subject: pl/PgSQL, variable names in NEW
Date: Mon, 07 Apr 2008 13:19:39 +0200
Message-ID: <[email protected]> (raw)

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

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pl/PgSQL, variable names in NEW
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox