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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
