Date: Wed, 30 Mar 2005 20:11:54 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>,
	Thomas Seeber <thomas.seeber@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Postgres 7.3 migrate to 8.0 date problems.
Message-ID: <20050331031154.GA38479@winnie.fuhr.org>
References: <578aa3f70503281144200c2404@mail.gmail.com>
	<1112042886.22988.12.camel@state.g2switchworks.com>
	<1112217814.28784.419.camel@camel>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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/