Fixing a too long column value in a before insert trigger or rule

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Fixing a too long column value in a before insert trigger or rule
Date: 2005-05-11 10:31:20
Message-ID: 1115807480.22860.55.camel@coppola.muc.ecircle.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

We have a situation where a varchar column was limited to a too small
maximum length in the design phase... shit happens, we will fix our db
schema in our next database downtime or software version deployment,
whichever comes first (we are using 7.4 and on the fly column type
change is only from 8.0 on I guess).
But in the meantime it would be nice to have a trigger or something in
place which could intercept inserts and fix the offending value by
truncating it, so that the insert doesn't fail because of this.

I've tried with a before insert trigger, but the column size seems to be
checked before the trigger ever gets a chance to be called, I suppose
it's a parse time check.

A rule will probably also not work, as I can't replace an insert with
another one to the same table, at least that's how I understand from the
docs, otherwise it causes recursive rule processing...

Is there any way to fix the length of a row to fit in the table in any
way, be it a rule or trigger ? Or the length check will kick in even for
a rule before it gets a chance to fix it ?

TIA,
Csaba.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Dean 2005-05-11 11:02:08 Rekall for Free
Previous Message Douglas McNaught 2005-05-11 10:25:34 Re: SECURITY RELEASES: 7.2.8 - 7.3.10 - 7.4.8 - 8.0.3