From: Michael Fuhr <mike@fuhr.org>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Thomas Seeber <thomas.seeber@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres 7.3 migrate to 8.0 date problems.
Date: Wed, 30 Mar 2005 20:11:54 -0700
Message-ID: <20050331031154.GA38479@winnie.fuhr.org> (raw)
In-Reply-To: <1112217814.28784.419.camel@camel>
References: <578aa3f70503281144200c2404@mail.gmail.com>
<1112042886.22988.12.camel@state.g2switchworks.com>
<1112217814.28784.419.camel@camel>
On Wed, Mar 30, 2005 at 04:23:34PM -0500, Robert Treat wrote:
>
> Would it be possible to use a BEFORE trigger to reformat the YYYY-DD-MM
> date to YYYY-MM-DD ? The error I see on 7.4 is ERROR: date/time field
> value out of range: "2005-14-01" so ISTM you could do some data
> manipulation if you wanted.
I don't think that would work if the target column has type DATE,
presumably because NEW has the same type as a row of the table, so
NEW.datefield would be a DATE and the YYYY-DD-MM value would raise
an exception before the trigger was ever called.
CREATE TABLE foo (
id serial PRIMARY KEY,
datefield date NOT NULL
);
CREATE FUNCTION datefix() RETURNS trigger AS $$
BEGIN
RAISE INFO 'datefix';
NEW.datefield := current_date; -- for testing
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE datefix();
INSERT INTO foo (datefield) VALUES ('2005-03-30'); -- valid
INFO: datefix
INSERT 0 1
INSERT INTO foo (datefield) VALUES ('2005-30-03'); -- not valid
ERROR: date/time field value out of range: "2005-30-03"
HINT: Perhaps you need a different "datestyle" setting.
Notice that the trigger wasn't called for the second INSERT.
Just brainstorming now, but if you want to keep datefield as a DATE,
then maybe you could create a view with datefield cast to TEXT and
create an appropriate rule so you can insert into the view and have
YYYY-DD-MM converted to YYYY-MM-DD:
DROP TABLE foo;
DROP FUNCTION datefix();
CREATE TABLE foo (
id serial PRIMARY KEY,
datefield date NOT NULL
);
CREATE VIEW fooview AS SELECT id, datefield::text FROM foo;
CREATE RULE datefix AS ON INSERT TO fooview
DO INSTEAD
INSERT INTO foo (datefield) VALUES (to_date(NEW.datefield, 'YYYY-DD-MM'));
INSERT INTO fooview (datefield) VALUES ('2005-30-03');
INSERT 0 1
SELECT * FROM foo;
id | datefield
----+------------
1 | 2005-03-30
(1 row)
I admittedly haven't thought this through very far so it could have
problems, but it might be a starting point. On the other hand, I'm
inclined to agree with Scott Marlowe's advice: fix the data.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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: mike@fuhr.org, xzilla@users.sourceforge.net, smarlowe@g2switchworks.com, thomas.seeber@gmail.com
Subject: Re: Postgres 7.3 migrate to 8.0 date problems.
In-Reply-To: <20050331031154.GA38479@winnie.fuhr.org>
* 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