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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
