Date: Tue, 20 Nov 2007 17:12:58 +0100
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: dynmic column names inside trigger?
Message-ID: <20071120161258.GA27106@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.17 (2007-11-01)

Hi,

I've got this trigger to clean up text entered in web forms:

	CREATE or replace FUNCTION sanitize_text() RETURNS "trigger"
		AS $$
	declare
	begin
		if old.story is not null and new.story != old.story
		then
			new.story = translate(new.story, E'\x92\x96', '''-');
			new.story = regexp_replace(new.story, E'\x9c', 'oe', 'g');
			new.story = regexp_replace(new.story, E'\x85', '...', 'g');
		end if;
		return new;
	end;
	$$
		LANGUAGE plpgsql;


	CREATE TRIGGER sanitize_text_trig
		BEFORE INSERT or update ON story
		FOR EACH ROW
		EXECUTE PROCEDURE sanitize_text();


I'd like to use it on other tables an columns but how can the column 
name be dynamic inside the procedure. Passing the column name in the 
trigger declaration and using it as NEW.TG_ARGV[0] seems out of the 
question.

Is there another solution out there?

Thanks,