pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate comparison, user defined operators and magic
2+ messages / 2 participants
[nested] [flat]
* Date comparison, user defined operators and magic
@ 2010-02-27 03:08 Petru Ghita <petrutz@venaver.info>
2010-02-27 03:32 ` Re: Date comparison, user defined operators and magic Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Petru Ghita @ 2010-02-27 03:08 UTC (permalink / raw)
To: 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-----
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Date comparison, user defined operators and magic
2010-02-27 03:08 Date comparison, user defined operators and magic Petru Ghita <petrutz@venaver.info>
@ 2010-02-27 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-02-27 03:32 UTC (permalink / raw)
To: Petru Ghita <petrutz@venaver.info>; +Cc: pgsql-sql
Petru Ghita <petrutz@venaver.info> writes:
> Hello everybody, I was missing a comparison operator for DATE so I
> wrote one after a really fast look into the documentation.
Huh?
regression=# \do <
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
------------+------+-----------------------------+-----------------------------+-------------+---------------------
...
pg_catalog | < | date | date | boolean | less-than
...
> 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!
They were all there before. I doubt it was using yours even in the <
case, because pg_catalog is normally at the front of the search path.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-02-27 03:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-27 03:08 Date comparison, user defined operators and magic Petru Ghita <petrutz@venaver.info>
2010-02-27 03:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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