v10-0004-CREATE-SCHEMA-CREATE-TYPE.patch

From 3136cddd6a8762aacbf6c9cbce84db2300ab0ebc Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 10 Mar 2026 17:56:16 +0800
Subject: [PATCH v10 4/5] CREATE SCHEMA CREATE TYPE

The SQL standard allows types to be specified in a CREATE SCHEMA statement.
This adds support for that capability.

For example:
CREATE SCHEMA schema_name CREATE TYPE ss;

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

Discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5985
---
 doc/src/sgml/ref/create_schema.sgml           |  3 +-
 src/backend/parser/parse_utilcmd.c            | 52 ++++++++++--
 .../expected/create_schema.out                | 16 +++-
 .../test_ddl_deparse/sql/create_schema.sql    |  6 +-
 src/test/regress/expected/create_schema.out   | 83 +++++++++++++++++++
 src/test/regress/expected/create_type.out     | 12 +++
 src/test/regress/sql/create_schema.sql        | 39 +++++++++
 src/test/regress/sql/create_type.sql          | 12 +++
 8 files changed, 211 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index d8273bb2d0c..905e966e30e 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -104,7 +104,8 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp
         <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
+        TRIGGER</command>, <command>CREATE TYPE</command> and
+        <command>GRANT</command> are accepted as clauses
         within <command>CREATE SCHEMA</command>. Other kinds of objects may
         be created in separate commands after the schema is created.
        </para>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 723a017a4cb..b6ee8342fde 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4502,7 +4502,7 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
 					obj_type = asc_toupper(stringify_objtype(stmt->kind),
 										   strlen(stringify_objtype(stmt->kind)));
 
-					if (stmt->kind != OBJECT_COLLATION)
+					if (stmt->kind != OBJECT_COLLATION && stmt->kind != OBJECT_TYPE)
 						ereport(ERROR,
 								errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 								errmsg("CREATE SCHEMA ... CREATE %s currently not supported", obj_type));
@@ -4519,16 +4519,52 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
 				}
 				break;
 
-				/*
-				 * gram.y classifies these as DefineStmt as well; therefore,
-				 * we must explicitly raise an error for these cases.
-				 */
 			case T_CompositeTypeStmt:
+				{
+					CompositeTypeStmt *stmt = castNode(CompositeTypeStmt, element);
+
+					checkSchemaName(pstate, schemaName, stmt->typevar);
+
+					elements = lappend(elements, element);
+				}
+				break;
+
 			case T_CreateEnumStmt:
+				{
+					char	   *typschema = NULL;
+					char	   *typname;
+
+					CreateEnumStmt *stmt = castNode(CreateEnumStmt, element);
+
+					DeconstructQualifiedName(stmt->typeName, &typschema, &typname);
+
+					if (typschema && strcmp(typschema, schemaName) != 0)
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+								errmsg("CREATE TYPE specifies a schema (%s) different from the one being created (%s)",
+									   typschema, schemaName));
+
+					elements = lappend(elements, element);
+				}
+				break;
+
 			case T_CreateRangeStmt:
-				ereport(ERROR,
-						errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-						errmsg("CREATE SCHEMA ... CREATE TYPE currently not supported"));
+				{
+					char	   *typschema = NULL;
+					char	   *typname;
+
+					CreateRangeStmt *stmt = castNode(CreateRangeStmt, element);
+
+					DeconstructQualifiedName(stmt->typeName, &typschema, &typname);
+
+					if (typschema && strcmp(typschema, schemaName) != 0)
+						ereport(ERROR,
+								errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+								errmsg("CREATE TYPE specifies a schema (%s) different from the one being created (%s)",
+									   schemaName, typschema));
+
+					elements = lappend(elements, element);
+				}
 				break;
 			default:
 				elog(ERROR, "unrecognized node type: %d",
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 e3c197af118..92da27c813b 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -15,15 +15,27 @@ CREATE SCHEMA element_test
   CREATE TABLE foo (id int)
   CREATE VIEW bar AS SELECT * FROM foo
   CREATE DOMAIN d1 AS INT
-  CREATE COLLATION coll (LOCALE="C");
+  CREATE COLLATION coll (LOCALE="C")
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE ss AS (a int)
+  CREATE TYPE sss
+  CREATE TYPE rainbow AS ENUM ('red', 'orange');
 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
+NOTICE:  DDL test: type simple, tag CREATE TYPE
+NOTICE:  DDL test: type simple, tag CREATE TYPE
+NOTICE:  DDL test: type simple, tag CREATE TYPE
+NOTICE:  DDL test: type simple, tag CREATE TYPE
 DROP SCHEMA element_test CASCADE;
-NOTICE:  drop cascades to 4 other objects
+NOTICE:  drop cascades to 8 other objects
 DETAIL:  drop cascades to table element_test.foo
 drop cascades to view element_test.bar
 drop cascades to type element_test.d1
 drop cascades to collation element_test.coll
+drop cascades to type element_test.floatrange
+drop cascades to type element_test.ss
+drop cascades to type element_test.sss
+drop cascades to type element_test.rainbow
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 0c61db13935..abe0637c415 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -16,6 +16,10 @@ CREATE SCHEMA element_test
   CREATE TABLE foo (id int)
   CREATE VIEW bar AS SELECT * FROM foo
   CREATE DOMAIN d1 AS INT
-  CREATE COLLATION coll (LOCALE="C");
+  CREATE COLLATION coll (LOCALE="C")
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE ss AS (a int)
+  CREATE TYPE sss
+  CREATE TYPE rainbow AS ENUM ('red', 'orange');
 
 DROP SCHEMA element_test CASCADE;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 3e061c7a0ef..a33cbd0c0d9 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -212,6 +212,76 @@ CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
  regress_schema_5 | coll | libc     | C       | C     |        |           | yes
 (1 row)
 
+-----CREATE SCHEMA CREATE TYPE
+--fail. can not CREATE TYPE to other schema
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a int);
+ERROR:  CREATE specifies a schema (public) different from the one being created (regress_schema_6)
+LINE 1: CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a i...
+                                                   ^
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss;
+ERROR:  CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_6)
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.rainbow AS ENUM ('red', 'orange');
+ERROR:  CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_6)
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+ERROR:  CREATE TYPE specifies a schema (regress_schema_6) different from the one being created (public)
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_6 AUTHORIZATION CURRENT_ROLE
+  CREATE TYPE postgres.public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+ERROR:  cross-database references are not implemented: postgres.public.floatrange
+--the following tests should error, because we execute subcommands in order
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss;
+ERROR:  type "ss" does not exist
+LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE...
+                                                          ^
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss AS (a int);
+ERROR:  type "ss" does not exist
+LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE...
+                                                          ^
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) CREATE TYPE rainbow AS ENUM ('red', 'orange');
+ERROR:  type "rainbow" does not exist
+LINE 1: CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) C...
+                                                          ^
+CREATE SCHEMA regress_schema_6
+  CREATE TABLE tts(a floatrange)
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+ERROR:  type "floatrange" does not exist
+LINE 2:   CREATE TABLE tts(a floatrange)
+                             ^
+--ok
+CREATE SCHEMA regress_schema_6
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE ss AS (a int)
+  CREATE TYPE sss
+  CREATE TYPE rainbow AS ENUM ('red', 'orange');
+\dT regress_schema_6.*
+                        List of data types
+      Schema      |               Name               | Description 
+------------------+----------------------------------+-------------
+ regress_schema_6 | regress_schema_6.floatmultirange | 
+ regress_schema_6 | regress_schema_6.floatrange      | 
+ regress_schema_6 | regress_schema_6.rainbow         | 
+ regress_schema_6 | regress_schema_6.ss              | 
+ regress_schema_6 | regress_schema_6.sss             | 
+(5 rows)
+
+--schema qualified, ok
+CREATE SCHEMA regress_schema_7
+  CREATE TYPE regress_schema_7.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE regress_schema_7.ss AS (a int)
+  CREATE TYPE regress_schema_7.sss
+  CREATE TYPE regress_schema_7.rainbow AS ENUM ('red', 'orange')
+  CREATE TABLE t(a floatrange, b ss, c rainbow);
+\dT regress_schema_7.*
+                        List of data types
+      Schema      |               Name               | Description 
+------------------+----------------------------------+-------------
+ regress_schema_7 | regress_schema_7.floatmultirange | 
+ regress_schema_7 | regress_schema_7.floatrange      | 
+ regress_schema_7 | regress_schema_7.rainbow         | 
+ regress_schema_7 | regress_schema_7.ss              | 
+ regress_schema_7 | regress_schema_7.sss             | 
+(5 rows)
+
 DROP SCHEMA regress_schema_2 CASCADE;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to type regress_schema_2.ss
