Add DOMAIN support. Includes manual pages and regression tests, from

Rod Taylor.

6 files changed, 461 insertions, 11 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index d785121a047..451c9dcaa8a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.33 2002/03/07 16:35:32 momjian Exp $
+ $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.34 2002/03/19 02:18:10 momjian Exp $
  -->
 
 <chapter id="catalogs">
@@ -2511,6 +2511,53 @@
      </row>
 
      <row>
+      <entry>typbasetype</entry>
+      <entry><type>oid</type></entry>
+      <entry></entry>
+      <entry><para>
+       <structfield>typbasetype</structfield> is the type that this one is based
+       on.  Normally references the domains parent type, and is 0 otherwise.
+      </para></entry>
+     </row>
+
+	 <row>
+	  <entry>typnotnull</entry>
+	  <entry><type>boolean</type></entry>
+	  <entry></entry>
+	  <entry><para>
+	   <structfield>typnotnull</structfield> represents a NOT NULL
+	   constraint on a type.  Used for domains only.
+	  </para></entry>
+	 </row>
+
+     <row>
+      <entry>typmod</entry>
+      <entry><type>integer</type></entry>
+      <entry></entry>
+      <entry><para>
+       <structfield>typmod</structfield> records type-specific data
+       supplied at table creation time (for example, the maximum
+       length of a <type>varchar</type> column).  It is passed to
+       type-specific input and output functions as the third
+       argument. The value will generally be -1 for types that do not
+       need typmod.  This data is copied to
+       <structfield>pg_attribute.atttypmod</structfield> on creation
+       of a table using a domain as it's field type.
+       </para></entry>
+     </row>
+
+     <row>
+      <entry>typdefaultbin</entry>
+      <entry><type>text</type></entry>
+      <entry></entry>
+      <entry><para>
+       <structfield>typdefaultbin</structfield> is NULL for types without a
+       default value.  If it's not NULL, it contains the internal string
+       representation of the default expression node.
+      </para></entry>
+     </row>
+
+     <row>
       <entry>typdefault</entry>
       <entry><type>text</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 3ea658fd042..460f150bc89 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.35 2002/03/07 16:35:32 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.36 2002/03/19 02:18:12 momjian Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -52,6 +52,7 @@ Complete list of usable sgml source files in this directory.
 <!entity createAggregate    system "create_aggregate.sgml">
 <!entity createConstraint   system "create_constraint.sgml">
 <!entity createDatabase     system "create_database.sgml">
+<!entity createDomain       system "create_domain.sgml">
 <!entity createFunction     system "create_function.sgml">
 <!entity createGroup        system "create_group.sgml">
 <!entity createIndex        system "create_index.sgml">
@@ -69,6 +70,7 @@ Complete list of usable sgml source files in this directory.
 <!entity delete             system "delete.sgml">
 <!entity dropAggregate      system "drop_aggregate.sgml">
 <!entity dropDatabase       system "drop_database.sgml">
+<!entity dropDomain         system "drop_domain.sgml">
 <!entity dropFunction       system "drop_function.sgml">
 <!entity dropGroup          system "drop_group.sgml">
 <!entity dropIndex          system "drop_index.sgml">
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 026533de954..f3530b71ce6 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/comment.sgml,v 1.14 2002/03/07 16:35:33 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/comment.sgml,v 1.15 2002/03/19 02:18:12 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -25,7 +25,7 @@ PostgreSQL documentation
   <synopsis>
 COMMENT ON
 [
-  [ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] <replaceable class="PARAMETER">object_name</replaceable> |
+  [ DATABASE | DOMAIN | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] <replaceable class="PARAMETER">object_name</replaceable> |
   COLUMN <replaceable class="PARAMETER">table_name</replaceable>.<replaceable class="PARAMETER">column_name</replaceable> |
   AGGREGATE <replaceable class="PARAMETER">agg_name</replaceable> (<replaceable class="PARAMETER">agg_type</replaceable>) |
   FUNCTION <replaceable class="PARAMETER">func_name</replaceable> (<replaceable class="PARAMETER">arg1</replaceable>, <replaceable class="PARAMETER">arg2</replaceable>, ...) |
@@ -33,7 +33,7 @@ COMMENT ON
   TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable>
 ] IS <replaceable class="PARAMETER">'text'</replaceable>
   </synopsis>
-  
+
   <refsect2 id="R2-SQL-COMMENT-1">
    <refsect2info>
     <date>1999-10-25</date>
@@ -64,7 +64,7 @@ COMMENT ON
     </variablelist>
    </para>
   </refsect2>
-  
+
   <refsect2 id="R2-SQL-COMMENT-2">
    <refsect2info>
     <date>1998-09-08</date>
@@ -99,7 +99,7 @@ COMMENT
   </title>
   <para>
    <command>COMMENT</command> stores a comment about a database object.
-    Comments can be 
+    Comments can be
     easily retrieved with <command>psql</command>'s
     <command>\dd</command>, <command>\d+</command>, or <command>\l+</command>
     commands.  Other user interfaces to retrieve comments can be built atop
@@ -141,6 +141,7 @@ COMMENT ON mytable IS 'This is my table.';
 
    <programlisting>
 COMMENT ON DATABASE my_database IS 'Development Database';
+COMMENT ON DOMAIN my_domain IS 'Email Address Domain';
 COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee id';
 COMMENT ON RULE my_rule IS 'Logs UPDATES of employee records';
 COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys';
@@ -155,12 +156,12 @@ COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for R.I.';
    </programlisting>
   </para>
  </refsect1>
- 
+
  <refsect1 id="R1-SQL-COMMENT-3">
   <title>
    Compatibility
   </title>
-  
+
   <refsect2 id="R2-SQL-COMMENT-4">
    <refsect2info>
     <date>1998-09-08</date>
diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml
new file mode 100644
index 00000000000..5f79dc63b53
--- /dev/null
+++ b/doc/src/sgml/ref/create_domain.sgml
@@ -0,0 +1,231 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v 1.3 2002/03/19 02:18:13 momjian Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-CREATEDOMAIN">
+ <refmeta>
+  <refentrytitle id="sql-createdomian-title">
+   CREATE DOMAIN
+  </refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   CREATE DOMAIN
+  </refname>
+  <refpurpose>
+   define a new domain
+  </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>2002-02-24</date>
+  </refsynopsisdivinfo>
+  <synopsis>
+CREATE DOMAIN <replaceable class="parameter">domainname</replaceable> <replaceable class="parameter">data_type</replaceable>  [ DEFAULT <replaceable>default_expr</> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [, ... ] ]
+[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
+{ NOT NULL | NULL <!-- |  UNIQUE | PRIMARY KEY |
+  CHECK (<replaceable class="PARAMETER">expression</replaceable>) |
+  REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL ]
+    [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] --> }
+<!-- [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] -->
+  </synopsis>
+
+  <refsect2 id="R2-SQL-CREATEDOMAIN-1">
+   <refsect2info>
+    <date>2002-02-24</date>
+   </refsect2info>
+   <title>
+    Parameters
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">domainname</replaceable></term>
+      <listitem>
+       <para>
+	The name of a domain to be created.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="PARAMETER">data_type</replaceable></term>
+      <listitem>
+       <para>
+        The data type of the domain. This may include array specifiers.
+        Refer to the <citetitle>User's Guide</citetitle> for further
+        information about data types and arrays.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>DEFAULT
+      <replaceable>default_expr</replaceable></literal></term>
+      <listitem>
+       <para>
+        The <literal>DEFAULT</> clause assigns a default data value for
+        the column whose column definition it appears within.  The value
+        is any variable-free expression (subselects and cross-references
+        to other columns in the current table are not allowed).  The
+        data type of the default expression must match the data type of the
+        domain.
+       </para>
+
+       <para>
+        The default expression will be used in any insert operation that
+        does not specify a value for the domain.  If there is no default
+        for a domain, then the default is NULL.
+       </para>
+
+       <note>
+        <para>
+         The default of a column will be tested before that of the domain.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
+      <listitem>
+       <para>
+        An optional name for a domain.  If not specified,
+        the system generates a name.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>NOT NULL</></term>
+      <listitem>
+       <para>
+        The column is not allowed to contain NULL values.  This is
+        equivalent to the column constraint <literal>CHECK (<replaceable
+        class="PARAMETER">column</replaceable> NOT NULL)</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>NULL</></term>
+      <listitem>
+       <para>
+        The column is allowed to contain NULL values. This is the default.
+       </para>
+
+       <para>
+        This clause is only available for compatibility with
+        non-standard SQL databases.  Its use is discouraged in new
+        applications.
+       </para>
+      </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-SQL-CREATEDOMAIN-2">
+   <refsect2info>
+    <date>2002-02-24</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+CREATE DOMAIN
+       </computeroutput></term>
+      <listitem>
+       <para>
+	Message returned if the domain is successfully created.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-SQL-CREATEDOMAIN-1">
+  <refsect1info>
+   <date>2002-02-24</date>
+  </refsect1info>
+  <title>
+   Description
+  </title>
+
+  <para>
+   <command>CREATE DOMAIN</command>  allows  the user to register a new user data
+   domain with PostgreSQL for use in the current data base.   The
+   user  who  defines  a domain becomes its owner.
+   <replaceable class="parameter">domainname</replaceable> is
+   the name of the new type and must  be  unique  within  the
+   types and domains defined for this database.
+  </para>
+
+  <para>
+   Domains are useful for abstracting common fields between tables into
+   a single location for maintenance.  An email address column may be used
+   in several tables, all with the same properties.  Define a domain and
+   use that rather than setting up each tables constraints individually.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+  <para>
+   This example creates the <type>country_code</type> data type and then uses the
+   type in a table definition:
+<programlisting>
+CREATE DOMAIN country_code char(2) NOT NULL;
+CREATE TABLE countrylist (id INT4, country country_code);
+</programlisting>
+  </para>
+ </refsect1>
+
+ <refsect1 id="SQL-CREATEDOMAIN-compatibility">
+  <title>Compatibility</title>
+
+  <para>
+   This <command>CREATE DOMAIN</command> command is a
+   <productname>PostgreSQL</productname> extension.  CHECK and FOREIGN KEY
+   constraints are currently unsupported.
+  </para>
+ </refsect1>
+
+ <refsect1 id="SQL-CREATEDOMAIN-see-also">
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-dropdomain"></member>
+   <member><citetitle>PostgreSQL Programmer's Guide</citetitle></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>
+
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/drop_domain.sgml b/doc/src/sgml/ref/drop_domain.sgml
new file mode 100644
index 00000000000..e9bc38ad28c
--- /dev/null
+++ b/doc/src/sgml/ref/drop_domain.sgml
@@ -0,0 +1,167 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/drop_domain.sgml,v 1.3 2002/03/19 02:18:13 momjian Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-DROPDOMAIN">
+ <refmeta>
+  <refentrytitle id="SQL-DROPDOMAIN-TITLE">
+   DROP DOMAIN
+  </refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   DROP DOMAIN
+  </refname>
+  <refpurpose>
+   remove a user-defined domain
+  </refpurpose>
+ </refnamediv>
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>1999-07-20</date>
+  </refsynopsisdivinfo>
+  <synopsis>
+DROP DOMAIN <replaceable class="PARAMETER">domainname</replaceable> [, ...]
+  </synopsis>
+
+  <refsect2 id="R2-SQL-DROPDOMAIN-1">
+   <refsect2info>
+    <date>2002-02-24</date>
+   </refsect2info>
+   <title>
+    Inputs
+   </title>
+   <para>
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="PARAMETER">domainname</replaceable></term>
+      <listitem>
+       <para>
+	The name of an existing domain.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-SQL-DROPDOMAIN-2">
+   <refsect2info>
+    <date>2002-02-24</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+DROP
+       </computeroutput></term>
+      <listitem>
+       <para>
+	The message returned if the command is successful.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><computeroutput>
+ERROR:  RemoveDomain: type '<replaceable class="parameter">domainname</replaceable>' does not exist
+       </computeroutput></term>
+      <listitem>
+       <para>
+	This message occurs if the specified domain (or type) is not found.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-SQL-DROPDOMAIN-1">
+  <refsect1info>
+   <date>2002-02-24</date>
+  </refsect1info>
+  <title>
+   Description
+  </title>
+  <para>
+   <command>DROP DOMAIN</command> will remove a user domain from the
+   system catalogs.
+  </para>
+  <para>
+   Only the owner of a domain can remove it.
+  </para>
+ </refsect1>
+
+ <refsect1 id="SQL-DROPDOMAIN-notes">
+  <title>Notes</title>
+
+  <itemizedlist>
+   <listitem>
+    <para>
+     It is the user's responsibility to remove any operators,
+     functions, aggregates, access methods, and tables that
+     use a deleted domain.
+    </para>
+   </listitem>
+  </itemizedlist>
+ </refsect1>
+
+ <refsect1 id="SQL-DROPDOMAIN-examples">
+  <title>Examples</title>
+  <para>
+   To remove the <type>box</type> domain:
+
+<programlisting>
+DROP DOMAIN box RESTRICT;
+</programlisting>
+  </para>
+ </refsect1>
+
+ <refsect1 id="SQL-DROPDOMAIN-compatibility">
+  <title>Compatibility</title>
+
+  <para>
+   A <command>DROP DOMAIN</command> statement exists in SQL99.  As with
+   most other <quote>drop</quote> commands, <command>DROP
+   DOMAIN</command> in SQL99 requires a <quote>drop behavior</quote>
+   clause to select between dropping all dependent objects or refusing
+   to drop if dependent objects exist:
+<synopsis>
+DROP DOMAIN <replaceable>name</replaceable> { CASCADE | RESTRICT }
+</synopsis>
+   <productname>PostgreSQL</productname> enforces the existance of
+   RESTRICT or CASCADE but ignores their enforcement against the
+   system tables.
+  </para>
+ </refsect1>
+
+ <refsect1 id="SQL-DROPDOMAIN-see-also">
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-createdomain"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 0ca0ea5a9c4..b6b63254462 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.23 2002/03/07 16:35:32 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.24 2002/03/19 02:18:11 momjian Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -61,6 +61,7 @@ PostgreSQL Reference Manual
    &createAggregate;
    &createConstraint;
    &createDatabase;
+   &createDomain;
    &createFunction;
    &createGroup;
    &createIndex;
@@ -78,6 +79,7 @@ PostgreSQL Reference Manual
    &delete;
    &dropAggregate;
    &dropDatabase;
+   &dropDomain;
    &dropFunction;
    &dropGroup;
    &dropIndex;
@@ -115,7 +117,7 @@ PostgreSQL Reference Manual
    &unlisten;
    &update;
    &vacuum;
-  
+
  </reference>
 
 <!--