pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Question about domains.
2+ messages / 2 participants
[nested] [flat]

* Question about domains.
@ 2010-07-08 13:27 Dmitriy Igrishin <dmitigr@gmail.com>
  2010-07-08 19:41 ` Re: Question about domains. Vibhor Kumar <vibhor.kumar@enterprisedb.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Dmitriy Igrishin @ 2010-07-08 13:27 UTC (permalink / raw)
  To: pgsql-sql

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

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?

Regards,
Dmitriy

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Question about domains.
  2010-07-08 13:27 Question about domains. Dmitriy Igrishin <dmitigr@gmail.com>
@ 2010-07-08 19:41 ` Vibhor Kumar <vibhor.kumar@enterprisedb.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Vibhor Kumar @ 2010-07-08 19:41 UTC (permalink / raw)
  To: Dmitriy Igrishin <dmitigr@gmail.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2010-07-08 19:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-08 13:27 Question about domains. Dmitriy Igrishin <dmitigr@gmail.com>
2010-07-08 19:41 ` Vibhor Kumar <vibhor.kumar@enterprisedb.com>

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