|
>
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Gauthier, Dave >
Sent: Friday, February 18, 2011 1:24 PM >
To: >
Subject: constraining chars for all cols of a table >
Hi: > I
have to constrain the chars used for table columns. For example... >
create table foo (col1 text, col2 text, col3 text); >
... where >
col1 has to be all uppercase, nothing but [A-Z] >
col2 has to be all lowercase [a-z] plus [0-9] is also allowed >
col3 can be mixed case plus [0-9] and sqr brackets (but nothing else). > I
could put a check constraint on each/every table column, but that seems
complicated and potentially slow. > I
could do this check using the existing insert and update before triggers, but
then I'd have to loop through all >
the columns and decide one by one how to check them. Again, slow and
complicated. > Is
there a better way? Whatever I do, I'd have to be able to capture
violations to provide informative feedback > to
the users through the perl script that'll actually be doing the insert/update. I would normally do this in the perl
script, using regexes, and not allowing the insert if the input data failed to
pass the tests. If this is a CGI program, it is easy to use _javascript_ to
validate before submitting to the CGI script. Of course, you have to ensure that every
program that inserts or updates data checks the constraints. Or put them
in the database, or both. Susan |
