On Fri, Oct 2, 2015 at 3:03 PM, Kevin Grittner <kgri...@ymail.com> wrote:
> pinker <pin...@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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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