Re: Empty Updates, ON UPDATE triggers and Rules

On Thu, Aug 6, 2009 at 3:31 PM, Josh Trutwin<josh(at)trutwins(dot)homeip(dot)net> wrote:
> 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....

yes, there is a similar, more circuitous way, that should work for 8.1
IIRC you have to calll record_out to get the text for the record (the
cast is just shorthand for that).

merlin