Re: Tidying values on variable instantiation

IMHO: It's not necessarily the job of the RDBMS to be responsible for
formatting and cleaning of your data... This is a job better suited for the
application layer and the data model...

The RDBMS should only be responsible for enforcing constraints on the
data... not validating or purifying the data...

Data validation and purification should be performed at the application
layer -- you should format your data appropriately BEFORE trying any
INSERT/UPDATE operations.

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Bath, David
Sent: Thursday, August 25, 2005 8:04 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Tidying values on variable instantiation

Folks,

Preamble:
* I can create a check constraint on a column or domain that enforces
"no leading or trailing whitespace". Imagine that the domain is
called "trimmed_varchar"
* I can create plpgsql function/triggers that "tidy" up incoming varchars,
trimming the offending whitespaces, on a column by column basis.
* When creating a column based on a domain with the check constraint, I
cannot "tidy it up" during a pre-insert/pre-update trigger. Fair enough.
* I'm only asking about this because I am a long-in-the-tooth Oracle guy,
and Pg seems to have many *very* nice features, and there might be
an *elegant* way to achieve this that I cannot attempt in Oracle.

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".

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.

Apologies if I have missed something obvious in the manual, or if it is
a well-known item in the wish-lists, but I am very new to serious Pg
work, and have a tight schedule to do deliver a schema. *sigh*

Thanks in advance
--
David T. Bath
dave(dot)bath(at)unix(dot)net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match