@@ -230,5 +300,18 @@ 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
+DROP SCHEMA regress_schema_6 CASCADE;
+NOTICE:  drop cascades to 4 other objects
+DETAIL:  drop cascades to type regress_schema_6.floatrange
+drop cascades to type regress_schema_6.ss
+drop cascades to type regress_schema_6.sss
+drop cascades to type regress_schema_6.rainbow
+DROP SCHEMA regress_schema_7 CASCADE;
+NOTICE:  drop cascades to 5 other objects
+DETAIL:  drop cascades to type regress_schema_7.floatrange
+drop cascades to type regress_schema_7.ss
+drop cascades to type regress_schema_7.sss
+drop cascades to type regress_schema_7.rainbow
+drop cascades to table regress_schema_7.t
 -- Clean up
 DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 5181c4290b4..83f63e5c8cc 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -35,6 +35,18 @@ CREATE FUNCTION int44out(city_budget)
 NOTICE:  argument type city_budget is only a shell
 LINE 1: CREATE FUNCTION int44out(city_budget)
                                  ^
+----- CREATE SCHEMA CREATE TYPE
+--error, cannot CREATE TYPE to other schema
+CREATE SCHEMA regress_schema_x
+   CREATE TYPE public.widget (
+      internallength = 24,
+      input = widget_in,
+      output = widget_out,
+      typmod_in = numerictypmodin,
+      typmod_out = numerictypmodout,
+      alignment = double
+   );
+ERROR:  CREATE TYPE specifies a schema (public) different from the one being created (regress_schema_x)
 CREATE TYPE widget (
    internallength = 24,
    input = widget_in,
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 0f802bcaffe..75fd928eacb 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -131,10 +131,49 @@ CREATE SCHEMA regress_schema_5 AUTHORIZATION CURRENT_ROLE
 \dO regress_schema_5.*
 
 
+
+-----CREATE SCHEMA CREATE TYPE
+--fail. can not CREATE TYPE to other schema
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss AS (a int);
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.ss;
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.rainbow AS ENUM ('red', 'orange');
+CREATE SCHEMA regress_schema_6 CREATE TYPE public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+
+--fail. improper qualified name
+CREATE SCHEMA regress_schema_6 AUTHORIZATION CURRENT_ROLE
+  CREATE TYPE postgres.public.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+
+--the following tests should error, because we execute subcommands in order
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss;
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a ss) CREATE TYPE ss AS (a int);
+CREATE SCHEMA regress_schema_6 CREATE TABLE tts(a rainbow) CREATE TYPE rainbow AS ENUM ('red', 'orange');
+CREATE SCHEMA regress_schema_6
+  CREATE TABLE tts(a floatrange)
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi);
+
+--ok
+CREATE SCHEMA regress_schema_6
+  CREATE TYPE floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE ss AS (a int)
+  CREATE TYPE sss
+  CREATE TYPE rainbow AS ENUM ('red', 'orange');
+\dT regress_schema_6.*
+
+--schema qualified, ok
+CREATE SCHEMA regress_schema_7
+  CREATE TYPE regress_schema_7.floatrange AS RANGE (subtype = float8, subtype_diff = float8mi)
+  CREATE TYPE regress_schema_7.ss AS (a int)
+  CREATE TYPE regress_schema_7.sss
+  CREATE TYPE regress_schema_7.rainbow AS ENUM ('red', 'orange')
+  CREATE TABLE t(a floatrange, b ss, c rainbow);
+\dT regress_schema_7.*
+
 DROP SCHEMA regress_schema_2 CASCADE;
 DROP SCHEMA regress_schema_3 CASCADE;
 DROP SCHEMA regress_schema_4 CASCADE;
 DROP SCHEMA regress_schema_5 CASCADE;
+DROP SCHEMA regress_schema_6 CASCADE;
+DROP SCHEMA regress_schema_7 CASCADE;
 
 -- Clean up
 DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c2..69efd069f58 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -32,6 +32,18 @@ CREATE FUNCTION int44out(city_budget)
    AS :'regresslib'
    LANGUAGE C STRICT IMMUTABLE;
 
+----- CREATE SCHEMA CREATE TYPE
+--error, cannot CREATE TYPE to other schema
+CREATE SCHEMA regress_schema_x
+   CREATE TYPE public.widget (
+      internallength = 24,
+      input = widget_in,
+      output = widget_out,
+      typmod_in = numerictypmodin,
+      typmod_out = numerictypmodout,
+      alignment = double
+   );
+
 CREATE TYPE widget (
    internallength = 24,
    input = widget_in,
-- 
2.34.1