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