pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddynmic column names inside trigger?
9+ messages / 4 participants
[nested] [flat]
* dynmic column names inside trigger?
@ 2007-11-20 16:12 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-20 16:56 ` Re: dynmic column names inside trigger? Tom Lane <tgl@sss.pgh.pa.us>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 2 replies; 9+ messages in thread
From: Louis-David Mitterrand @ 2007-11-20 16:12 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-11-20 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-22 13:54 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-11-20 16:56 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-20 16:56 ` Re: dynmic column names inside trigger? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-22 13:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 9+ messages in thread
From: Louis-David Mitterrand @ 2007-11-22 13:54 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-11-21 08:14 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-11-22 13:54 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-23 17:06 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 2 replies; 9+ messages in thread
From: Bart Degryse @ 2007-11-21 08:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-11-22 13:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-22 14:11 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 9+ messages in thread
From: Louis-David Mitterrand @ 2007-11-22 13:54 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
2007-11-22 13:54 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-11-22 14:11 ` Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 9+ messages in thread
From: Bart Degryse @ 2007-11-22 14:11 UTC (permalink / raw)
To: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-11-23 17:06 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-23 20:54 ` Re: dynmic column names inside trigger? Michael Glaesemann <grzm@seespotcode.net>
2007-11-26 08:13 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 2 replies; 9+ messages in thread
From: Louis-David Mitterrand @ 2007-11-23 17:06 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
2007-11-23 17:06 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-11-23 20:54 ` Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Glaesemann @ 2007-11-23 20:54 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: dynmic column names inside trigger?
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Re: dynmic column names inside trigger? Bart Degryse <Bart.Degryse@indicator.be>
2007-11-23 17:06 ` Re: dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-11-26 08:13 ` Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 0 replies; 9+ messages in thread
From: Bart Degryse @ 2007-11-26 08:13 UTC (permalink / raw)
To: pgsql-sql
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 (
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-11-26 08:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-20 16:12 dynmic column names inside trigger? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-20 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-22 13:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-21 08:14 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-11-22 13:54 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-22 14:11 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-11-23 17:06 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-11-23 20:54 ` Michael Glaesemann <grzm@seespotcode.net>
2007-11-26 08:13 ` Bart Degryse <Bart.Degryse@indicator.be>
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