Re: Composite type versus Domain constraints.

On Apr 12, 2005, at 4:48 PM, Tom Lane wrote:

> James Robinson <jlrobins(at)socialserve(dot)com> writes:
>> insert into simple_table values (null, '(43)'); -- GRR works!!! It'll
>> let any smallint in. What happened to the constraint?
>
> The composite-type input routine doesn't check any constraints ...
> and that includes domains. You can make it work if you don't use
> a composite literal:
>
> egression=# insert into simple_table values (null, row(43));
> ERROR: value for domain "simple" violates check constraint "limits"

Thank you for the great info. If I may, here's another question. I am in
the need of new scalar types, essentially domain'd smallints, hence
why my composite type had but one composite member. Domain'd
smallints would be great, but it seems when they get returned in a
result set to the client, they come shipped with the oid of smallint
(21 on my box), not the oid of the domain. I'm experimenting with
a client driver (Python's psycopg) which allows you to register
handlers for arbitrary oids -- but if the result set's metadata
contains the oid for smallint, this does not bode well -- if I register
for the domain's oid, it never gets returned to me in a select, and
if I register for int2's oid hilarity ensues.

Is there an easy path to creating (many) scalar types which piggyback
on int2's functions. Naive experimentation fails:

social=# create type MyType (
INTERNALLENGTH = 2,
INPUT = int2in,
OUTPUT = int2out
);
ERROR: function int2out(mytype) does not exist

Which is reasonable.

In short, I need a domain'd smallint with a different type oid returned
from selects. Is there a short path? The composite type solution works
at a purely SQL level, although something feels not quite right.

----
James Robinson
Socialserve.com