Обсуждение: dynmic column names inside trigger?
Hi, I've got this trigger to clean up text entered in web forms: CREATE or replace FUNCTION sanitize_text() RETURNS "trigger" AS $$declarebegin 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,
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes:
> I'd like to use it on other tables an columns but how can the column
> name be dynamic inside the procedure.
It can't --- plpgsql has no support for that. You could probably make
it work in some of the other PL languages, such as plperl or pltcl,
which are less strongly typed.
regards, tom lane
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;
$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;
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;
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();
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
>>> 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
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): Hello, > 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; But if I test a non-existent column for not being null I will have an exception, no? Otherwise this is a nice way of doing it. Thanks,
On Tue, Nov 20, 2007 at 11:56:02AM -0500, Tom Lane wrote: > Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes: > > I'd like to use it on other tables an columns but how can the column > > name be dynamic inside the procedure. > > It can't --- plpgsql has no support for that. You could probably make > it work in some of the other PL languages, such as plperl or pltcl, > which are less strongly typed. Hi Tom, What the performance penality of using plperl vs. plpgsql ? Thanks,
I don't really see how you could test a non-existing column. Here
> if old.story is not null and new.story != old.story then
> new.story = sanitize_text(new.story);
you would always use fields from OLD and NEW otherwise you can't even create the trigger.
> new.story = sanitize_text(new.story);
you would always use fields from OLD and NEW otherwise you can't even create the trigger.
If a table has 3 fields (field1, field2 and field3) then why would you create a trigger in which
you do something with field4. I haven't tried (because it seems so absurd) but I expect
PG to refuse to create such a trigger.
>>> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> 2007-11-22 14:54 >>>
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> I would do something like this (not tested, but conceptually working):
Hello,
> 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;
But if I test a non-existent column for not being null I will have an
exception, no?
Otherwise this is a nice way of doing it.
Thanks,
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > 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; Hi, I was curious as to why you created this function with a prototype of func(intext IN text, outtext OUT text) ... returns NULL instead of the usual func(intext text) ... returns TEXT Is that a more efficient way?
On Nov 23, 2007, at 12:06 , Louis-David Mitterrand wrote: > On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: >> LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; > > Hi, > > I was curious as to why you created this function with a prototype of > > func(intext IN text, outtext OUT text) ... returns NULL Don't confuse RETURNS NULL ON NULL INPUT with the function return value: this is the verbose spelling of STRICT. Michael Glaesemann grzm seespotcode net
Functionally it's the same. The difference is that you don't have to DECLARE a variable for assembling your return value.
It's either
func(intext IN text, outtext OUT text .... returns NULL AS (
BEGIN
...
return;
END;
);
or
func(intext IN text) returns text AS (
func(intext IN text) returns text AS (
DECLARE
outtext text;
BEGIN
...
return outtext;
END;
);
I find it especially handy when I want the function to return multiple values (thus a record) which are not of a table type.
The second way you would have to define a type for your return values first and than use it in your function
"My" way you would just write
func(intext IN text, outfield1 OUT text, outfield2 OUT date, outfield3 OUT integer) returns NULL AS (...);
A third way is to define the return value(s) inside the function as type record but than you have to name the output when you call the function
func(intext IN text) returns record AS (
DECLARE
rec record;
BEGIN
...
return rec;
END;
);
SELECT * FROM func('my input text') AS (field1 text, field2 date, field3 integer);
I find this less intuitive.
Buth in the end you just choose the technique you like best. I'm not aware of any performance penalties for either technique. Anyone?
>>> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> 2007-11-23 18:06 >>>
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> 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;
Hi,
I was curious as to why you created this function with a prototype of
func(intext IN text, outtext OUT text) ... returns NULL
instead of the usual
func(intext text) ... returns TEXT
Is that a more efficient way?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster