|
>>Restrict access to the
table (for inserts) to a function that does the verification and then executes
the insert in addition to any kind of logging and >>“RAISE”ing you need. Wouldn't that be akin to doing
the checking in the insert and update before triggers? That's certainly
possible, but I fear the performance hit if I have to do a metadata query to
get all the column names, then check them all one by one against a regexp that
each maps to. This should be no problem for single record inserts by users.
But some of these insert triggers cascade the creation of hundreds or thousands
of additional records recursively. If the added overhead is a hald second per
for what becomes a 1000 record creation, that's over 8 minutes ! I was looking at enum, and
something like that would work if I could replace the hardcoded list of values
with something like a regexp. Still looking :-) From: David Johnston
[mailto:polobo@xxxxxxxxx] Restrict access to the table
(for inserts) to a function that does the verification and then executes the
insert in addition to any kind of logging and “RAISE”ing you need. If you need to validate existing
data I’d probably just do some one-time verifications and updates where
required. A column “CHECK” constraint,
however, seems like it should work just find if you use a regular _expression_ –
and I cannot imagine it would be that performance limiting. Without a more specific model in
mind choosing between different approaches is difficult. David J. From:
pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On
Behalf Of Gauthier, Dave 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. Thanks in Advance ! |
