Re: Basic DOMAIN Support

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(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org]On Behalf Of Rod Taylor
> Sent: Monday, 25 February 2002 7:35 AM
> To: pgsql-patches(at)postgresql(dot)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.
>
>