Re: Postgres 7.3 migrate to 8.0 date problems.

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/