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()
RETURNStrigger 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
youneed 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/
