From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Re: dynmic column names inside trigger?
Date: Wed, 21 Nov 2007 09:14:14 +0100
Message-ID: <4743F6E5.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <20071120161258.GA27106@apartia.fr>
References: <20071120161258.GA27106@apartia.fr>
I would do something like this (not tested, but conceptually working):
CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text) AS
$body$
BEGIN
cleantext = translate(webtext, E'\x92\x96', '''-');
cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g');
cleantext = regexp_replace(cleantext, E'\x85', '...', 'g');
END;
$body$
LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT;
CREATE or replace FUNCTION func_trig_tbl_x() RETURNS "trigger" AS
$body$
BEGIN
if old.story is not null and new.story != old.story then
new.story = sanitize_text(new.story);
end if;
--checks on other field can be included here, eg
if old.otherfield is not null and new.otherfield != old.otherfield then
new.otherfield = sanitize_text(new.otherfield);
end if;
return new;
END;
$body$
LANGUAGE plpgsql;
CREATE TRIGGER trig_tbl_x
BEFORE INSERT or update ON tbl_x
FOR EACH ROW
EXECUTE PROCEDURE func_trig_tbl_x();
Now for tbl_y you can create a function func_trig_tbl_y (which again calls sanitize_text for any field you like) and a trigger trig_tbl_x.
It might be necessary to replace "cleantext OUT text" by "cleantext IN OUT text". I haven't tested it.
>>> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> 2007-11-20 17:12 >>>
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,
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
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: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: dynmic column names inside trigger?
In-Reply-To: <4743F6E5.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox