Re: [GENERAL] Composite type versus Domain constraints.
On Apr 12, 2005, at 4:48 PM, Tom Lane wrote: James Robinson [EMAIL PROTECTED] 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
Re: [GENERAL] Composite type versus Domain constraints.
James Robinson [EMAIL PROTECTED] writes: 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. Yeah. IIRC that was a deliberate decision on the grounds that most client software would probably break if we sent the domain OID. Maybe we should reconsider, but I think the answer would be the same. 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. What you'd have to do is also create dummy I/O functions as aliases for the internal int2in/out functions. regression=# create function mytypein(cstring) returns mytype regression-# as 'int2in' language internal strict immutable; NOTICE: type mytype is not yet defined DETAIL: Creating a shell type definition. CREATE FUNCTION regression=# create function mytypeout(mytype) returns cstring regression-# as 'int2out' language internal strict immutable; NOTICE: argument type mytype is only a shell CREATE FUNCTION regression=# create type mytype( input = mytypein, output = mytypeout, regression(# internallength = 2, passedbyvalue, alignment = int2 ); CREATE TYPE regression=# select '42'::mytype; mytype 42 (1 row) regression=# Note that it's absolutely critical that you get the size/alignment/byval properties right ;-) With suitable casts to/from int2 (probably implicit to int2, but not implicit from), this would probably work fairly well. regression=# select '42'::mytype + '42'::mytype; ERROR: operator does not exist: mytype + mytype HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. regression=# create cast (mytype as int2) without function as implicit; CREATE CAST regression=# select '42'::mytype + '42'::mytype; ?column? -- 84 (1 row) regression=# create table zit(f1 mytype primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index zit_pkey for table zit CREATE TABLE regression=# \d zit Table public.zit Column | Type | Modifiers ++--- f1 | mytype | not null Indexes: zit_pkey PRIMARY KEY, btree (f1 int2_ops) regression=# Sweet ... I wasn't actually expecting the index to work without more hacking than that ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Composite type versus Domain constraints.
On Apr 13, 2005, at 11:50 AM, Tom Lane wrote: 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. Yeah. IIRC that was a deliberate decision on the grounds that most client software would probably break if we sent the domain OID. Maybe we should reconsider, but I think the answer would be the same. [snipped fantastic PG type trickery -- thanks! We'll run with that style for now] Those wrapper in/out functions and casts to int2 look great and will work for what we absolutely need, but still seem a bit on the wordy side. What about, for databases + client applications which expect it, an option to have a domain expose its oid in result set metadata. Domains created without the extra syntactical flag would operate exactly as they do now -- returning the oid of the wrapped type. But if created like: CREATE DOMAIN mytype AS int2 EXPOSE OID CONSTRAINT test CHECK (VALUE IN (0,1,2,3)); Then when these guys are returned in queries, the domain's oid is eturned as the metadata for the column. Would psql or pg_dump care? Our client apps would be expecting it and would love it. I would suspect that an additional boolean column in pg_type, something along the lines of 'tyobscuresbasetype', defaulting to false, but set to true if 'EXPOSE OID' was provided would be enough for the system to decide which oid to send back. That seems less fragile and error prone than casts, wrapping in/out functions, etc. Reduces the barrier of effort towards making lots and lots of these little guys and harnessing easy extra value on the client side of things. We've essentially got hordes of enumerated types on the client side adding lots of value and functionality to the database-stored smallint, and having only two lines of SQL to build to educate the database about each one would be a real winner. Not to sound ungrateful -- the scalar type tricks can work, but with more SQL-level effort. James Robinson Socialserve.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Composite type versus Domain constraints.
James Robinson [EMAIL PROTECTED] 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 Obviously this whole area needs improvement. Domain constraints in particular fail to be enforced in many places where they should be, such as plpgsql variables. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly