Subject: Re: CREATE SCHEMA ... CREATE DOMAIN support

From: jian he <[email-masked]>
Date: Sat, 23 Nov 2024 13:19:27 +0800

On Tue, Nov 12, 2024 at 8:55 PM Kirill Reshke <[email-masked]> 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 <[email-masked]>
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

---

From: jian he <[email-masked]>
Date: Wed, 27 Nov 2024 11:42:12 +0800

> 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);
                                    ^
From d95e70bdaa8b3b44d71507d656e3d7e1a3df2647 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 27 Nov 2024 11:27:40 +0800
Subject: [PATCH v3 1/1] support CREATE SCHEMA ... CREATE DOMAIN

now you can:

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
 		;

 
 	/*

 	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

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Nov 2024 22:47:24 -0500

> 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

---

From: Kirill Reshke <[email-masked]>
Date: Wed, 27 Nov 2024 11:38:20 +0500

> attached patch with thorough tests and documentation.

Hi! Thanks for pushing this further.

> 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