comparing NEW and OLD (any good this way?)
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
Hello
regards
Pavel Stehule
2009/7/23 Willy-Bas Loos <willybas@gmail.com>:
Show quoted text
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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Pavel Stehule, 23.07.2009 13:45:
Hello
regards
Pavel Stehule
That collection of tips is really nice.
Why isn't there a link from the Postgres Wiki to your page?
Regards
Thomas
Pavel Stehule schrieb:
Hello
regards
Pavel Stehule
Pavel, this trick-list is awesome ;-) Thanks for the tip!
Cheers
Andy
P.S.: a link to that would be nice ;-)
2009/7/23 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>:
Pavel Stehule schrieb:
Hello
regards
Pavel StehulePavel, this trick-list is awesome ;-) Thanks for the tip!
Cheers
Andy
P.S.: a link to that would be nice ;-)
look on http://wiki.postgresql.org/wiki/Category:Snippets
There are link on tricks page.
Pavel
Pavel Stehule, 23.07.2009 14:50:
That page is not accessible from the Wiki's main page (at least I can't find an easy way to navigate there)
I think there should be a prominent link right at the start page that links to that page and your excellent collection.
Regards
Thomas
On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:
Hello
note: in PostgreSQL 8.4, you can compare record variables directly
with standard boolean operators.
merlin
On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote:
Just had a quick flick through your list and one of the early ones stuck
out:
is scary; even worse is that it was changed to be like this in 8.2
because the standard says it should behave this way. What on earth were
they thinking when they defined the standard this way?
--
Sam http://samason.me.uk/
2009/7/23 Merlin Moncure <mmoncure@gmail.com>:
On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:
Hello
note: in PostgreSQL 8.4, you can compare record variables directly
with standard boolean operators.
actualised
Thank You
Pavel
Show quoted text
merlin
On Thu, Jul 23, 2009 at 01:40:45PM +0200, Willy-Bas Loos wrote:
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?
Perhaps 8.4's suppress_redundant_updates_trigger() could be helpful in this
case:
http://www.postgresql.org/docs/8.4/interactive/functions-trigger.html
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
Thomas Kellerer wrote:
Pavel Stehule, 23.07.2009 14:50:
That page is not accessible from the Wiki's main page (at least I
can't find an easy way to navigate there)I think there should be a prominent link right at the start page that
links to that page and your excellent collection.
Agreed, just added one.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote:
On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote:
Just had a quick flick through your list and one of the early ones stuck
out:is scary; even worse is that it was changed to be like this in 8.2
because the standard says it should behave this way. What on earth were
they thinking when they defined the standard this way?
since any comparson involving those tuples will return NULL true is the
correct value for IS NULL
if you are bothered by this behavior you are misusing NULL.
On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote:
On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote:
is scary; even worse is that it was changed to be like this in 8.2
because the standard says it should behave this way. What on earth were
they thinking when they defined the standard this way?since any comparson involving those tuples will return NULL true is the
correct value for IS NULL
I think you missed the point:
SELECT r IS NULL, r IS NOT NULL
FROM (VALUES (1,NULL)) r(a,b);
returns FALSE for *both* columns. How can a row be both NULL *and*
non-NULL?
if you are bothered by this behavior you are misusing NULL.
I understand that this is the specified behavior, and hence PG is
correctly following the spec--but it still bothers me.
--
Sam http://samason.me.uk/
On Wed, Jul 29, 2009 at 9:40 AM, Sam Mason<sam@samason.me.uk> wrote:
On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote:
On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote:
is scary; even worse is that it was changed to be like this in 8.2
because the standard says it should behave this way. What on earth were
they thinking when they defined the standard this way?since any comparson involving those tuples will return NULL true is the
correct value for IS NULLI think you missed the point:
SELECT r IS NULL, r IS NOT NULL
FROM (VALUES (1,NULL)) r(a,b);returns FALSE for *both* columns. How can a row be both NULL *and*
non-NULL?if you are bothered by this behavior you are misusing NULL.
I understand that this is the specified behavior, and hence PG is
correctly following the spec--but it still bothers me.
not only that, but while pg's treats composite types with null members
as null according to the 'is null' operator (in accordance with the
spec), but as not null everywhere else. thus, for example, a 'null'
composite type is counted in the count() aggregate function. how
funky is that?
merlin
SELECT r IS NULL, r IS NOT NULL
FROM (VALUES (1,NULL)) r(a,b);returns FALSE for *both* columns. How can a row be both NULL *and*
non-NULL?
Actually, the value is neither NULL, nor non-NULL.
Part of it is NULL and part of it isn't so neither "IS NULL" is true,
nor is "IS NOT NULL"
cheers,
WBL
On Wed, Jul 29, 2009 at 3:40 PM, Sam Mason<sam@samason.me.uk> wrote:
On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote:
On 2009-07-23, Sam Mason <sam@samason.me.uk> wrote:
is scary; even worse is that it was changed to be like this in 8.2
because the standard says it should behave this way. What on earth were
they thinking when they defined the standard this way?since any comparson involving those tuples will return NULL true is the
correct value for IS NULLI think you missed the point:
SELECT r IS NULL, r IS NOT NULL
FROM (VALUES (1,NULL)) r(a,b);returns FALSE for *both* columns. How can a row be both NULL *and*
non-NULL?if you are bothered by this behavior you are misusing NULL.
I understand that this is the specified behavior, and hence PG is
correctly following the spec--but it still bothers me.--
Sam http://samason.me.uk/--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw
On Wed, Aug 12, 2009 at 10:51:04AM +0200, Willy-Bas Loos wrote:
SELECT r IS NULL, r IS NOT NULL
FROM (VALUES (1,NULL)) r(a,b);returns FALSE for *both* columns. How can a row be both NULL *and*
non-NULL?Actually, the value is neither NULL, nor non-NULL.
Part of it is NULL and part of it isn't so neither "IS NULL" is true,
nor is "IS NOT NULL"
Nope, I still don't get it. Why treat rows specially? If this was
true, then what should:
SELECT a IS NULL, a IS NOT NULL
FROM (SELECT ARRAY [1,NULL]) x(a);
evaluate to? As "part of it" is NULL and part isn't then, by your
reasoning, it should return TRUE for both. PG doesn't and I think this
is much more useful behavior. The value itself is not unknown, it just
happens to contain some unknown values. Having a row that consists
entirely of NULL values being treated as NULL is OK, but some weird
halfway house is horrible. Standards' conforming, but still horrible.
--
Sam http://samason.me.uk/
Sam Mason wrote:
Nope, I still don't get it. Why treat rows specially? If this was
true, then what should:SELECT a IS NULL, a IS NOT NULL
FROM (SELECT ARRAY [1,NULL]) x(a);evaluate to? As "part of it" is NULL and part isn't then, by your
reasoning, it should return TRUE for both. PG doesn't and I think this
is much more useful behavior.
But IS NULL applied to an array is useless to test if there are null values
inside, whereas this is apparently the whole point of IS NULL applied to
rows.
I mean:
select a is null from (select array[null]) x(a);
returns false, as well as:
select a is null from (select array[1]) x(a);
When applied to rows, if you consider that:
- is null applied to a row means that all columns are null
- is not null applied to a row means that all columns are not null
which is what the standard seems to dictate, then these operators make sense
and are probably useful in some situations.
Now there is the unfortunate consequence that (r is null) is not equivalent
to (not (r is not null)), yet it's not the standard's fault if "not all
values are null" is not the same as "all values are not null", that's just
set logic.
Maybe they could have made this easier for us by naming the operators
differently, such as "is entirely null" and "is entirely not null"
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Wed, Aug 12, 2009 at 10:14 AM, Daniel Verite<daniel@manitou-mail.org> wrote:
Sam Mason wrote:
Nope, I still don't get it. Why treat rows specially? If this was
true, then what should:SELECT a IS NULL, a IS NOT NULL
FROM (SELECT ARRAY [1,NULL]) x(a);evaluate to? As "part of it" is NULL and part isn't then, by your
reasoning, it should return TRUE for both. PG doesn't and I think this
is much more useful behavior.But IS NULL applied to an array is useless to test if there are null values
inside, whereas this is apparently the whole point of IS NULL applied to
rows.
I mean:
select a is null from (select array[null]) x(a);
returns false, as well as:
select a is null from (select array[1]) x(a);When applied to rows, if you consider that:
- is null applied to a row means that all columns are null
- is not null applied to a row means that all columns are not null
which is what the standard seems to dictate, then these operators make sense
and are probably useful in some situations.Now there is the unfortunate consequence that (r is null) is not equivalent
to (not (r is not null)), yet it's not the standard's fault if "not all
values are null" is not the same as "all values are not null", that's just
set logic.Maybe they could have made this easier for us by naming the operators
differently, such as "is entirely null" and "is entirely not null"
IMO, the standard really blew it.
PostgreSQL's approach is ok, minimal standards compliance balanced out
with practical considerations. This leads to some strange behaviors
as noted upthread, but it's workable if you know the tricks. I guess
it's not very well documented....
merlin
On Wed, Aug 12, 2009 at 04:14:31PM +0200, Daniel Verite wrote:
But IS NULL applied to an array is useless to test if there are null values
inside, whereas this is apparently the whole point of IS NULL applied to
rows.
I mean:
select a is null from (select array[null]) x(a);
returns false, as well as:
select a is null from (select array[1]) x(a);
Yes, I know. But it seems to be a somewhat arbitrary choice to handle
IS NULL for rows differently from everything else.
When applied to rows, if you consider that:
- is null applied to a row means that all columns are null
- is not null applied to a row means that all columns are not null
which is what the standard seems to dictate, then these operators make sense
and are probably useful in some situations.
Yes, I understand what it's specified to do and that it's consistent
with SQL spec. I just think (and Merlin seems to agree) that the spec
has specified the "wrong" behavior.
Now there is the unfortunate consequence that (r is null) is not equivalent
to (not (r is not null)), yet it's not the standard's fault if "not all
values are null" is not the same as "all values are not null", that's just
set logic.
Yes; but this means the user now has to be aware of exactly which type
their code is using as the behavior of various things will magically
change in rare circumstances.
Maybe they could have made this easier for us by naming the operators
differently, such as "is entirely null" and "is entirely not null"
Yes, this would be *much* more preferable. For people aware of it this
it's obviously an easy translation to make, but it's a nasty waiting for
those who aren't and especially for anybody doing anything formal. I.e.
when reasoning about operator semantics you suddenly have to know the
type of data you're dealing with before you can say useful things about
the result. There will of course be ways of avoiding the general case
of an exponential increase in complexity, but it's still nasty.
Anybody else think this thread is past it's bed time and should be put
to rest?
--
Sam http://samason.me.uk/
Sam Mason wrote:
But it seems to be a somewhat arbitrary choice to handle
IS NULL for rows differently from everything else.
For scalar or array types, "is null" means that the value happens to be that
special value that we call null. No conceptual problem here.
But for rows, there is no such thing. You can't assign null to a row, it
makes no sense and actually causes an error.
Starting from that point, what consistency can we expect for the "is null"
operator across row types and other types?
Yes, I understand what it's specified to do and that it's consistent
with SQL spec. I just think (and Merlin seems to agree) that the spec
has specified the "wrong" behavior.
So for you guys, what would be the "right" behavior?
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
