Date comparison, user defined operators and magic - Mailing list pgsql-sql

-----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-----