From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: Tidying values on variable instantiation
Date: Fri, 26 Aug 2005 12:31:23 -0400
Message-ID: <60vf1slkx0.fsf@dba2.int.libertyrms.com> (raw)
References: <200508261304.10542.dave.bath@unix.net>
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/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Tidying values on variable instantiation
In-Reply-To: <60vf1slkx0.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox