From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Tidying values on variable instantiation
Date: Fri, 26 Aug 2005 12:31:23 -0400
Organization: cbbrowne Computing Inc
Lines: 43
Message-ID: <60vf1slkx0.fsf@dba2.int.libertyrms.com>
References: <200508261304.10542.dave.bath@unix.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux)
Cancel-Lock: sha1:1PmMJMTLRgPWa1FFP4jiXSINwzo=
To: pgsql-sql@postgresql.org

dave.bath@unix.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/>