Thread: Generic triggers ?

Hi,

I wanted to setup a simple "generic" type trigger.  What a mean by generic is that i don't want to hardcode the NEW.<column> or OLD.<column> calls, i searched for a way to loop over the NEW/OLD rowtypes but could not figure out how it should be done.

This led me to attempt to do it in a way which i think is incorrect, first off i extract the column names for the given table (TG_RELNAME) from the information schema like so

-- extract the column names from tab
FOR colname IN (SELECT column_name FROM information_schema.columns WHERE table_name = tab) LOOP
   colnames[counter] := colname.column_name;
   counter := counter + 1;
END LOOP;

Where tab is TG_RELNAME, i then store them in a varchar array (i have verified that the values contained are correct).

If the TG_OP is 'UPDATE' i then wanted to figure out what column had been changed (at a later date i might also list the new and old vaules), so the plan was to do something like this:

ELSIF (TG_OP = 'UPDATE') THEN
   FOR i IN 1 .. counter LOOP
      IF(NEW.colnames[i] <> OLD.colnames[i])THEN
         -- only for testing, to see if a change in a column can be detected
         values_changed := values_changed || colnames[i] || ' was changed';
      END IF;
   END LOOP;
  
   -- values_changed currently contains rubbish
   INSERT INTO audit VALUES (audit_id, 'UPDATE', values_changed, '', now(), TG_RELNAME);
ELSE

The above code does nothing useful and is only to test out the concept.  The problem comes from NEW.colnames[i] because the NEW rowtype does not have a column called colnames.  Can anyone tell me how to append the value of colnames[i] to NEW so it would work, or is it even possible to iterate of the NEW/OLD rowtype without having to know the column names ?

Thanks in advance,

Andy

On Tue, Apr 24, 2007 at 05:35:09PM +0200, Andy Dale wrote:
> Hi,
>
> I wanted to setup a simple "generic" type trigger.  What a mean by generic
> is that i don't want to hardcode the
> NEW.<column> or OLD.<column> calls, i searched for a way to loop over
> the NEW/OLD rowtypes but could not figure out how it should be done.

You're trying to do something that pl/pgsql it notoriously bad at. I
suggest you use some more dynamic language like perl/python/etc.

> The above code does nothing useful and is only to test out the concept.  The
> problem comes from NEW.colnames[i] because the NEW rowtype does not have a
> column called colnames.  Can anyone tell me how to append the value of
> colnames[i] to NEW so it would work, or is it even possible to iterate of
> the NEW/OLD rowtype without having to know the column names ?

Not in pl/pgsql (being statically typed). In some other languages you can.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Andy Dale wrote:
> Hi,
>
> I wanted to setup a simple "generic" type trigger.  What a mean by generic
> is that i don't want to hardcode the
> NEW.<column> or OLD.<column> calls, i searched for a way to loop over
> the NEW/OLD rowtypes but could not figure out how it should be done.

Don't use plpgsql - that's the secret. Try pltcl or plperl or similar.
PL/pgsql is a bit too "statically typed" for it.

--
   Richard Huxton
   Archonet Ltd

Thanks for the quick response, i think will try it with python or perl.


Cheers,

Andy