Re: Empty Updates, ON UPDATE triggers and Rules

On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure <mmoncure(at)gmail(dot)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