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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 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 [EMAIL PROTECTED] ---------------------------(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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org