From: Petru Ghita <petrutz@venaver.info>
To: pgsql-sql@postgresql.org
Subject: Date comparison, user defined operators and magic
Date: Sat, 27 Feb 2010 04:08:31 +0100
Message-ID: <4B888CAF.6030101@venaver.info> (raw)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello everybody, I was missing a comparison operator for DATE so I
wrote one after a really fast look into the documentation. Working
with version 8.4.
create or replace function vav_date_posterior(date, date) RETURNS
boolean AS $$
- -- return TRUE if $1 < $2, FALSE otherway
DECLARE
d2 ALIAS FOR $1;
d1 ALIAS FOR $2;
result BOOLEAN;
delta1 interval;
delta2 interval;
ini_date date;
BEGIN
ini_date := cast ('101-01-01' as date);
delta1 := d1 - ini_date;
delta2 := d2 - ini_date;
result := false;
if (delta1 > delta2) then
result := true;
end if;
return result;
END;
$$
LANGUAGE plpgsql;
CREATE OPERATOR < (
leftarg = date,
rightarg = date,
procedure = vav_date_posterior,
commutator = <
);
Then I tested it:
select vav_date_posterior(date '2001-01-2', date '2001-03-20'),
vav_date_posterior(date '2002-01-3', date '2001-03-20'),
vav_date_posterior(date '2001-01-4', date '2001-01-4'),
date '2001-01-5' <> date '2001-01-5',
date '2001-01-5' > date '2001-01-5',
date '2001-01-5' < date '2001-01-5',
date '2001-01-5' = date '2001-01-5',
date '2001-01-6' > date '2001-01-5',
date '2001-01-6' < date '2001-01-5',
date '2001-01-5' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-5',
date '2001-01-6' >= date '2001-01-5',
date '2001-01-5' <= date '2001-01-15';
And EVERYTHING was working!
So I started to wonder how is this possible because after doing the
first comparison using the < operator I really wasn't expecting any of
the other operators to work at all. But they did!
I thought ok, the > operator was inferred as it's the inverse function
for the operator I have just defined. As this one was magically
inferred, probably the equal operator was also automagically created
as the exclusion of the other two, so if A > B is FALSE, and B > A is
FALSE, we can assume that A = B. As the for the <>, >=, <=, the logic
from this point on would be quite straight forward.
The problem is that I then went back to the documentation and I red
the next page:
http://www.postgresql.org/docs/8.4/interactive/xoper-optimization.html
After reading that I understand that I'd actually have to go remove
the COMMUTATOR keyword from there as the function is not commutative
one, add a NEGATOR, define the > operator and do the same, and then go
for the = operator and so on.
But the thing is it's working...
So question is:
Is it this normal behavior?
Could someone please give a working example or a pointer to an
implemented comparison function?
Could someone please point me to the fastest way to do DATE comparison?
What would it be the fastest way of correctly implementing comparison
operators for the DATE type?
Thank you very much in advance,
Petru Ghita
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkuIjK8ACgkQt6IL6XzynQSDRQCfVLY8XEUZXUyMFWTiCzbG6PqE
828An3v47bGjM9p4oXltivmZZ+UFe6kr
=761N
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: petrutz@venaver.info
Subject: Re: Date comparison, user defined operators and magic
In-Reply-To: <4B888CAF.6030101@venaver.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox