Re: Tidying values on variable instantiation

On Aug 26, 2005, at 12:04 PM, Bath, David wrote:

> Desired Outcome(s):
> * I would like to have the convenience of declaring a column that
> obeys
>   a constraint (similar to using a domain), but allows a "tidy-up"
> as the
>   value is created BEFORE asserting the constraint.  This *might* be
>   termed a "domain trigger".  (Perhaps even a WORM is possible!).
> * I would like to able to declare columns as
>   "trimmed_varchar(n)".
> * I'd like to be able to use the same approach for other "weak
> domains".


Unfortuantely, I don't know of a way to obtain your desired outcomes,
but perhaps can offer a couple of ideas that you haven't mentioned
(though you may have already thought of them and discarded them as
undesireable. In that case, my apologies :)

Perhaps rather doing this with a trigger and having the table take
care of it, you could use pl functions to handle the inserts, so
instead of using INSERT directly, you could call the
insert_into_table_foo function. The insert_into_table_foo function
would clean up the input and then call INSERT. A disadvantage of this
is that you'll need to write one of these for each table, though
there are some who handle a lot of their inserts, updates, etc, via
pl functions rather than calling the INSERT and UPDATE commands
directly.

Another option would be to have a separate cleaning function (e.g.,
clean_foo() )for each "type" you want, and then call it with
something like INSERT INTO bar (baz, bat, foo) values (232,
'2005-02-20', clean_foo('protofoo')); This has the advantage that you
just need to write one function for each type (rather than each
table), but you'll have to remember to call it.

While I can understand your motivation, I personally think this kind
of operation is best left in the application layer (which includes
such insert functions) rather than the DDL.

Just my ¥2.

Michael Glaesemann
grzm myrealbox com