On Tue, Nov 12, 2024 at 8:55 PM Kirill Reshke <reshkekir...@gmail.com> wrote:
>
> Patch obviously leaks doc & regression tests, but I'm posting it to
> see if this contribution is needed in PostgreSQL
the following two statement should fail:
CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
CREATE table t(a ss)
create domain public.ss as text not null default 'hello' constraint
nn check (value <> 'hello');
CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
CREATE table t(a ss)
create domain postgres.public.ss as text not null default 'hello'
constraint nn check (value <> 'hello');
we aslo need to consider the dependency issue. like the following should be ok.
CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
create view test as select 'hello'::ss as test
CREATE table t(a ss)
create domain ss as text not null;
i fixed these two issues, and add the above example as tests in
src/test/regress/sql/create_schema.sql
I didn't add a doc entry. I will do it later.
From aa1d229544636453fddbb0ba01ddb12bd515f55e Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sat, 23 Nov 2024 13:13:57 +0800
Subject: [PATCH v2 1/1] support CREATE SCHEMA ... CREATE DOMAIN
now you can:
CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
create view test as select 'hello'::ss as test
CREATE table t(a ss)
create domain ss as text not null;
the domain will be created in the same schema as the created schema.
it's not allowed to let domain created in different schema as the the created schema.
discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg%40mail.gmail.com
---
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 26 +++++++++++++
src/test/regress/expected/create_schema.out | 43 +++++++++++++++++++++
src/test/regress/sql/create_schema.sql | 26 +++++++++++++
4 files changed, 96 insertions(+)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 67eb96396a..ad8d9270ac 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1584,6 +1584,7 @@ schema_stmt:
| CreateTrigStmt
| GrantStmt
| ViewStmt
+ | CreateDomainStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0f324ee4e3..e5f5cd22ac 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -105,6 +105,7 @@ typedef struct
List *indexes; /* CREATE INDEX items */
List *triggers; /* CREATE TRIGGER items */
List *grants; /* GRANT items */
+ List *domains; /* DOMAIN items */
} CreateSchemaStmtContext;
@@ -4039,6 +4040,7 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
cxt.indexes = NIL;
cxt.triggers = NIL;
cxt.grants = NIL;
+ cxt.domains = NIL;
/*
* Run through each schema element in the schema element list. Separate
@@ -4107,6 +4109,29 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
cxt.grants = lappend(cxt.grants, element);
break;
+ case T_CreateDomainStmt:
+ {
+ CreateDomainStmt *elp = (CreateDomainStmt *) element;
+ char *domain_schema = NULL;
+
+ /* DOMAIN's schema must the same as the to be created schema
+ * if length of domainname > 3 will fail at
+ * DeconstructQualifiedName,
+ */
+ if (list_length(elp->domainname) == 2)
+ {
+ domain_schema = strVal(list_nth(elp->domainname, 0));
+ setSchemaName(cxt.schemaname, &domain_schema);
+ }
+ else if (list_length(elp->domainname) == 3)
+ {
+ domain_schema = strVal(list_nth(elp->domainname, 1));
+ setSchemaName(cxt.schemaname, &domain_schema);
+ }
+ cxt.domains = lappend(cxt.domains, element);
+ }
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
@@ -4114,6 +4139,7 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
}
result = NIL;
+ result = list_concat(result, cxt.domains);
result = list_concat(result, cxt.sequences);
result = list_concat(result, cxt.tables);
result = list_concat(result, cxt.views);
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 93302a07ef..cf99e8313f 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -58,6 +58,49 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
EXECUTE FUNCTION schema_trig.no_func();
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
RESET ROLE;
+-- Cases where the schema creation with domain.
+--fail. cannot create domain to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain public.ss as text not null default 'hello' constraint nn check (value <> 'hello');
+ERROR: CREATE specifies a schema (public) different from the one being created (regress_schema_2)
+--fail. cannot create domain to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain postgres.public.ss as text not null default 'hello' constraint nn check (value <> 'hello');
+ERROR: CREATE specifies a schema (public) different from the one being created (regress_schema_2)
+--ok, qualified schema name for domain should be same as the created schema.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain regress_schema_2.ss as text not null;
+\dD regress_schema_2.*
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default | Check
+------------------+------+------+-----------+----------+---------+-------
+ regress_schema_2 | ss | text | | not null | |
+(1 row)
+
+--ok, no qualified schema name for domain. also check the dependency.
+CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
+ create view test as select 'hello'::ss as test
+ CREATE table t(a ss)
+ create domain ss as text not null;
+\dD regress_schema_3.*
+ List of domains
+ Schema | Name | Type | Collation | Nullable | Default | Check
+------------------+------+------+-----------+----------+---------+-------
+ regress_schema_3 | ss | text | | not null | |
+(1 row)
+
+DROP SCHEMA regress_schema_2 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to type regress_schema_2.ss
+drop cascades to table regress_schema_2.t
+DROP SCHEMA regress_schema_3 CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to type regress_schema_3.ss
+drop cascades to table regress_schema_3.t
+drop cascades to view regress_schema_3.test
-- Cases where the schema creation succeeds.
-- The schema created matches the role name.
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 1b7064247a..393a173ca8 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -47,6 +47,32 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
EXECUTE FUNCTION schema_trig.no_func();
RESET ROLE;
+-- Cases where the schema creation with domain.
+
+--fail. cannot create domain to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain public.ss as text not null default 'hello' constraint nn check (value <> 'hello');
+--fail. cannot create domain to other schema
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain postgres.public.ss as text not null default 'hello' constraint nn check (value <> 'hello');
+--ok, qualified schema name for domain should be same as the created schema.
+CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE
+ CREATE table t(a ss)
+ create domain regress_schema_2.ss as text not null;
+\dD regress_schema_2.*
+
+--ok, no qualified schema name for domain. also check the dependency.
+CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE
+ create view test as select 'hello'::ss as test
+ CREATE table t(a ss)
+ create domain ss as text not null;
+\dD regress_schema_3.*
+
+DROP SCHEMA regress_schema_2 CASCADE;
+DROP SCHEMA regress_schema_3 CASCADE;
+
-- Cases where the schema creation succeeds.
-- The schema created matches the role name.
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
--
2.34.1
