Обсуждение: Testing a value against a constraint?
I'm writing an intranet app in PHP, and having issues around constraints. Specifically, the error messages coming back from PG aren't very user friendly. I'm looking for a way to make this a bit more smooth to the end user, and ensure that my conditional checks really do match the requirements set forth in the database. For example, given a check constraint called "check_productcode" is it possible to test a value against that constraint alone, without attempting to insert anything? Something like "select check constraint check_productcode('testvalue')" where "testvalue" is the string you wish to check against constraint called "check_productcode". It might return an error message or not. Can this be done? How? I can't find this in the docs anywhere... Right now, I have lots of code like this: <? Function CheckInvoice($invoice) { $productcodes=array('E1', 'E2', 'E3', 'F11'); if (!in_array($productcodes($invoice['productcode'], $productcodes)) return "Error: Product code is not valid!"; ... } This is a tedious, duplicative, and error-prone way just to get some plain-english error messages, since anytime a constraint needs to be changed, it requires a code audit. Ugh. Is there a better way? -Ben -- "I kept looking around for somebody to solve the problem. Then I realized I am somebody" -Anonymous
On Jul 12, 2004, at 9:53 AM, Benjamin Smith wrote: > I'm writing an intranet app in PHP, and having issues around > constraints. > Specifically, the error messages coming back from PG aren't very user > friendly. I'm looking for a way to make this a bit more smooth to the > end > user, and ensure that my conditional checks really do match the > requirements > set forth in the database. > > For example, given a check constraint called "check_productcode" is it > possible to test a value against that constraint alone, without > attempting to > insert anything? I did something like this in one iteration of an app I was running. I wanted user names to be at least 6 characters long, so I made this function: create or replace function domain_username_constraint_check (text) returns boolean as ' select case when (length($1) >= 6) then true else false end ; ' language 'sql'; Then, I defined my username domain like this: create domain username as text check (domain_username_constraint_check(value)); The PHP code could check the validity of the username without trying to insert by calling a simple SQL select : $user = pg_escape_string($user); $sql = "select domain_username_constraint_check($user);"; And checking whether the result is true or false. Of course you could make the constraint check more complex. In your case, I suspect you'd want to have valid product codes stored in the db. You could have an SQL function along the lines of create function is_valid_product_code( text -- product code to be tested ) returns boolean language sql as ' select count(*) = 1 from product_codes where product_code = $1; '; This assumes you have a table product_codes that has a unique product codes (such as a primary key). If product codes aren't unique in the table (though I'd think a good db design would have such a table somewhere), you can change count(*) = 1 to count(*) > 0. Again, a simple select is_valid_product_code($product_code) should return true or false which can be called in your PHP code. Does this help? Michael Glaesemann grzm myrealbox com