Обсуждение: 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