From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pl/pgsql trigger function - compare *most* columns in NEW vs. OLD |
Date: | 2014-12-18 19:59:47 |
Message-ID: | CAHyXU0xLrLjgF2aDNQK9tQwp2aaGEUjhmwY3f6maSnA7zcNCdg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Dec 18, 2014 at 12:16 PM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
> Is there a simple notation for comparing most columns in the new and old
> records in a pl/pgsql trigger function? Something like
>
> (new.b, new.c, new.d) = (old.b, old.c, old.d)
>
> works to compare all the columns except 'a', but is fragile in that it needs
> to be updated any time a column is added to the table, and is rather messy
> looking when the actual table has many columns.
>
> Is there a better approach to detecting a change in any column except a few
> specific ones?
yes, via hstore
declare
diff hstore;
ignore_columns text[] default array['col1', 'col2'];
begin
diff := hstore(new) - hstore(old) - ignore_columns;
...
boom!
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Robert DiFalco | 2014-12-18 20:10:12 | Combining two queries |
Previous Message | harpagornis | 2014-12-18 18:56:31 | Re: SSL Certificates in Windows 7 & Postgres 9.3 |