pinker <pinker@onet.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