Обсуждение: comparing OLD and NEW in update trigger..

Hi there,

i'm planning to use the following trigger function to update a timestamp of
a row when it is UPDATEd:

CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$
BEGIN
         NEW.modify_timestamp := now();
END;
$$ LANGUAGE SQL;

Since i like to use the same trigger procedure for various tables, i'm
planning to keep it very generic.

What i'd like to do now is to just update the modify_timestamp column if OLD
and NEW are different. I'd LOOP over the row elements, and compare each
column of OLD with NEW, and bailing out if there's a difference.

I'd appreciate your help on the following two questions:

- How can i get the column names of NEW/OLD? Is there a set returning
function for this?
- Is there a more efficient way to compare whole rows?

thanks,

Alex Mayrhofer
---
http://nona.net/features/map/

Alex Mayrhofer wrote:
>
> Hi there,
>
> i'm planning to use the following trigger function to update a timestamp
> of a row when it is UPDATEd:
>
> CREATE OR REPLACE modified_trigger() RETURNS opaque AS $$
> BEGIN
>         NEW.modify_timestamp := now();
> END;
> $$ LANGUAGE SQL;

I don't think you can write a trigger function in "SQL" - you'll want
one of the procedural languages: plpgsql / plperl / pltcl etc.

> Since i like to use the same trigger procedure for various tables, i'm
> planning to keep it very generic.
>
> What i'd like to do now is to just update the modify_timestamp column if
> OLD
> and NEW are different. I'd LOOP over the row elements, and compare each
> column of OLD with NEW, and bailing out if there's a difference.
>
> I'd appreciate your help on the following two questions:
>
> - How can i get the column names of NEW/OLD? Is there a set returning
> function for this?

You'll want one of the interpreted languages: pltcl / plperl / plphp
etc. You'll find plpgsql can't cope with the sort of dynamic-typing
required to do this easily.

> - Is there a more efficient way to compare whole rows?

No.

--
   Richard Huxton
   Archonet Ltd