pinker <pinker@xxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
