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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Bath, David
Sent: Thursday, August 25, 2005 8:04 PM
To: pgsql-sql@postgresql.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
thatthe domain is called "trimmed_varchar"
* I can create plpgsql function/triggers that "tidy" up incoming varchars, trimming the offending whitespaces, on a
columnby column basis.
* When creating a column based on a domain with the check constraint, I cannot "tidy it up" during a
pre-insert/pre-updatetrigger. 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
allowsa "tidy-up" as the value is created BEFORE asserting the constraint. This *might* be termed a "domain trigger".
(Perhapseven 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
combinationof 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.bath@unix.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
datatypesdo not match
