Re: Testing a value against a constraint?

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