public inbox for [email protected]help / color / mirror / Atom feed
Re: CREATE SCHEMA ... CREATE DOMAIN support 4+ messages / 3 participants [nested] [flat]
* Re: CREATE SCHEMA ... CREATE DOMAIN support @ 2024-11-23 05:19 jian he <[email protected]> 2024-11-27 03:42 ` Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: jian he @ 2024-11-23 05:19 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Tue, Nov 12, 2024 at 8:55 PM Kirill Reshke <[email protected]> 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. Attachments: [text/x-patch] v2-0001-support-CREATE-SCHEMA-.-CREATE-DOMAIN.patch (7.5K, 2-v2-0001-support-CREATE-SCHEMA-.-CREATE-DOMAIN.patch) download | inline diff: From aa1d229544636453fddbb0ba01ddb12bd515f55e Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SCHEMA ... CREATE DOMAIN support 2024-11-23 05:19 Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> @ 2024-11-27 03:42 ` jian he <[email protected]> 2024-11-27 03:47 ` Re: CREATE SCHEMA ... CREATE DOMAIN support Tom Lane <[email protected]> 2024-11-27 06:38 ` Re: CREATE SCHEMA ... CREATE DOMAIN support Kirill Reshke <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: jian he @ 2024-11-27 03:42 UTC (permalink / raw) To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Sat, Nov 23, 2024 at 1:19 PM jian he <[email protected]> wrote: > > I didn't add a doc entry. I will do it later. hi attached patch with thorough tests and documentation. one issue i still have is: CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE create domain ss1 as ss create domain ss as text; ERROR: type "ss" does not exist the error message seems not that OK, if we can point out the error position, that would be great. like what we did with create schema create table: CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE create table t(a int, b x); ERROR: type "x" does not exist LINE 2: create table t(a int, b x); ^ Attachments: [text/x-patch] v3-0001-support-CREATE-SCHEMA-.-CREATE-DOMAIN.patch (8.8K, 2-v3-0001-support-CREATE-SCHEMA-.-CREATE-DOMAIN.patch) download | inline diff: From d95e70bdaa8b3b44d71507d656e3d7e1a3df2647 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 27 Nov 2024 11:27:40 +0800 Subject: [PATCH v3 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 within the to be created schema. it's not allowed to let domain create within a different schema. discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg%40mail.gmail.com --- doc/src/sgml/ref/create_schema.sgml | 2 +- src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 26 +++++++++++ src/test/regress/expected/create_schema.out | 51 +++++++++++++++++++++ src/test/regress/sql/create_schema.sql | 33 +++++++++++++ 5 files changed, 112 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index ed69298ccc..06f6314a5b 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -100,7 +100,7 @@ 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 + schema. Currently, only <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/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..d2b97911cc 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -58,6 +58,57 @@ 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) +--fail. forward references, need reorder. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + create domain ss1 as ss + create domain ss as text; +ERROR: type "ss" does not exist +--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 regress_schema_2.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. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + create view test as select 'hello'::ss as test + create table t(a ss1) + create domain ss as text not null + create domain ss1 as ss; +\dD regress_schema_3.* + List of domains + Schema | Name | Type | Collation | Nullable | Default | Check +------------------+------+---------------------+-----------+----------+---------+------- + regress_schema_3 | ss | text | | not null | | + regress_schema_3 | ss1 | regress_schema_3.ss | | | | +(2 rows) + +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 4 other objects +DETAIL: drop cascades to type regress_schema_3.ss +drop cascades to type regress_schema_3.ss1 +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..421aaa424e 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -47,6 +47,39 @@ 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'); + +--fail. forward references, need reorder. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + create domain ss1 as ss + create domain ss as text; + +--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 regress_schema_2.ss) + create domain regress_schema_2.ss as text not null; +\dD regress_schema_2.* + +--ok, no qualified schema name for domain. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + create view test as select 'hello'::ss as test + create table t(a ss1) + create domain ss as text not null + create domain ss1 as ss; +\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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SCHEMA ... CREATE DOMAIN support 2024-11-23 05:19 Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> 2024-11-27 03:42 ` Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> @ 2024-11-27 03:47 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Tom Lane @ 2024-11-27 03:47 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: Kirill Reshke <[email protected]>; PostgreSQL Hackers <[email protected]> jian he <[email protected]> writes: > one issue i still have is: > CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE > create domain ss1 as ss > create domain ss as text; > ERROR: type "ss" does not exist > the error message seems not that OK, > if we can point out the error position, that would be great. That doesn't happen in the base case either: regression=# create domain ss1 as ss; ERROR: type "ss" does not exist I doubt that fixing it should be part of this patch. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE SCHEMA ... CREATE DOMAIN support 2024-11-23 05:19 Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> 2024-11-27 03:42 ` Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> @ 2024-11-27 06:38 ` Kirill Reshke <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Kirill Reshke @ 2024-11-27 06:38 UTC (permalink / raw) To: jian he <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Wed, 27 Nov 2024 at 08:42, jian he <[email protected]> wrote: > > On Sat, Nov 23, 2024 at 1:19 PM jian he <[email protected]> wrote: > > > > I didn't add a doc entry. I will do it later. > hi > attached patch with thorough tests and documentation. > Hi! Thanks for pushing this further. > one issue i still have is: > CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE > create domain ss1 as ss > create domain ss as text; > ERROR: type "ss" does not exist > > the error message seems not that OK, > if we can point out the error position, that would be great. > like what we did with create schema create table: > > CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE > create table t(a int, b x); > ERROR: type "x" does not exist > LINE 2: create table t(a int, b x); > ^ To implement this, we need to include `ParseLoc location` to the `CreateDomainStmt` struct, which is doubtful, because I don't see any other type of create *something* that does this. `make check` on v3 runs successfully. Test & doc seems fine to me. PFA v4. The only change I made is for a commit message, and pg indent run on this diff. -- Best regards, Kirill Reshke Attachments: [application/octet-stream] v4-0001-Extend-CREATE-SCHEMA-element-with-DOMAIN-support.patch (9.2K, 2-v4-0001-Extend-CREATE-SCHEMA-element-with-DOMAIN-support.patch) download | inline diff: From 390512c87be6384ed07e95b1b9d7e795611508dd Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 27 Nov 2024 11:27:40 +0800 Subject: [PATCH v4] Extend CREATE SCHEMA element with DOMAIN support. SQL standart allow domain 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 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 within the to be created schema. The domain name can be schema-qualified or database-qualified, however it's not allowed to let domain create within a different schema. Author: Kirill Reshke <[email protected]> Author: Jian He <[email protected]> Reviewed-by: Alvaro Herrera <[email protected]> Reviewed-by: Tom Lane <[email protected]> discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg%40mail.gmail.com --- doc/src/sgml/ref/create_schema.sgml | 2 +- src/backend/parser/gram.y | 1 + src/backend/parser/parse_utilcmd.c | 27 +++++++++++ src/test/regress/expected/create_schema.out | 51 +++++++++++++++++++++ src/test/regress/sql/create_schema.sql | 33 +++++++++++++ 5 files changed, 113 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index ed69298ccc6..06f6314a5bb 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -100,7 +100,7 @@ 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 + schema. Currently, only <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/parser/gram.y b/src/backend/parser/gram.y index 67eb96396af..ad8d9270acc 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 0f324ee4e31..45328eea168 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,30 @@ 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 +4140,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 93302a07efc..d2b97911cc8 100644 --- a/src/test/regress/expected/create_schema.out +++ b/src/test/regress/expected/create_schema.out @@ -58,6 +58,57 @@ 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) +--fail. forward references, need reorder. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + create domain ss1 as ss + create domain ss as text; +ERROR: type "ss" does not exist +--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 regress_schema_2.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. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + create view test as select 'hello'::ss as test + create table t(a ss1) + create domain ss as text not null + create domain ss1 as ss; +\dD regress_schema_3.* + List of domains + Schema | Name | Type | Collation | Nullable | Default | Check +------------------+------+---------------------+-----------+----------+---------+------- + regress_schema_3 | ss | text | | not null | | + regress_schema_3 | ss1 | regress_schema_3.ss | | | | +(2 rows) + +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 4 other objects +DETAIL: drop cascades to type regress_schema_3.ss +drop cascades to type regress_schema_3.ss1 +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 1b7064247a1..421aaa424e0 100644 --- a/src/test/regress/sql/create_schema.sql +++ b/src/test/regress/sql/create_schema.sql @@ -47,6 +47,39 @@ 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'); + +--fail. forward references, need reorder. +CREATE SCHEMA regress_schema_2 AUTHORIZATION CURRENT_ROLE + create domain ss1 as ss + create domain ss as text; + +--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 regress_schema_2.ss) + create domain regress_schema_2.ss as text not null; +\dD regress_schema_2.* + +--ok, no qualified schema name for domain. +CREATE SCHEMA regress_schema_3 AUTHORIZATION CURRENT_ROLE + create view test as select 'hello'::ss as test + create table t(a ss1) + create domain ss as text not null + create domain ss1 as ss; +\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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-11-27 06:38 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-23 05:19 Re: CREATE SCHEMA ... CREATE DOMAIN support jian he <[email protected]> 2024-11-27 03:42 ` jian he <[email protected]> 2024-11-27 03:47 ` Tom Lane <[email protected]> 2024-11-27 06:38 ` Kirill Reshke <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox