Thread: Question about domains.

Hey all,<br /><br />Is there a way to add constraint to the domain that used by a composite type<br />that used by a
table?E.g.:<br /><br />CREATE DOMAIN superid AS integer;<br /><br />CREATE TYPE idtype AS<br />(<br />  id superid<br
/>);<br/><br />CREATE TABLE mytab (id idtype NOT NULL);<br /><br />ALTER DOMAIN superid ADD CONSTRAINT superid_check
CHECK(VALUE > 0);<br /><br />ALTER DOMAIN superid DROP CONSTRAINT superid_check;<br /><br />produces the following
output:<br/><br />dmitigr=> CREATE DOMAIN<br />Time: 23,809 ms<br />dmitigr=> CREATE TYPE<br />Time: 44,875 ms<br
/>dmitigr=>CREATE TABLE<br />Time: 134,101 ms<br />dmitigr=> ERROR:  cannot alter type "superid" because column
"mytab"."id"uses it<br /> dmitigr=> ALTER DOMAIN<br />Time: 0,270 ms<br /><br />As you can see, adding constraint to
thedomain produces an error, while dropping<br />constraint is possible!<br />Any comments?<br /><br />Regards,<br
/>Dmitriy<br/> 

On 08/07/10 2:27 PM, Dmitriy Igrishin wrote:
> Hey all,
>
> Is there a way to add constraint to the domain that used by a composite type
> that used by a table? E.g.:

Currently in PG, adding constraint on Domain, which is already in use is 
not supported.

> CREATE DOMAIN superid AS integer;
>
> CREATE TYPE idtype AS
> (
>    id superid
> );
>
> CREATE TABLE mytab (id idtype NOT NULL);
>
> ALTER DOMAIN superid ADD CONSTRAINT superid_check CHECK (VALUE > 0);
>
> ALTER DOMAIN superid DROP CONSTRAINT superid_check;
>
> produces the following output:
>
> dmitigr=> CREATE DOMAIN
> Time: 23,809 ms
> dmitigr=> CREATE TYPE
> Time: 44,875 ms
> dmitigr=> CREATE TABLE
> Time: 134,101 ms
> dmitigr=> ERROR:  cannot alter type "superid" because column
> "mytab"."id" uses it
> dmitigr=> ALTER DOMAIN
> Time: 0,270 ms
>
> As you can see, adding constraint to the domain produces an error, while
> dropping
> constraint is possible!
> Any comments?

If you want, you can try following:
CREATE DOMAIN superid1 AS integer check(value > 0);
create type idtype1 as (id superid1);

create or replace function idtype2idtype1(idtype) returns idtype1
as
$$ select row($1.id)::idtype1;
$$ language sql;

create cast (idtype as idtype1) with function idtype2int(idtype) as 
implicit;

Then execute the alter table command to convert the data type:
alter table mytab alter column id type idtype1;


-- 
Thanks&  Regards,
Vibhor Kumar.
EnterpriseDB Corporation
The Enterprise Postgres Company