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