Re: Tidying values on variable instantiation

dave(dot)bath(at)unix(dot)net ("Bath, David") writes:
> Question(s):
> * Am I being realistic, or should I grit my teeth and clone code from
> trigger to trigger and column to column?
> * Is this something I should try and do using domains, types and
> cast functions from "text" or some horrible combination of them all?
> * Has anybody got a code sample that might do something similar.

If what you are looking for is an API that "cleans things up," then
I'd suggest the thought of building a stored function API, and using
*that* to do the work instead of hitting tables directly.

In the 'domain registry' area, I have done this very sort of thing,
mostly oriented towards data conversions.

We have, as major objects, domains, contacts, and nameservers.

I have some functions, create_domain(), create_contact(), and
create_nameserver(), where the stored procs receive a very limited set
of parameters (in comparison, at least, with the total number of
attributes associated with the respective sets of tables).

Relevant to the thread, create_contact() does a lot of "data
cleansing" in order to try to perform the Procrustean task of forcing
telephone numbers into a particular "standard form."

That approach has proved very useful.

If you have some well-defined set of actions that you want to perform
on the objects in your system, defining a stored function for each
action gives a good way of centralizing the "cleanup" parts.

We have separate status tables; I could have defined triggers to try
to manage them; it seemed more sensible to instead handle that in the
stored procs.
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/oses.html
Rules of the Evil Overlord #69. "All midwives will be banned from the
realm. All babies will be delivered at state-approved
hospitals. Orphans will be placed in foster-homes, not abandoned in
the woods to be raised by creatures of the wild."
<http://www.eviloverlord.com/>