Обсуждение: Date comparison, user defined operators and magic
-----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-----
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
I'm having a issue with a insert INSERT INTO iss.citystateinfo ( citystateinfoid, citystate, zipcode, cityname, statecode ) VALUES ( '31344342-3439-4135-2d32-3044462d3433', 'Orange Park,FL', '32065', 'Orange Park', 'FL' ); This inserts correctly, however when I view the data the citystate is always displayed as Orange Park (FL). Every row is displayed the same. I've used PGAdmin to look at the data, EMS for PostGres and have exported the data to Excel and they all display the data incorrectly. This is a issue for us because we want the user to type in either the zipcode if known to bring back the city and state, or the city,state to bring back the zipcode. Any idea's on why this could be happening. I'm running on Windows 7 x64 using 8.4.2 Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
On Sat, Feb 27, 2010 at 10:14 AM, Michael Gould <mgould@intermodalsoftwaresolutions.net> wrote: > I'm having a issue with a insert > > INSERT INTO > iss.citystateinfo > ( > citystateinfoid, > citystate, > zipcode, > cityname, > statecode > ) > VALUES ( > '31344342-3439-4135-2d32-3044462d3433', > 'Orange Park,FL', > '32065', > 'Orange Park', > 'FL' > ); > > This inserts correctly, however when I view the data the citystate is always > displayed as > > Orange Park (FL). Every row is displayed the same. I've used PGAdmin to > look at the data, EMS for PostGres and have exported the data to Excel and > they all display the data incorrectly. This is a issue for us because we > want the user to type in either the zipcode if known to bring back the city > and state, or the city,state to bring back the zipcode. > > Any idea's on why this could be happening. I'm running on Windows 7 x64 > using 8.4.2 When you display it, do you use pgadmin or psql? Does psql do the same thing? Can we see the ddl that created this table? Can you create a self-contained test-case that others can run and see the same results?
Here is the table DDL,
To test you can either use the record below or make up anything you want.
I've changed the isscontrib.citext to varchar and text data types and the
results are the same.
Best Regards
Michael Gould
CREATE TABLE "iss"."citystateinfo" (
"citystateinfoid" UUID DEFAULT isscontrib.uuid_generate_v4() NOT NULL,
"citystate" "isscontrib"."citext" NOT NULL,
"zipcode" CHAR(10) NOT NULL,
"cityname" "isscontrib"."citext" NOT NULL,
"statecode" VARCHAR(2) NOT NULL,
"latitude" NUMERIC(12,8),
"longitude" NUMERIC(12,8),
"countrycode" VARCHAR(2),
"activeflag" CHAR(1) DEFAULT 'Y'::bpchar NOT NULL,
"createdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"createuser" "isscontrib"."citext" DEFAULT "current_user"(),
"editdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"edituser" "isscontrib"."citext" DEFAULT "current_user"(),
CONSTRAINT "pk_citystateinfo" PRIMARY KEY("citystateinfoid"),
CONSTRAINT "citystateinfo_activeflag_check" CHECK (activeflag = ANY
(ARRAY['N'::bpchar, 'Y'::bpchar]))
) WITHOUT OIDS;
COMMENT ON TABLE "iss"."citystateinfo"
IS 'City State information for Mileage calculations';
COMMENT ON COLUMN "iss"."citystateinfo"."citystateinfoid"
IS 'Global Unique Identifier - CityStateInfo';
COMMENT ON COLUMN "iss"."citystateinfo"."citystate"
IS 'City State Combination in City, State';
COMMENT ON COLUMN "iss"."citystateinfo"."zipcode"
IS 'Postal Code';
COMMENT ON COLUMN "iss"."citystateinfo"."cityname"
IS 'City Name';
COMMENT ON COLUMN "iss"."citystateinfo"."statecode"
IS 'State Code';
COMMENT ON COLUMN "iss"."citystateinfo"."latitude"
IS 'Latitude';
COMMENT ON COLUMN "iss"."citystateinfo"."longitude"
IS 'Longitude';
COMMENT ON COLUMN "iss"."citystateinfo"."countrycode"
IS 'Country Code';
COMMENT ON COLUMN "iss"."citystateinfo"."activeflag"
IS 'Is Item Active?';
COMMENT ON COLUMN "iss"."citystateinfo"."createdatetime"
IS 'Time stamp record created';
COMMENT ON COLUMN "iss"."citystateinfo"."createuser"
IS 'Record Created by User';
COMMENT ON COLUMN "iss"."citystateinfo"."editdatetime"
IS 'Time stamp when record last changed';
COMMENT ON COLUMN "iss"."citystateinfo"."edituser"
IS 'Last User to change record';
CREATE INDEX "i_cityname" ON "iss"."citystateinfo"
USING btree ("citystate" "isscontrib"."citext_ops");
COMMENT ON INDEX "iss"."i_cityname"
IS 'Index by City Name';
CREATE INDEX "i_citystateinfozipcode" ON "iss"."citystateinfo"
USING btree ("zipcode");
COMMENT ON INDEX "iss"."i_citystateinfozipcode"
IS 'Index by ZipCode';
CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();
COMMENT ON TRIGGER "insertcitystateinfo" ON "iss"."citystateinfo"
IS 'Setup cityname column';
CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."timestampfunc"();
"Michael Gould" <mgould@intermodalsoftwaresolutions.net> wrote:
> I'm having a issue with a insert
>
> INSERT INTO
> iss.citystateinfo
> (
> citystateinfoid,
> citystate,
> zipcode,
> cityname,
> statecode
> )
> VALUES (
> '31344342-3439-4135-2d32-3044462d3433',
> 'Orange Park,FL',
> '32065',
> 'Orange Park',
> 'FL'
> );
>
> This inserts correctly, however when I view the data the citystate is
always
> displayed as
>
> Orange Park (FL). Every row is displayed the same. I've used PGAdmin to
> look at the data, EMS for PostGres and have exported the data to Excel and
> they all display the data incorrectly. This is a issue for us because we
> want the user to type in either the zipcode if known to bring back the
city
> and state, or the city,state to bring back the zipcode.
>
> Any idea's on why this could be happening. I'm running on Windows 7 x64
> using 8.4.2
>
> Best Regards
> --
> Michael Gould, Managing Partner
> Intermodal Software Solutions, LLC
> 904.226.0978
> 904.592.5250 fax
>
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> On Sat, Feb 27, 2010 at 10:14 AM, Michael Gould
> <mgould@intermodalsoftwaresolutions.net> wrote:
>> I'm having a issue with a insert
>>
>> INSERT INTO
>> iss.citystateinfo
>> (
>> citystateinfoid,
>> citystate,
>> zipcode,
>> cityname,
>> statecode
>> )
>> VALUES (
>> '31344342-3439-4135-2d32-3044462d3433',
>> 'Orange Park,FL',
>> '32065',
>> 'Orange Park',
>> 'FL'
>> );
>>
>> This inserts correctly, however when I view the data the citystate is
always
>> displayed as
>>
>> Orange Park (FL). Every row is displayed the same. I've used PGAdmin to
>> look at the data, EMS for PostGres and have exported the data to Excel
and
>> they all display the data incorrectly. This is a issue for us because we
>> want the user to type in either the zipcode if known to bring back the
city
>> and state, or the city,state to bring back the zipcode.
>>
>> Any idea's on why this could be happening. I'm running on Windows 7 x64
>> using 8.4.2
>
> When you display it, do you use pgadmin or psql? Does psql do the same
thing?
>
> Can we see the ddl that created this table? Can you create a
> self-contained test-case that others can run and see the same results?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Michael Gould <mgould@intermodalsoftwaresolutions.net> writes:
> Here is the table DDL,
Can't really test this since you didn't supply the source code for
those triggers:
> CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
> ON "iss"."citystateinfo" FOR EACH ROW
> EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();
> CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
> ON "iss"."citystateinfo" FOR EACH ROW
> EXECUTE PROCEDURE "iss"."timestampfunc"();
Personally I'm suspicious that the BEFORE INSERT trigger is changing the
data.
regards, tom lane
Tom, Thanks, that's exactly what it was. Best Regards Mike Gould "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Michael Gould <mgould@intermodalsoftwaresolutions.net> writes: >> Here is the table DDL, > > Can't really test this since you didn't supply the source code for > those triggers: > >> CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE >> ON "iss"."citystateinfo" FOR EACH ROW >> EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"(); > >> CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE >> ON "iss"."citystateinfo" FOR EACH ROW >> EXECUTE PROCEDURE "iss"."timestampfunc"(); > > Personally I'm suspicious that the BEFORE INSERT trigger is changing the > data. > > regards, tom lane > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax