v8-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
text/x-patch
Filename: v8-0002-CREATE-SCHEMA-CREATE-DOMAIN.patch
Type: text/x-patch
Part: 4
From fc8c3f52c87d0fe057487a185d3735efd42eb456 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 30 Dec 2025 11:48:49 +0800
Subject: [PATCH v8 2/5] CREATE SCHEMA CREATE DOMAIN
The SQL standard allows domains to be specified in a CREATE SCHEMA statement.
This adds support for that capability.
For example:
CREATE SCHEMA schema_name AUTHORIZATION CURRENT_ROLE
create domain ss as text not null;
The domain will be created within the to be created schema. The domain name can
be schema-qualified or database-qualified, however it's not allowed to let
domain create within a different schema.
Author: Kirill Reshke <reshkekirill@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com
---
doc/src/sgml/ref/create_schema.sgml | 4 +-
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 26 +++++++++
src/bin/psql/tab-complete.in.c | 12 ++--
.../expected/create_schema.out | 4 +-
.../test_ddl_deparse/sql/create_schema.sql | 3 +-
src/test/regress/expected/create_schema.out | 56 +++++++++++++++++++
src/test/regress/sql/create_schema.sql | 36 ++++++++++++
8 files changed, 132 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 625793a6b67..79186d2b936 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -100,8 +100,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp
<listitem>
<para>
An SQL statement defining an object to be created within the
- schema. Currently, only <command>CREATE
- TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE
+ schema. Currently, only <command>CREATE DOMAIN</command>
+ <command>CREATE TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE
INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE
TRIGGER</command> and <command>GRANT</command> are accepted as clauses
within <command>CREATE SCHEMA</command>. Other kinds of objects may
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 28f4e11e30f..54e09d0ceb5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1637,6 +1637,7 @@ schema_stmt:
| CreateTrigStmt
| GrantStmt
| ViewStmt
+ | CreateDomainStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 5d168fd0285..849a19c4e47 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4458,6 +4458,32 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
elements = lappend(elements, element);
break;
+ case T_CreateDomainStmt:
+ {
+ char *domain_schema = NULL;
+
+ CreateDomainStmt *elp = castNode(CreateDomainStmt, element);
+
+ /*
+ * The schema of the DOMAIN must match the schema being
+ * created. If the domain name length exceeds 3, it will
+ * fail in DeconstructQualifiedName.
+ */
+ if (list_length(elp->domainname) == 2)
+ domain_schema = strVal(list_nth(elp->domainname, 0));
+ else if (list_length(elp->domainname) == 3)
+ domain_schema = strVal(list_nth(elp->domainname, 1));
+
+ if (domain_schema != NULL && strcmp(domain_schema, schemaName) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE DOMAIN specifies a schema (%s) different from the one being created (%s)",
+ domain_schema, schemaName));
+
+ elements = lappend(elements, element);
+ }
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 75a101c6ab5..e750b2dbb87 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2201,7 +2201,7 @@ match_previous_words(int pattern_id,
{
/* only some object types can be created as part of CREATE SCHEMA */
if (HeadMatches("CREATE", "SCHEMA"))
- COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER",
+ COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER", "DOMAIN",
/* for INDEX and TABLE/SEQUENCE, respectively */
"UNIQUE", "UNLOGGED");
else
@@ -3471,15 +3471,15 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "DATABASE", MatchAny, "STRATEGY"))
COMPLETE_WITH("WAL_LOG", "FILE_COPY");
- /* CREATE DOMAIN */
- else if (Matches("CREATE", "DOMAIN", MatchAny))
+ /* CREATE DOMAIN --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny))
COMPLETE_WITH("AS");
- else if (Matches("CREATE", "DOMAIN", MatchAny, "AS"))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
- else if (Matches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
COMPLETE_WITH("COLLATE", "DEFAULT", "CONSTRAINT",
"NOT NULL", "NULL", "CHECK (");
- else if (Matches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations);
/* CREATE EXTENSION */
diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out
index 8ab4eb03385..d73c4702051 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -13,7 +13,9 @@ CREATE SCHEMA IF NOT EXISTS baz;
NOTICE: schema "baz" already exists, skipping
CREATE SCHEMA element_test
CREATE TABLE foo (id int)
- CREATE VIEW bar AS SELECT * FROM foo;
+ CREATE VIEW bar AS SELECT * FROM foo
+ CREATE DOMAIN d1 AS INT;
NOTICE: DDL test: type simple, tag CREATE SCHEMA
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE VIEW
+NOTICE: DDL test: type simple, tag CREATE DOMAIN
diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
index f314dc2b840..57ada462070 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -14,4 +14,5 @@ CREATE SCHEMA IF NOT EXISTS baz;
CREATE SCHEMA element_test
CREATE TABLE foo (id int)
- CREATE VIEW bar AS SELECT * FROM foo;
+ CREATE VIEW bar AS SELECT * FROM foo
+ CREATE DOMAIN d1 AS INT;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 38530c282a9..0533c29a311 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -131,5 +131,61 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
NOTICE: drop cascades to table regress_schema_1.tab
RESET ROLE;
+-- Cases where the schema creation with domain.
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN public.ss AS TEXT NOT NULL DEFAULT 'hello' CONSTRAINT nn CHECK (VALUE <> 'hello')
+ CREATE TABLE t(a ss);
+ERROR: CREATE DOMAIN specifies a schema (public) different from the one being created (regress_schema_2)
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN postgres.public.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello'
+ CREATE TABLE t(a ss);
+ERROR: CREATE DOMAIN specifies a schema (public) different from the one being created (regress_schema_2)
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_2 CREATE DOMAIN ss.postgres.regress_schema_2.ss AS TEXT;
+ERROR: improper qualified name (too many dotted names): ss.postgres.regress_schema_2.ss
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN ss1 AS ss
+ CREATE DOMAIN ss AS TEXT;
+ERROR: type "ss" does not exist
+LINE 2: CREATE DOMAIN ss1 AS ss
+ ^
+--ok, qualified schema name for domain should be same as the created schema.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN regress_schema_2.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' COLLATE "C"
+ CREATE TABLE t(a regress_schema_2.ss);
+\dD regress_schema_2.*
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default | Check
+------------------+------+------+-----------+----------+---------------+--------------------------------
+ regress_schema_2 | ss | text | C | not null | 'hello'::text | CHECK (VALUE <> 'hello'::text)
+(1 row)
+
+--ok, no qualified schema name for domain.
+CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN ss AS TEXT CONSTRAINT nn CHECK (VALUE <> 'hello') NOT NULL DEFAULT 'hello'
+ CREATE DOMAIN ss1 AS ss
+ CREATE VIEW test AS SELECT 'hello'::ss AS test
+ CREATE TABLE t(a ss1);
+\dD regress_schema_3.*
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default | Check
+------------------+------+---------------------+-----------+----------+---------------+--------------------------------
+ regress_schema_3 | ss | text | | not null | 'hello'::text | CHECK (VALUE <> 'hello'::text)
+ regress_schema_3 | ss1 | regress_schema_3.ss | | | 'hello'::text |
+(2 rows)
+
+DROP SCHEMA regress_schema_2 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to type regress_schema_2.ss
+drop cascades to table regress_schema_2.t
+DROP SCHEMA regress_schema_3 CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to type regress_schema_3.ss
+drop cascades to type regress_schema_3.ss1
+drop cascades to view regress_schema_3.test
+drop cascades to table regress_schema_3.t
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index b3dc1cfd758..54a07054767 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -71,5 +71,41 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
RESET ROLE;
+-- Cases where the schema creation with domain.
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN public.ss AS TEXT NOT NULL DEFAULT 'hello' CONSTRAINT nn CHECK (VALUE <> 'hello')
+ CREATE TABLE t(a ss);
+
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN postgres.public.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello'
+ CREATE TABLE t(a ss);
+
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_2 CREATE DOMAIN ss.postgres.regress_schema_2.ss AS TEXT;
+
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN ss1 AS ss
+ CREATE DOMAIN ss AS TEXT;
+
+--ok, qualified schema name for domain should be same as the created schema.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN regress_schema_2.ss AS TEXT NOT NULL CONSTRAINT nn CHECK (VALUE <> 'hello') DEFAULT 'hello' COLLATE "C"
+ CREATE TABLE t(a regress_schema_2.ss);
+\dD regress_schema_2.*
+
+--ok, no qualified schema name for domain.
+CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
+ CREATE DOMAIN ss AS TEXT CONSTRAINT nn CHECK (VALUE <> 'hello') NOT NULL DEFAULT 'hello'
+ CREATE DOMAIN ss1 AS ss
+ CREATE VIEW test AS SELECT 'hello'::ss AS test
+ CREATE TABLE t(a ss1);
+\dD regress_schema_3.*
+
+DROP SCHEMA regress_schema_2 CASCADE;
+DROP SCHEMA regress_schema_3 CASCADE;
+
-- Clean up
DROP ROLE regress_create_schema_role;
--
2.34.1