v7-0003-CREATE-SCHEMA-CREATE-COLLATION.patch

From cf95a41428b022e6361f9a4a0e00a1a9474a34b6 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 26 Aug 2025 12:43:12 +0800
Subject: [PATCH v7 3/4] CREATE SCHEMA CREATE COLLATION

SQL standard allow collation to be specified with CREATE SCHEMA
statement. This patch adds support in PostgreSQL for that.

For example:
    CREATE SCHEMA schema_name CREATE COLLATION coll_icu_und FROM "und-x-icu";

The collation will be created within the to be created schema.
The collation name can be schema-qualified or database-qualified,
however it's not allowed to let collation create within a different schema.

Note: src/bin/psql/tab-complete.in.c changes seems incorrect.

Discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com
---
 doc/src/sgml/ref/create_schema.sgml           |  5 ++-
 src/backend/catalog/objectaddress.c           | 16 +++++++
 src/backend/parser/gram.y                     |  1 +
 src/backend/parser/parse_utilcmd.c            | 32 ++++++++++++++
 src/bin/psql/tab-complete.in.c                |  8 ++--
 src/include/catalog/objectaddress.h           |  1 +
 .../expected/create_schema.out                |  4 +-
 .../test_ddl_deparse/sql/create_schema.sql    |  3 +-
 .../regress/expected/collate.icu.utf8.out     | 22 ++++++++++
 src/test/regress/expected/create_schema.out   | 43 +++++++++++++++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 12 ++++++
 src/test/regress/sql/create_schema.sql        | 27 ++++++++++++
 12 files changed, 166 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 79186d2b936..faf99c3399d 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -100,8 +100,9 @@ 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 DOMAIN</command>
-        <command>CREATE TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE
+        schema. Currently, only <command>CREATE COLLATION</command>,
+        <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/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 0102c9984e7..55b65dd08a3 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2622,6 +2622,22 @@ read_objtype_from_string(const char *objtype)
 	return -1;					/* keep compiler quiet */
 }
 
+const char *
+stringify_objtype(ObjectType objtype)
+{
+	int			i;
+
+	for (i = 0; i < lengthof(ObjectTypeMap); i++)
+	{
+		if (ObjectTypeMap[i].tm_type == objtype)
+			return ObjectTypeMap[i].tm_name;
+	}
+	ereport(ERROR,
+			errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg("unrecognized object type %d", objtype));
+
+	return NULL;					/* keep compiler quiet */
+}
 /*
  * Interfaces to reference fields of ObjectPropertyType
  */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3aca508b08f..30c4a567502 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1627,6 +1627,7 @@ schema_stmt:
 			| GrantStmt
 			| ViewStmt
 			| CreateDomainStmt
+			| DefineStmt
 		;
 
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 218ec6f0982..431023ee734 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -61,6 +61,7 @@
 #include "rewrite/rewriteManip.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
+#include "utils/formatting.h"
 #include "utils/lsyscache.h"
 #include "utils/partcache.h"
 #include "utils/rel.h"
@@ -4171,6 +4172,37 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
 				}
 				break;
 
+			case T_DefineStmt:
+				{
+					char	   *coll_schema = NULL;
+					char	   *collName;
+					DefineStmt *stmt = (DefineStmt *) element;
+
+					if (stmt->kind != OBJECT_COLLATION)
+						ereport(ERROR,
+								errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								errmsg("CREATE SCHEMA ... CREATE %s currently not supported",
+									   asc_toupper(stringify_objtype(stmt->kind),
+												   strlen(stringify_objtype(stmt->kind)))));
+
+					DeconstructQualifiedName(stmt->defnames, &coll_schema, &collName);
+					if (coll_schema && strcmp(coll_schema, schemaName) != 0)
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+								errmsg("CREATE specifies a schema (%s) "
+									   "different from the one being created (%s)",
+									   schemaName, coll_schema));
+
+					elements = lappend(elements, element);
+				}
+				break;
+			case T_CompositeTypeStmt:
+			case T_CreateEnumStmt:
+			case T_CreateRangeStmt:
+				ereport(ERROR,
+						errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						errmsg("CREATE SCHEMA ... CREATE TYPE currently not supported"));
+				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 d7a8c769e35..b6beb868eb2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2178,7 +2178,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", "DOMAIN",
+			COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER", "DOMAIN", "COLLATION",
 			/* for INDEX and TABLE/SEQUENCE, respectively */
 						  "UNIQUE", "UNLOGGED");
 		else
@@ -3370,10 +3370,10 @@ match_previous_words(int pattern_id,
 	else if (Matches("CREATE", "ACCESS", "METHOD", MatchAny, "TYPE", MatchAny))
 		COMPLETE_WITH("HANDLER");
 
-	/* CREATE COLLATION */
-	else if (Matches("CREATE", "COLLATION", MatchAny))
+	/* CREATE COLLATION --- is allowed inside CREATE SCHEMA, so use TailMatches */
+	else if (TailMatches("CREATE", "COLLATION", MatchAny))
 		COMPLETE_WITH("(", "FROM");
-	else if (Matches("CREATE", "COLLATION", MatchAny, "FROM"))
+	else if (TailMatches("CREATE", "COLLATION", MatchAny, "FROM"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations);
 	else if (HeadMatches("CREATE", "COLLATION", MatchAny, "(*"))
 	{
diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h
index 630434b73cf..96e6abc9ffd 100644
--- a/src/include/catalog/objectaddress.h
+++ b/src/include/catalog/objectaddress.h
@@ -79,6 +79,7 @@ extern char *getObjectDescription(const ObjectAddress *object,
 extern char *getObjectDescriptionOids(Oid classid, Oid objid);
 
 extern int	read_objtype_from_string(const char *objtype);
+extern const char *stringify_objtype(ObjectType objtype);
 extern char *getObjectTypeDescription(const ObjectAddress *object,
 									  bool missing_ok);
 extern char *getObjectIdentity(const ObjectAddress *object,
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 d73c4702051..2a905b28600 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -14,8 +14,10 @@ NOTICE:  schema "baz" already exists, skipping
 CREATE SCHEMA element_test
   CREATE TABLE foo (id int)
   CREATE VIEW bar AS SELECT * FROM foo
-  CREATE DOMAIN d1 AS INT;
+  CREATE DOMAIN d1 AS INT
+  CREATE COLLATION coll (LOCALE="C");
 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
+NOTICE:  DDL test: type simple, tag CREATE COLLATION
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 57ada462070..9581935160e 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -15,4 +15,5 @@ CREATE SCHEMA IF NOT EXISTS baz;
 CREATE SCHEMA element_test
   CREATE TABLE foo (id int)
   CREATE VIEW bar AS SELECT * FROM foo
-  CREATE DOMAIN d1 AS INT;
+  CREATE DOMAIN d1 AS INT
+  CREATE COLLATION coll (LOCALE="C");
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 69805d4b9ec..4c303385447 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2690,6 +2690,28 @@ SELECT * FROM t5 ORDER BY c ASC, a ASC;
  3 | d1 | d1
 (3 rows)
 
+--CREATE SCHEMA CREATE COLLATION
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+  CREATE COLLATION coll_icu_und FROM "und-x-icu";
+ERROR:  collation "coll_icu_und" for encoding "UTF8" does not exist
+LINE 2:   CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+                                  ^
+CREATE SCHEMA regress_schema_4
+  CREATE COLLATION coll_icu_und FROM "und-x-icu"
+  CREATE TABLE tts(a TEXT COLLATE coll_icu_und);
+\dO regress_schema_4.*
+                                         List of collations
+      Schema      |     Name     | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? 
+------------------+--------------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_4 | coll_icu_und | icu      |         |       | und    |           | yes
+(1 row)
+
+DROP SCHEMA regress_schema_4 CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to collation regress_schema_4.coll_icu_und
+drop cascades to table regress_schema_4.tts
 -- cleanup
 RESET search_path;
 SET client_min_messages TO warning;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index d6718a9f519..91f586606e7 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -177,6 +177,41 @@ CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
  regress_schema_3 | ss1  | regress_schema_3.ss |           |          | 'hello'::text | 
 (2 rows)
 
+-- Cases where the schema creation with collations
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION public.coll_icu_und FROM "und-x-icu";
+ERROR:  CREATE specifies a schema (regress_schema_4) different from the one being created (public)
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION postgres.public.coll_icu_und FROM "und-x-icu";
+ERROR:  cross-database references are not implemented: postgres.public.coll_icu_und
+--fail. only support collation object for DefineStmt node
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE AGGREGATE balk(int4)(SFUNC = int4_sum(int8, int4),STYPE = int8, PARALLEL = SAFE, INITCOND = '0');
+ERROR:  CREATE SCHEMA ... CREATE AGGREGATE currently not supported
+--ok, qualified schema name for domain should be same as the created schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION regress_schema_4.coll (LOCALE="C")
+  CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll);
+\dO regress_schema_4.*
+                                     List of collations
+      Schema      | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? 
+------------------+------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_4 | coll | libc     | C       | C     |        |           | yes
+(1 row)
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION coll (LOCALE="C")
+  CREATE TABLE t(a TEXT COLLATE coll);
+\dO regress_schema_5.*
+                                     List of collations
+      Schema      | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? 
+------------------+------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_5 | coll | libc     | C       | C     |        |           | yes
+(1 row)
+
 DROP SCHEMA regress_schema_2 CASCADE;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to type regress_schema_2.ss
@@ -187,5 +222,13 @@ 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
+DROP SCHEMA regress_schema_4 CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to collation regress_schema_4.coll
+drop cascades to table regress_schema_4.t
+DROP SCHEMA regress_schema_5 CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to collation regress_schema_5.coll
+drop cascades to table regress_schema_5.t
 -- Clean up
 DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index dbc190227d0..06a46662f7f 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -997,6 +997,18 @@ INSERT INTO t5 (a, b) values (1, 'D1'), (2, 'D2'), (3, 'd1');
 -- rewriting.)
 SELECT * FROM t5 ORDER BY c ASC, a ASC;
 
+--CREATE SCHEMA CREATE COLLATION
+--fail. Execute subcommands in order; we do not implicitly reorder them.
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE TABLE tts(a TEXT COLLATE coll_icu_und)
+  CREATE COLLATION coll_icu_und FROM "und-x-icu";
+
+CREATE SCHEMA regress_schema_4
+  CREATE COLLATION coll_icu_und FROM "und-x-icu"
+  CREATE TABLE tts(a TEXT COLLATE coll_icu_und);
+\dO regress_schema_4.*
+DROP SCHEMA regress_schema_4 CASCADE;
+
 
 -- cleanup
 RESET search_path;
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 3028148e96b..608defcd5e2 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -103,8 +103,35 @@ CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
   CREATE TABLE t(a ss1);
 \dD regress_schema_3.*
 
+-- Cases where the schema creation with collations
+--fail. cannot CREATE DOMAIN to other schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION public.coll_icu_und FROM "und-x-icu";
+
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION postgres.public.coll_icu_und FROM "und-x-icu";
+
+--fail. only support collation object for DefineStmt node
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE AGGREGATE balk(int4)(SFUNC = int4_sum(int8, int4),STYPE = int8, PARALLEL = SAFE, INITCOND = '0');
+
+--ok, qualified schema name for domain should be same as the created schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION regress_schema_4.coll (LOCALE="C")
+  CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll);
+\dO regress_schema_4.*
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION coll (LOCALE="C")
+  CREATE TABLE t(a TEXT COLLATE coll);
+\dO regress_schema_5.*
+
 DROP SCHEMA regress_schema_2 CASCADE;
 DROP SCHEMA regress_schema_3 CASCADE;
+DROP SCHEMA regress_schema_4 CASCADE;
+DROP SCHEMA regress_schema_5 CASCADE;
 
 -- Clean up
 DROP ROLE regress_create_schema_role;
-- 
2.34.1