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