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 !=3D old.story then new.story =3D translate(new.story, E'\x92\x96', '''-'); new.story =3D regexp_replace(new.story, E'\x9c', 'oe', 'g'); new.story =3D 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=20 name be dynamic inside the procedure. Passing the column name in the=20 trigger declaration and using it as NEW.TG_ARGV[0] seems out of the=20 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 --=__Part42644BC6.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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 =3D translate(webtext, = E'\x92\x96', '''-');
cleantext =3D regexp_replace(cleantext, E'\x9c', = 'oe', 'g');
cleantext =3D 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 !=3D = old.story then
new.story =3D sanitize_text(new.story);
end = if;

--checks on other field can be included here, eg

if old.otherfield is not null and new.otherfield !=3D old.otherfield = then

new.otherfield =3D 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 !=3D old.story
then
new.story =3D translate(new.story, = E'\x92\x96', '''-');
new.story =3D regexp_replace(new.story, E'\x9c', = 'oe', 'g');
new.story =3D regexp_replace(new.story, E'\x85', '...', = 'g');
end if;
return new;
end;
$$
LANGUAGE plpgsql;

<= BR>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.<= BR>
Is there another solution out there?

Thanks,

---------= ------------------(end of broadcast)---------------------------
TIP 3: = Have you checked our extensive FAQ?

&n= bsp; http://www.postgresql.org/docs/faq

--=__Part42644BC6.0__=--