Re: 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