Re: By Passed Domain Constraints - Mailing list pgsql-interfaces

     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@lodestonetechnologies.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@postgresql.org)
>