v6-0003-CREATE-SCHEMA-CREATE-COLLATION.patch

From 9ff37e0923317cbce1722ea4c0260e72ce0db9c2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 19 Aug 2025 11:31:08 +0800
Subject: [PATCH v6 3/3] 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 AUTHORIZATION CURRENT_ROLE
    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
---
 src/backend/catalog/objectaddress.c           | 16 ++++++
 src/backend/parser/gram.y                     |  1 +
 src/backend/parser/parse_utilcmd.c            | 23 +++++++++
 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 +-
 src/test/regress/expected/create_schema.out   | 50 +++++++++++++++++++
 src/test/regress/sql/create_schema.sql        | 32 ++++++++++++
 9 files changed, 132 insertions(+), 6 deletions(-)

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..5fd6fc63ed6 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4171,6 +4171,29 @@ 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 OBJECT currently not support for %s",
+									   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;
 			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..0070e77731e 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);
+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..19e0aad3cb0 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_icu_und FROM "und-x-icu";
 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..c9a70a0862a 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_icu_und FROM "und-x-icu";
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index d6718a9f519..2669da3b15a 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -177,6 +177,48 @@ 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 objbect 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 OBJECT currently not support for aggregate
+--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)
+                                  ^
+--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_icu_und FROM "und-x-icu"
+  CREATE TABLE t(a TEXT COLLATE regress_schema_4.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)
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION coll_icu_und FROM "und-x-icu"
+  CREATE TABLE t(a TEXT COLLATE regress_schema_5.coll_icu_und);
+\dO regress_schema_5.*
+                                         List of collations
+      Schema      |     Name     | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic? 
+------------------+--------------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_5 | coll_icu_und | icu      |         |       | und    |           | 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 +229,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_icu_und
+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_icu_und
+drop cascades to table regress_schema_5.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 3028148e96b..e99915da383 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -103,8 +103,40 @@ 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 objbect 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');
+
+--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";
+
+--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_icu_und FROM "und-x-icu"
+  CREATE TABLE t(a TEXT COLLATE regress_schema_4.coll_icu_und);
+\dO regress_schema_4.*
+
+--ok, no qualified schema name for collation
+CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
+  CREATE COLLATION coll_icu_und FROM "und-x-icu"
+  CREATE TABLE t(a TEXT COLLATE regress_schema_5.coll_icu_und);
+\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