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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
