v8-0004-CREATE-SCHEMA-CREATE-TYPE.patch
text/x-patch
Filename: v8-0004-CREATE-SCHEMA-CREATE-TYPE.patch
Type: text/x-patch
Part: 1
From 1d4d10a467adf7ffed45f73dd56166c6fedd5e6d Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 30 Dec 2025 13:48:06 +0800
Subject: [PATCH v8 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
---
doc/src/sgml/ref/create_schema.sgml | 3 +-
src/backend/parser/parse_utilcmd.c | 52 ++++++++++--
.../expected/create_schema.out | 10 ++-
.../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, 206 insertions(+), 11 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 af164e360b5..c2a8afedb3e 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -4496,7 +4496,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));
@@ -4513,16 +4513,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 2a905b28600..b2c85682b20 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -15,9 +15,17 @@ 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
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 9727408dc69..84b35ee6fe6 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -16,4 +16,8 @@ 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");
\ No newline at end of file
+ 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');
\ No newline at end of file
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..68decc41bb0 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_1
+ 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_1)
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..9c8939e24e0 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_1
+ 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