Thread: dynmic column names inside trigger?

dynmic column names inside trigger?

From

Louis-David Mitterrand

Date:

20 November 2007, 15:13:10

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,

Re: dynmic column names inside trigger?

From

Tom Lane

Date:

20 November 2007, 15:57:31

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

Re: dynmic column names inside trigger?

From

"Bart Degryse"

Date:

21 November 2007, 07:14:39

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

Re: dynmic column names inside trigger?

From

Louis-David Mitterrand

Date:

22 November 2007, 12:54:16

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,

Re: dynmic column names inside trigger?

From

Louis-David Mitterrand

Date:

22 November 2007, 12:55:33

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,

Re: dynmic column names inside trigger?

From

"Bart Degryse"

Date:

22 November 2007, 13:11:59

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

Re: dynmic column names inside trigger?

From

Louis-David Mitterrand

Date:

23 November 2007, 16:06:18

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?

Re: dynmic column names inside trigger?

From

Michael Glaesemann

Date:

23 November 2007, 19:54:30

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

Re: dynmic column names inside trigger?

From

"Bart Degryse"

Date:

26 November 2007, 07:13:42

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