Skip site navigation (1) Skip section navigation (2)

Re: numericOnly trigger

From: Derrick Betts <list(at)blueaxis(dot)com>
To: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: numericOnly trigger
Date: 2007-09-22 14:28:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Oliver Elphick wrote:
> On Fri, 2007-09-21 at 17:39 -0600, Derrick Betts wrote:
>> Sean Davis wrote:
>>> Derrick Betts wrote:
>>>> I was wondering if anyone has built a trigger or etc. that verifies the
>>>> validity of an entry that is being posted to the database. I would like
>>>> to do the following:
>>>>    1.    Iterate through the column types in the table being updated
>>>>    2.    If the column type is numeric or real, or etc.,
>>>>        a. remove all the strings from the New.value
>>>>        b. set the New.value to the newly stripped value
>>>>     3.    Update the table with the modified values.
>>>> The part I was hoping not to have to reproduce, if anyone has it and is
>>>> willing to share it, is the logic for iterating through the column types
>>>> and cleaning the specific values for update.
>>> I might be wrong, but I do not think your trigger will not actually fire
>>> if you try to do an update with text data in a column with a numeric
>>> datatype.  The type checking happens BEFORE a trigger fires, so you will
>>> simply get an error.
>>> Sean
>> If the trigger is a BEFORE UPDATE trigger will that not work?
> Well, the quick way to answer such a question is to try it!
> As soon as I thought about trying it, I realised that my trigger would
> be testing NEW.numeric_column, so it couldn't possibly work, because
> non-numeric data couldn't get into it in the first place.
> But I think your plan is fundamentally misconceived, because if the data
> is wrong, which is automatically the case if a numeric field contains
> non-numeric characters, you don't know what the right data is.  If
> someone enters "4w2", you intend to enter "42" - how do you know he
> didn't mean "432"?
That's a good question. It looks I may be relegated to solving the 
problem in the user application. What I wanted was to change numbers 
like $235,000.45 to 235000.45 for storage in the DB.
Thanks everyone for your insights.

In response to

pgsql-novice by date

Next:From: Raimon FernandezDate: 2007-09-24 07:42:40
Subject: Cursors performance
Previous:From: Derrick BettsDate: 2007-09-21 23:39:30
Subject: Re: numericOnly trigger

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group