Re: By Passed Domain Constraints

I'm sorry Tom, but I am not certain that I understand how this
known problem applies to my problem.

select * from employee_passcode_update('system',100000, 'DOGGY');
ERROR: value for domain d_passcode violates check constraint
"d_passcode_check"

I call the same function from pqlib using PQexecParams and the
next thing I know.

select * from employee_passcode where id = 100000;
id | passcode | mod_user | timestamp
--------+----------+----------+-------------------------------
100000 | DOGGY | system | 2005-07-06 11:37:09.926907-04

Where

\df employee_passcode_update
List of functions
Schema | Name | Result data type |
Argument data types
--------+--------------------------+------------------
+----------------------------------------
public | employee_passcode_update | d_employee_id | d_user_name,
d_employee_id, d_passcode

and

\d employee_passcode
Table "public.employee_passcode"
Column | Type | Modifiers
-----------+---------------+-----------
id | d_employee_id | not null
passcode | d_passcode |
mod_user | d_user_name |
timestamp | d_timestamp |
Indexes:
"employee_passcode_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"employee_passcode_mod_user_fkey" FOREIGN KEY (mod_user)
REFERENCES employee(user_name)

The same plpgsql function is being called each time.
Additionally, I believe that the same checks work from php.

However, if I cast the D_PASSCODE to a varchar and back in the
function implementation I do get the expected exception when called
from PQexecParams. Which is good. It means I have a work around,
though I really do not want to updated hundreds of functions to do
this. (I suspect that this will be my short term solution) Just
casting to a D_PASSCODE does nothing. I am guessing this is because
p_passcode, my function parameter in question, is already a D_PASSCODE.

I have also been bitten by the problem you are describing. But,
that one is a problem even when called from psql if I am not
mistaken. Does psql not use pqlib? Perhaps it is something about
PQexecParams that is the problem. I will test in a little while.

Thanks for you help
Robert Perry

On Jul 6, 2005, at 10:35 AM, Tom Lane wrote:

> Robert Perry <rlperry(at)lodestonetechnologies(dot)com> writes:
>
>> It looks like the data is not checked when passed to the
>> function and from their on out since it is already the correct type
>> it is not checked again. Has anyone else seen something like this?
>>
>
> IIRC, plpgsql does not know anything about domains and does not
> enforce
> domain constraints when assigning to local variables or the function
> result. The same is true of the other PLs, though I think it could
> only
> matter for the function result in those cases (internal variables
> aren't
> of SQL types anyway for them). You could probably work around this by
> writing explicit casts to the domain inside the function, eg
> "RETURN x::domain" not just "RETURN x".
>
> I thought the unfinished work for domains was mentioned on the TODO
> list
> but I don't see anything about it right now.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>