On Apr 12, 2005, at 4:48 PM, Tom Lane wrote:
James Robinson <jlrobins@xxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
