v8-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
text/x-patch
Filename: v8-0003-CREATE-SCHEMA-CREATE-COLLATION.patch
Type: text/x-patch
Part: 0
From f585d3966cedf6046a863a7a9018a3ffd8399559 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 30 Dec 2025 13:40:48 +0800
Subject: [PATCH v8 3/5] CREATE SCHEMA CREATE COLLATION
The SQL standard allows collation to be specified in a CREATE SCHEMA statement.
This adds support for that capability.
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.
Discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com
---
doc/src/sgml/ref/create_schema.sgml | 3 +-
src/backend/catalog/objectaddress.c | 18 ++++++++
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 40 +++++++++++++++++
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 | 15 +++++++
src/test/regress/expected/create_schema.out | 43 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 7 +++
src/test/regress/sql/create_schema.sql | 29 +++++++++++++
11 files changed, 161 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 79186d2b936..d8273bb2d0c 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -100,7 +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 DOMAIN</command>
+ 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
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index fa6c6df598a..f32052084dc 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2621,6 +2621,24 @@ read_objtype_from_string(const char *objtype)
return -1; /* keep compiler quiet */
}
+/* get the ObjectType name */
+const char *
+stringify_objtype(ObjectType objtype)
+{
+ for (int 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 54e09d0ceb5..07b7bf0ab5c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1638,6 +1638,7 @@ schema_stmt:
| GrantStmt
| ViewStmt
| CreateDomainStmt
+ | DefineStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 849a19c4e47..af164e360b5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -64,6 +64,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"
@@ -4484,6 +4485,45 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
}
break;
+ case T_DefineStmt:
+ {
+ char *coll_schema = NULL;
+ char *collName;
+ char *obj_type;
+
+ DefineStmt *stmt = castNode(DefineStmt, element);
+
+ obj_type = asc_toupper(stringify_objtype(stmt->kind),
+ strlen(stringify_objtype(stmt->kind)));
+
+ if (stmt->kind != OBJECT_COLLATION)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("CREATE SCHEMA ... CREATE %s currently not supported", obj_type));
+
+ DeconstructQualifiedName(stmt->defnames, &coll_schema, &collName);
+
+ if (coll_schema && strcmp(coll_schema, schemaName) != 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE %s specifies a schema (%s) different from the one being created (%s)",
+ obj_type, coll_schema, schemaName));
+
+ elements = lappend(elements, element);
+ }
+ break;
+
+ /*
+ * gram.y classifies these as DefineStmt as well; therefore,
+ * we must explicitly raise an error for these cases.
+ */
+ 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/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..9727408dc69 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");
\ No newline at end of file
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 8023014fe63..7e54e43363f 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1295,6 +1295,21 @@ DROP TABLE test7;
CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!');
NOTICE: using standard form "und" for ICU locale ""
ERROR: could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ERROR
+--CREATE SCHEMA CREATE COLLATION
+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
-- nondeterministic collations
CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true);
NOTICE: using standard form "und" for ICU locale ""
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 0533c29a311..3e061c7a0ef 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. can not CREATE COLLATION to other schema
+CREATE SCHEMA regress_schema_4 AUTHORIZATION CURRENT_ROLE
+ CREATE COLLATION public.coll_icu_und FROM "und-x-icu";
+ERROR: CREATE COLLATION specifies a schema (public) different from the one being created (regress_schema_4)
+--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 collation 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 b6c54503d21..243d69e4d32 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -513,6 +513,13 @@ DROP TABLE test7;
CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!');
+--CREATE SCHEMA CREATE COLLATION
+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;
+
-- nondeterministic collations
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 54a07054767..0f802bcaffe 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -104,8 +104,37 @@ 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. can not CREATE COLLATION 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 collation 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