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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
