comparing NEW and OLD (any good this way?)

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: comparing NEW and OLD (any good this way?)
Date: 2009-07-23 11:40:45
Message-ID: 1dd6057e0907230440x52c8c92fg55925f4527ca1ca1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

My colleage Geard Troost and I found a handy way of comparing OLD and
NEW in a trigger function.
Normally this does not work (if anyone can tell me why, that'd be
great), but once you cast them to text, it does.

Is there anything to say against this, or can i go ahead and recommend
this to everyone who wants to check if anything changed before doing
what their update triggers do?

Cheers,

WBL

Here's the code:

drop table test;
create table test (id integer primary key, value integer);
insert into test values (1,1);
insert into test values (2,1);
insert into test values (3,1);
insert into test values (4,1);
insert into test values (5,1);
insert into test values (6,1);

create or replace function bla() returns trigger as $$
begin
IF (NEW::TEXT = OLD::TEXT) THEN raise notice 'changed';
END IF;
return NEW;
end
$$
language plpgsql;

CREATE TRIGGER test_bla BEFORE UPDATE
ON test FOR EACH ROW
EXECUTE PROCEDURE public.bla();

update test set value =NULL where id= 1;
update test set value =NULL where id= 1;

--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2009-07-23 11:45:36 Re: comparing NEW and OLD (any good this way?)
Previous Message Marcin Gon 2009-07-23 11:38:23 ERROR: unexpected data beyond EOF in block of relation "RelationName"