Basic DOMAIN Support - Mailing list pgsql-patches

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.