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