From: "Bath, David" <dave.bath@unix.net>
Reply-To: dave.bath@unix.net
To: pgsql-sql@postgresql.org
Subject: Tidying values on variable instantiation
Date: Fri, 26 Aug 2005 13:04:10 +1000
User-Agent: KMail/1.8
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200508261304.10542.dave.bath@unix.net>

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