public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
Date: Wed, 27 Nov 2024 11:38:20 +0500
Message-ID: <CALdSSPggNNvcad69dhUceZ_gPuEYnKNNd=WJ_WnP=YDmh=iwmw@mail.gmail.com> (raw)
In-Reply-To: <CACJufxFUdgqDiK9B+VNtnAwZOj=O3NqdLtXO_OrOwE5XPdCpBA@mail.gmail.com>
References: <CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com>
<CACJufxG+mrh2O9RS0gX43gU6sv+CMY847eMjMQpe8t4ou-2ryg@mail.gmail.com>
<CACJufxFUdgqDiK9B+VNtnAwZOj=O3NqdLtXO_OrOwE5XPdCpBA@mail.gmail.com>
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
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support
In-Reply-To: <CALdSSPggNNvcad69dhUceZ_gPuEYnKNNd=WJ_WnP=YDmh=iwmw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox