Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Fri, Oct 2, 2015 at 3:03 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> pinker <pinker(at)onet(dot)eu> wrote:
>
> > I've tried to write audit trigger which fires only when data
> > changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause
> > as described in documentation. Should this clause be independent
> > from data type? because an error occurs when I'm trying to modify
> > row with point data type:
> > ERROR: could not identify an equality operator for type point
>
> > CREATE TRIGGER trigger_update_test
> > AFTER UPDATE
> > ON test1
> > FOR EACH ROW
> > WHEN ((old.* IS DISTINCT FROM new.*))
> > EXECUTE PROCEDURE test_update();
>
> Since you seem to be on 9.4, how about this?:
>
> CREATE TRIGGER trigger_update_test
> AFTER UPDATE
> ON test1
> FOR EACH ROW
> WHEN ((old *<> new))
> EXECUTE PROCEDURE test_update();
>
>
> http://www.postgresql.org/docs/9.4/static/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON
>
> Which says (in part):
>
> | To support matching of rows which include elements without a
> | default B-tree operator class, the following operators are
> | defined for composite type comparison: *=, *<>, *<, *<=, *>, and
> | *>=. These operators compare the internal binary representation
> | of the two rows. Two rows might have a different binary
> | representation even though comparisons of the two rows with the
> | equality operator is true. The ordering of rows under these
> | comparison operators is deterministic but not otherwise
> | meaningful. These operators are used internally for materialized
> | views and might be useful for other specialized purposes such as
> | replication but are not intended to be generally useful for
> | writing queries.
>
> It seems to me that auditing would be an appropriate use, because
> it would show whether there was any change in the stored value, not
> just whether the old and new values were equal in a btree ordering
> comparison. For example, if a citext column were changed from 'a'
> to 'A', it would compare as equal with its type's "=" operator, but
> the row would show as changed anyway, if you use "*=" or "*<>".
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
Hi,

Would you please provide the link to the section in the documentation that
you are referring to because I'm new to PostgreSQL and I didn't know WHEN
could be used outside of CASE and EXCEPTION blocks.

Thanks.

Dane
​