Re: Empty Updates, ON UPDATE triggers and Rules

On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure <mmoncure@gmail.com> wrote:

> CREATE OR REPLACE FUNCTION set_last_modified ()
> RETURNS TRIGGER
> AS $$
> BEGIN
>   IF NEW != OLD THEN  -- 8.4 syntax
>     NEW.last_modified = NOW();
>   END IF;
>
>   RETURN NEW;
> END;
> $$ LANGUAGE PLPGSQL;

Interestingly, this syntax is accepted in 8.3.7, but SELECT queries
fail:

CREATE TRIGGER trigger_test_upd_set_last_mod
BEFORE UPDATE ON test_upd
FOR EACH ROW EXECUTE PROCEDURE set_last_modified();

Then:

UPDATE test_upd SET foo = 'foo' WHERE id = 1;
ERROR:  operator does not exist: test_upd <> test_upd
LINE 1: SELECT   $1  !=  $2
                     ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts. QUERY:  SELECT   $1  !=  $2
CONTEXT:  PL/pgSQL function "set_last_modified_test" line 2 at IF

This seems to be working fine on 8.3 though:

>   IF old::text != new::text THEN

Are there any solutions pre 8.3?  We still have some 8.1 installs....

Thanks!

Josh