Re: Update on tables when the row doesn't change - Mailing list pgsql-general

Dawid Kuroczko wrote:

>>>Control question, I didn't check it, but would it be enough to change from:
>>>   UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
>>>to:
>>>   UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id AND text1 <> NEW.text1?
>>>
>>>...  I may be wrong. :)
>>
>>Yes, thats more elegant then my other (4th) solution.
>>Was late yesterday evening ;)
>
>
> Be wary of the NULL values though. :)  Either don't use them, add
> something like 'AND (text1 <> NEW.text1 OR text1 IS NULL OR NEW.text1
> IS NULL)' or something more complicated. :)

Thanks for the notice, but I have a special operator for this:

CREATE OR REPLACE FUNCTION different (ANYELEMENT, ANYELEMENT) RETURNS
BOOLEAN AS $$
   BEGIN
    IF ($1 <> $2) OR ($1 IS NULL <> $2 IS NULL) THEN
       RETURN TRUE;
    ELSE
      RETURN FALSE;
   END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR <<>> (
    LEFTARG = ANYELEMENT,
    RIGHTARG = ANYELEMENT,
    PROCEDURE = different,
    COMMUTATOR = <<>>,
    NEGATOR = ====
);

Sebastian