Re: Parameterized trigger?

Write a do instead rule for updates and inserts
which wraps the fields with trim(). This is probably simpler.

To make it a regular function and not a trigger function.
Then create a query string
Then use execute to run the query;

create or replace function trimme( text )
returns void
as '
DECLARE
qry text;
field alias for $1;
BEGIN
qry := ''update foo set '' || field || ''=trim('' || field || '')'';
execute qry;
return;
END;
' language 'plpgsql';

You will have to do what you want to do to pass in
an array of field names, but maybe you'd rather do it
in a select loop.

I have some nice routines from Joe Conway on array handling
if you really need to use the arrays. They are more
complicated for 7.3 than in 7.4.

If you insist on a trigger function you are in trouble because
the only argument you get passed in is the NEW and OLD rows.

It is possible iterate over each field in the new row,
however. Someone recently had something that compared OLD
and NEW but my memory fades.

In plpythonu I think (untested) you can access columns
by number. Trigger functions can be written in plpythonu.
To access the fields of NEW use: TD["new"][0][n]

elein

On Tue, Sep 09, 2003 at 04:13:43PM -0700, David Fetter wrote:
> Kind people,
>
> I'd like to set quite a few fields in a DB to be trimmed of leading
> and trailing whitespace. My idea for doing it is to write a Pl/PgSQL
> function that gets called on insert or update, but takes the
> fieldname(s) as an array. Function looks something like this:
>
> CREATE OR REPLACE FUNCTION trim_white(VARCHAR[]) RETURNS TRIGGER AS '
> lower INTEGER;
> upper INTEGER;
> i INTEGER;
> BEGIN
> -- Get the upper & lower bounds somehow, and
> FOR i = lower .. upper LOOP
> NEW.$1[i] := btrim(NEW.$1[i], ' \t\n\r');
> RETURN NEW;
> END LOOP;
> END;
> ' LANGUAGE 'plpgsql';
>
> Then write triggers on each affected table that call trim_white.
>
> Is this workable as designed? If not, what is? I'm running 7.3.4, if
> that matters...
>
> TIA for any hints on this :)
>
> Cheers,
> D
> --
> David Fetter david(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 cell: +1 415 235 3778