Re: Basic DOMAIN Support - Mailing list pgsql-patches

Your tests look pretty good - maybe you should make them into a proper
regression test as well?  Also, shouldn't there be some modification to
pg_dump to all DOMAINs to be dumped?

Chris


> -----Original Message-----
> From: pgsql-patches-owner@postgresql.org
> [mailto:pgsql-patches-owner@postgresql.org]On Behalf Of Rod Taylor
> Sent: Monday, 25 February 2002 7:35 AM
> To: pgsql-patches@postgresql.org
> Subject: [PATCHES] Basic DOMAIN Support
>
>
> I intend to add other parts of domain support later on (no reason to
> hold up committing this though) but would appreciate some feedback
> about what I've done.
>
> What's there works, however I intend to finish it off with CHECK
> and -- if I can figure out a good way -- REFERENCES.
>
>
> Implements:
> CREATE DOMAIN domain type [NULL | NOT NULL] [DEFAULT expression];
> COMMENT ON DOMAIN domain IS '';
> DROP DOMAIN domain [RESTRICT | CASCADE]; -- Doesn't actually restrict
> due to pg_depends
>
> Affects:
> Types can be specified as NOT NULL.  No interface is available to set
> this for any type other than a domain however.  Types may also use a
> complex expression (b_expr) for their default.
>
> Various Tasks (output from psql for some simple operations involving
> domains):
>
> NOTE: For DEFAULT NULL to have any effect in table creation the
> default actually needs to be stored.
>
> Since Type defaults have overridden NULL in the past, I left it so
> domains would as well.
>
> Below are some tests I used to check the implementation.
>
> ## DOMAIN TEST ##
> create domain domainvarchar varchar(15);
> create domain domainnumeric numeric(8,2);
> create domain domainint4 int4;
> create domain domaintext text;
>
> -- Test tables using domains
> create table basictest
>            ( testint4 domainint4
>            , realint4 int4
>            , testtext domaintext
>            , realtext text
>            , testvarchar domainvarchar
>            , realvarchar varchar(15)
>            , testnumeric domainnumeric
>            , realnumeric numeric(8,2)
>            );
>
> INSERT INTO basictest values ('88', '88', 'haha', 'haha', 'short
> text', 'short text', '123.12', '123.12');
> select * from basictest;
>
> create domain dnotnull varchar(15) NOT NULL;
> create domain dnull    varchar(15) NULL;
>
> -- NOT NULL in the domain cannot be overridden
> create table nulltest
>            ( col1 dnotnull
>            , col2 dnotnull NULL
>            , col3 dnull    NOT NULL
>            , col4 dnull
>            );
> INSERT INTO nulltest DEFAULT VALUES;
> INSERT INTO nulltest values ('a', 'b', 'c', 'd');  -- Good
> INSERT INTO nulltest values (NULL, 'b', 'c', 'd');
> INSERT INTO nulltest values ('a', NULL, 'c', 'd');
> INSERT INTO nulltest values ('a', 'b', NULL, 'd');
> INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good
> select * from nulltest;
>
>
> create domain ddef1 int4 DEFAULT 3;
> create domain ddef2 numeric(8,6) DEFAULT random();
> -- Type mixing, function returns int8
> create domain ddef3 text DEFAULT random();
> create sequence ddef4_seq;
> create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text));
>
> create table defaulttest
>             ( col1 ddef1
>             , col2 ddef2
>             , col3 ddef3
>             , col4 ddef4
>             , col5 ddef1 DEFAULT NULL
>             , col6 ddef2 DEFAULT '88.1'
>             , col7 ddef4 DEFAULT random() * 8000
>             );
> insert into defaulttest default values;
> insert into defaulttest default values;
> insert into defaulttest default values;
> select * from defaulttest;
>
> ## PSQL OUTPUT ##
>
> newdb=# -- Test Comment / Drop
> newdb=# create domain domaindroptest int4;
> CREATE DOMAIN
> newdb=# comment on domain domaindroptest is 'About to drop this..';
> COMMENT
> newdb=#
> newdb=# select * from pg_type where typname = 'domaindroptest';
>     typname     | typowner | typlen | typprtlen | typbyval | typtype |
> typisdefined | typdelim | typrelid | typelem | typinput | typoutput |
> typrecei
> e | typsend | typalign | typstorage | typnotnull | typmod |
> typdefaultbin | typdefault
> ----------------+----------+--------+-----------+----------+---------+
> --------------+----------+----------+---------+----------+-----------+
> ---------
> --+---------+----------+------------+------------+--------+-----------
> ----+------------
>  domaindroptest |        1 |      4 |        10 | t        | d       |
> t            | ,        |        0 |      23 | int4in   | int4out   |
> int4in
>   | int4out | i        | p          | f          |     -1 |
> |
> (1 row)
>
> newdb=#
> newdb=# drop domain domaindroptest restrict;
> DROP
> newdb=#
> newdb=# select * from pg_type where typname = 'domaindroptest';
>  typname | typowner | typlen | typprtlen | typbyval | typtype |
> typisdefined | typdelim | typrelid | typelem | typinput | typoutput |
> typreceive | ty
> send | typalign | typstorage | typnotnull | typmod | typdefaultbin |
> typdefault
> ---------+----------+--------+-----------+----------+---------+-------
> -------+----------+----------+---------+----------+-----------+-------
> -----+---
> -----+----------+------------+------------+--------+---------------+--
> ----------
> (0 rows)
>
> newdb=# -- TEST Domains.
> newdb=#
> newdb=# create domain domainvarchar varchar(15);
> CREATE DOMAIN
> newdb=# create domain domainnumeric numeric(8,2);
> CREATE DOMAIN
> newdb=# create domain domainint4 int4;
> CREATE DOMAIN
> newdb=# create domain domaintext text;
> CREATE DOMAIN
> newdb=#
> newdb=# -- Test tables using domains
> newdb=# create table basictest
> newdb-#            ( testint4 domainint4
> newdb(#            , realint4 int4
> newdb(#            , testtext domaintext
> newdb(#            , realtext text
> newdb(#            , testvarchar domainvarchar
> newdb(#            , realvarchar varchar(15)
> newdb(#            , testnumeric domainnumeric
> newdb(#            , realnumeric numeric(8,2)
> newdb(#            );
> CREATE
> newdb=#
> newdb=# INSERT INTO basictest values ('88', '88', 'haha', 'haha',
> 'short text', 'short text', '123.12', '123.12');
> INSERT 90400 1
> newdb=# select * from basictest;
>  testint4 | realint4 | testtext | realtext | testvarchar | realvarchar
> | testnumeric | realnumeric
> ----------+----------+----------+----------+-------------+------------
> -+-------------+-------------
>  88       |       88 | haha     | haha     | short text  | short text
> | 123.12      |      123.12
> (1 row)
>
> newdb=#
> newdb=# create domain dnotnull varchar(15) NOT NULL;
> CREATE DOMAIN
> newdb=# create domain dnull    varchar(15) NULL;
> CREATE DOMAIN
> newdb=# -- NOT NULL in the domain cannot be overridden
> newdb=# create table nulltest
> newdb-#            ( col1 dnotnull
> newdb(#            , col2 dnotnull NULL
> newdb(#            , col3 dnull    NOT NULL
> newdb(#            , col4 dnull
> newdb(#            );
> CREATE
> newdb=# INSERT INTO nulltest DEFAULT VALUES;
> ERROR:  ExecAppend: Fail to add null value in not null attribute col1
> newdb=# INSERT INTO nulltest values ('a', 'b', 'c', 'd');  -- Good
> INSERT 90408 1
> newdb-# INSERT INTO nulltest values (NULL, 'b', 'c', 'd');
> ERROR:  ExecAppend: Fail to add null value in not null attribute col1
> newdb=# INSERT INTO nulltest values ('a', NULL, 'c', 'd');
> ERROR:  ExecAppend: Fail to add null value in not null attribute col2
> newdb=# INSERT INTO nulltest values ('a', 'b', NULL, 'd');
> ERROR:  ExecAppend: Fail to add null value in not null attribute col3
> newdb=# INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good
> INSERT 90409 1
> newdb-# select * from nulltest;
>  col1 | col2 | col3 | col4
> ------+------+------+------
>  a    | b    | c    | d
>  a    | b    | c    |
> (2 rows)
>
> newdb=# create domain ddef1 int4 DEFAULT 3;
> CREATE DOMAIN
> newdb=# create domain ddef2 numeric(8,6) DEFAULT random();
> CREATE DOMAIN
> newdb=# -- Type mixing, function returns int8
> newdb=# create domain ddef3 text DEFAULT random();
> CREATE DOMAIN
> newdb=# create sequence ddef4_seq;
> CREATE
> newdb=# create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as
> text));
> CREATE DOMAIN
> newdb=#
> newdb=# create table defaulttest
> newdb-#             ( col1 ddef1
> newdb(#             , col2 ddef2
> newdb(#             , col3 ddef3
> newdb(#             , col4 ddef4
> newdb(#             , col5 ddef1 DEFAULT NULL
> newdb(#             , col6 ddef2 DEFAULT '88.1'
> newdb(#             , col7 ddef4 DEFAULT random() * 8000
> newdb(#             );
> CREATE
> newdb=# insert into defaulttest default values;
> INSERT 90421 1
> newdb=# insert into defaulttest default values;
> INSERT 90422 1
> newdb=# insert into defaulttest default values;
> INSERT 90423 1
> newdb=# select * from defaulttest;
>  col1 |       col2        |       col3        | col4 | col5 | col6 |
> col7
> ------+-------------------+-------------------+------+------+------+--
> ----
>  3    | 0.186453586065422 | 0.391880722433273 | 1    | 3    | 88.1 |
> 1930
>  3    | 0.999444424174467 | 0.461114872461704 | 2    | 3    | 88.1 |
> 6024
>  3    | 0.837450824602251 | 0.632604472633733 | 3    | 3    | 88.1 |
> 7441
> (3 rows)
>
>
> --
> Rod Taylor
>
> Your eyes are weary from staring at the CRT. You feel sleepy. Notice
> how restful it is to watch the cursor blink. Close your eyes. The
> opinions stated above are yours. You cannot imagine why you ever felt
> otherwise.
>
>