From: Vibhor Kumar <vibhor.kumar@enterprisedb.com>
To: Dmitriy Igrishin <dmitigr@gmail.com>
Cc: postgres list <pgsql-sql@postgresql.org>
Subject: Re: Question about domains.
Date: Thu, 08 Jul 2010 20:41:13 +0100
Message-ID: <4C3629D9.9000900@enterprisedb.com> (raw)
In-Reply-To: <AANLkTimJ41uGheKYOX3xjuz_CdeLUdruizay6BP4jjZw@mail.gmail.com>
References: <AANLkTimJ41uGheKYOX3xjuz_CdeLUdruizay6BP4jjZw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vibhor.kumar@enterprisedb.com, dmitigr@gmail.com
Subject: Re: Question about domains.
In-Reply-To: <4C3629D9.9000900@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox