Re: CREATE SCHEMA ... CREATE DOMAIN support - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: CREATE SCHEMA ... CREATE DOMAIN support |
| Date | |
| Msg-id | 1105374.1775426741@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: CREATE SCHEMA ... CREATE DOMAIN support (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
I wrote:
> Here's a revised patchset that I think is pretty close to committable.
The cfbot reminded me that I'd failed to check whether the regression
test outputs are stable across different collations. Grumble.
Fixed that, and rewrote the changes in create_schema.sgml as I'd been
speculating about earlier.
regards, tom lane
From 4f2ecd1698799bf145b4e1b67cf715e206128f0f Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 5 Apr 2026 17:39:03 -0400
Subject: [PATCH v13 1/3] Don't try to re-order the subcommands of CREATE
SCHEMA.
transformCreateSchemaStmtElements has always believed that it is
supposed to re-order the subcommands of CREATE SCHEMA into a safe
execution order. However, it is nowhere near being capable of doing
that correctly. Nor is there reason to think that it ever will be,
or that that is a well-defined requirement. (The SQL standard does
say that it should be possible to do foreign-key forward references
within CREATE SCHEMA, but it's not clear that the text requires
anything more than that.) Moreover, the problem will get worse as
we add more subcommand types. Let's just drop the whole idea and
execute the commands in the order given, which seems like a much
less astonishment-prone definition anyway. The foreign-key issue
will be handled in a follow-up patch.
Along the way, pass down a ParseState so that we can provide an
error cursor for "wrong schema name" and related errors, and fix
transformCreateSchemaStmtElements so that it doesn't scribble
on the parsetree passed to it.
Author: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/1075425.1732993688@sss.pgh.pa.us
---
doc/src/sgml/ref/create_schema.sgml | 17 ++-
src/backend/commands/extension.c | 7 +-
src/backend/commands/schemacmds.c | 14 +--
src/backend/parser/parse_utilcmd.c | 131 ++++++++------------
src/backend/tcop/utility.c | 4 +-
src/include/commands/schemacmds.h | 7 +-
src/include/parser/parse_utilcmd.h | 3 +-
src/test/regress/expected/create_schema.out | 37 ++++++
src/test/regress/expected/create_view.out | 2 +
src/test/regress/expected/event_trigger.out | 2 +-
src/test/regress/expected/namespace.out | 9 +-
src/test/regress/sql/create_schema.sql | 5 +
src/test/regress/sql/namespace.sql | 11 +-
src/tools/pgindent/typedefs.list | 1 -
14 files changed, 138 insertions(+), 112 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index ed69298ccc6..9e6f0e24339 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -131,6 +131,11 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp
<literal>CREATE</literal> privilege for the current database.
(Of course, superusers bypass this check.)
</para>
+
+ <para>
+ The <replaceable class="parameter">schema_element</replaceable>
+ subcommands, if any, are executed in the order they are written.
+ </para>
</refsect1>
<refsect1>
@@ -193,12 +198,12 @@ CREATE VIEW hollywood.winners AS
</para>
<para>
- The SQL standard specifies that the subcommands in <command>CREATE
- SCHEMA</command> can appear in any order. The present
- <productname>PostgreSQL</productname> implementation does not
- handle all cases of forward references in subcommands; it might
- sometimes be necessary to reorder the subcommands in order to avoid
- forward references.
+ Some other SQL implementations attempt to allow more kinds of forward
+ references to objects defined in
+ later <replaceable class="parameter">schema_element</replaceable>
+ subcommands than just foreign key constraints. This is difficult or
+ impossible to do correctly in general, and it is not clear that the SQL
+ standard requires any such behavior except for foreign keys.
</para>
<para>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index b98801d08f2..a330b5fd6ce 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1952,14 +1952,17 @@ CreateExtensionInternal(char *extensionName,
if (!OidIsValid(schemaOid))
{
+ ParseState *pstate = make_parsestate(NULL);
CreateSchemaStmt *csstmt = makeNode(CreateSchemaStmt);
+ pstate->p_sourcetext = "(generated CREATE SCHEMA command)";
+
csstmt->schemaname = schemaName;
csstmt->authrole = NULL; /* will be created by current user */
csstmt->schemaElts = NIL;
csstmt->if_not_exists = false;
- CreateSchemaCommand(csstmt, "(generated CREATE SCHEMA command)",
- -1, -1);
+
+ CreateSchemaCommand(pstate, csstmt, -1, -1);
/*
* CreateSchemaCommand includes CommandCounterIncrement, so new
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index 4c51e920626..bfaa4743cd8 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -49,7 +49,7 @@ static void AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerI
* a subquery.
*/
Oid
-CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString,
+CreateSchemaCommand(ParseState *pstate, CreateSchemaStmt *stmt,
int stmt_location, int stmt_len)
{
const char *schemaName = stmt->schemaname;
@@ -189,12 +189,12 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString,
/*
* Examine the list of commands embedded in the CREATE SCHEMA command, and
- * reorganize them into a sequentially executable order with no forward
- * references. Note that the result is still a list of raw parsetrees ---
- * we cannot, in general, run parse analysis on one statement until we
- * have actually executed the prior ones.
+ * do preliminary transformations. Note that the result is still a list
+ * of raw parsetrees --- we cannot, in general, run parse analysis on one
+ * statement until we have actually executed the prior ones.
*/
- parsetree_list = transformCreateSchemaStmtElements(stmt->schemaElts,
+ parsetree_list = transformCreateSchemaStmtElements(pstate,
+ stmt->schemaElts,
schemaName);
/*
@@ -219,7 +219,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString,
/* do this step */
ProcessUtility(wrapper,
- queryString,
+ pstate->p_sourcetext,
false,
PROCESS_UTILITY_SUBCOMMAND,
NULL,
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 9a918e14aa7..cb2e57610b2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -99,18 +99,6 @@ typedef struct
bool ofType; /* true if statement contains OF typename */
} CreateStmtContext;
-/* State shared by transformCreateSchemaStmtElements and its subroutines */
-typedef struct
-{
- const char *schemaname; /* name of schema */
- List *sequences; /* CREATE SEQUENCE items */
- List *tables; /* CREATE TABLE items */
- List *views; /* CREATE VIEW items */
- List *indexes; /* CREATE INDEX items */
- List *triggers; /* CREATE TRIGGER items */
- List *grants; /* GRANT items */
-} CreateSchemaStmtContext;
-
static void transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column);
@@ -137,7 +125,8 @@ static void transformCheckConstraints(CreateStmtContext *cxt,
static void transformConstraintAttrs(CreateStmtContext *cxt,
List *constraintList);
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
-static void setSchemaName(const char *context_schema, char **stmt_schema_name);
+static void checkSchemaName(ParseState *pstate, const char *context_schema,
+ RangeVar *relation);
static void transformPartitionCmd(CreateStmtContext *cxt, PartitionBoundSpec *bound);
static List *transformPartitionRangeBounds(ParseState *pstate, List *blist,
Relation parent);
@@ -4395,51 +4384,35 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column)
* transformCreateSchemaStmtElements -
* analyzes the elements of a CREATE SCHEMA statement
*
- * Split the schema element list from a CREATE SCHEMA statement into
- * individual commands and place them in the result list in an order
- * such that there are no forward references (e.g. GRANT to a table
- * created later in the list). Note that the logic we use for determining
- * forward references is presently quite incomplete.
+ * This is now somewhat vestigial: its only real responsibility is to complain
+ * if any of the elements are trying to create objects outside the new schema.
+ * We used to try to re-order the commands in a way that would work even if
+ * the user-written order would not, but that's too hard (perhaps impossible)
+ * to do correctly with not-yet-parse-analyzed commands. Now we'll just
+ * execute the elements in the order given.
*
* "schemaName" is the name of the schema that will be used for the creation
- * of the objects listed, that may be compiled from the schema name defined
+ * of the objects listed. It may be obtained from the schema name defined
* in the statement or a role specification.
*
- * SQL also allows constraints to make forward references, so thumb through
- * the table columns and move forward references to a posterior alter-table
- * command.
- *
* The result is a list of parse nodes that still need to be analyzed ---
* but we can't analyze the later commands until we've executed the earlier
* ones, because of possible inter-object references.
- *
- * Note: this breaks the rules a little bit by modifying schema-name fields
- * within passed-in structs. However, the transformation would be the same
- * if done over, so it should be all right to scribble on the input to this
- * extent.
*/
List *
-transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
+transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
+ const char *schemaName)
{
- CreateSchemaStmtContext cxt;
- List *result;
- ListCell *elements;
-
- cxt.schemaname = schemaName;
- cxt.sequences = NIL;
- cxt.tables = NIL;
- cxt.views = NIL;
- cxt.indexes = NIL;
- cxt.triggers = NIL;
- cxt.grants = NIL;
+ List *elements = NIL;
+ ListCell *lc;
/*
- * Run through each schema element in the schema element list. Separate
- * statements by type, and do preliminary analysis.
+ * Run through each schema element in the schema element list. Check
+ * target schema names, and collect the list of actions to be done.
*/
- foreach(elements, schemaElts)
+ foreach(lc, schemaElts)
{
- Node *element = lfirst(elements);
+ Node *element = lfirst(lc);
switch (nodeTag(element))
{
@@ -4447,8 +4420,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
{
CreateSeqStmt *elp = (CreateSeqStmt *) element;
- setSchemaName(cxt.schemaname, &elp->sequence->schemaname);
- cxt.sequences = lappend(cxt.sequences, element);
+ checkSchemaName(pstate, schemaName, elp->sequence);
+ elements = lappend(elements, element);
}
break;
@@ -4456,12 +4429,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
{
CreateStmt *elp = (CreateStmt *) element;
- setSchemaName(cxt.schemaname, &elp->relation->schemaname);
-
- /*
- * XXX todo: deal with constraints
- */
- cxt.tables = lappend(cxt.tables, element);
+ checkSchemaName(pstate, schemaName, elp->relation);
+ elements = lappend(elements, element);
}
break;
@@ -4469,12 +4438,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
{
ViewStmt *elp = (ViewStmt *) element;
- setSchemaName(cxt.schemaname, &elp->view->schemaname);
-
- /*
- * XXX todo: deal with references between views
- */
- cxt.views = lappend(cxt.views, element);
+ checkSchemaName(pstate, schemaName, elp->view);
+ elements = lappend(elements, element);
}
break;
@@ -4482,8 +4447,8 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
{
IndexStmt *elp = (IndexStmt *) element;
- setSchemaName(cxt.schemaname, &elp->relation->schemaname);
- cxt.indexes = lappend(cxt.indexes, element);
+ checkSchemaName(pstate, schemaName, elp->relation);
+ elements = lappend(elements, element);
}
break;
@@ -4491,13 +4456,13 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
{
CreateTrigStmt *elp = (CreateTrigStmt *) element;
- setSchemaName(cxt.schemaname, &elp->relation->schemaname);
- cxt.triggers = lappend(cxt.triggers, element);
+ checkSchemaName(pstate, schemaName, elp->relation);
+ elements = lappend(elements, element);
}
break;
case T_GrantStmt:
- cxt.grants = lappend(cxt.grants, element);
+ elements = lappend(elements, element);
break;
default:
@@ -4506,32 +4471,40 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
}
}
- result = NIL;
- result = list_concat(result, cxt.sequences);
- result = list_concat(result, cxt.tables);
- result = list_concat(result, cxt.views);
- result = list_concat(result, cxt.indexes);
- result = list_concat(result, cxt.triggers);
- result = list_concat(result, cxt.grants);
-
- return result;
+ return elements;
}
/*
- * setSchemaName
- * Set or check schema name in an element of a CREATE SCHEMA command
+ * checkSchemaName
+ * Check schema name in an element of a CREATE SCHEMA command
+ *
+ * It's okay if the command doesn't specify a target schema name, because
+ * CreateSchemaCommand will set up the default creation schema to be the
+ * new schema. But if a target schema name is given, it had better match.
+ * We also have to check that the command doesn't say CREATE TEMP, since
+ * that would likewise put the object into the wrong schema.
*/
static void
-setSchemaName(const char *context_schema, char **stmt_schema_name)
+checkSchemaName(ParseState *pstate, const char *context_schema,
+ RangeVar *relation)
{
- if (*stmt_schema_name == NULL)
- *stmt_schema_name = unconstify(char *, context_schema);
- else if (strcmp(context_schema, *stmt_schema_name) != 0)
+ if (relation->schemaname != NULL &&
+ strcmp(context_schema, relation->schemaname) != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
errmsg("CREATE specifies a schema (%s) "
"different from the one being created (%s)",
- *stmt_schema_name, context_schema)));
+ relation->schemaname, context_schema),
+ parser_errposition(pstate, relation->location)));
+
+ if (relation->relpersistence == RELPERSISTENCE_TEMP)
+ {
+ /* spell this error the same as in RangeVarAdjustRelationPersistence */
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot create temporary relation in non-temporary schema"),
+ parser_errposition(pstate, relation->location)));
+ }
}
/*
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 2b609bfc824..832d7647904 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1122,8 +1122,8 @@ ProcessUtilitySlow(ParseState *pstate,
* relation and attribute manipulation
*/
case T_CreateSchemaStmt:
- CreateSchemaCommand((CreateSchemaStmt *) parsetree,
- queryString,
+ CreateSchemaCommand(pstate,
+ (CreateSchemaStmt *) parsetree,
pstmt->stmt_location,
pstmt->stmt_len);
diff --git a/src/include/commands/schemacmds.h b/src/include/commands/schemacmds.h
index d100e4691b3..b4348436dd3 100644
--- a/src/include/commands/schemacmds.h
+++ b/src/include/commands/schemacmds.h
@@ -16,12 +16,11 @@
#define SCHEMACMDS_H
#include "catalog/objectaddress.h"
-#include "nodes/parsenodes.h"
+#include "parser/parse_node.h"
-extern Oid CreateSchemaCommand(CreateSchemaStmt *stmt,
- const char *queryString,
+extern Oid CreateSchemaCommand(ParseState *pstate,
+ CreateSchemaStmt *stmt,
int stmt_location, int stmt_len);
-
extern ObjectAddress RenameSchema(const char *oldname, const char *newname);
extern ObjectAddress AlterSchemaOwner(const char *name, Oid newOwnerId);
extern void AlterSchemaOwner_oid(Oid schemaoid, Oid newOwnerId);
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 53f282f77ec..34c98e5122f 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -30,7 +30,8 @@ extern CreateStatsStmt *transformStatsStmt(Oid relid, CreateStatsStmt *stmt,
const char *queryString);
extern void transformRuleStmt(RuleStmt *stmt, const char *queryString,
List **actions, Node **whereClause);
-extern List *transformCreateSchemaStmtElements(List *schemaElts,
+extern List *transformCreateSchemaStmtElements(ParseState *pstate,
+ List *schemaElts,
const char *schemaName);
extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation parent,
PartitionBoundSpec *spec);
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 93302a07efc..4ab947a60a8 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -9,55 +9,92 @@ CREATE ROLE regress_create_schema_role SUPERUSER;
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
CREATE SEQUENCE schema_not_existing.seq;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE SEQUENCE schema_not_existing.seq;
+ ^
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
CREATE TABLE schema_not_existing.tab (id int);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE TABLE schema_not_existing.tab (id int);
+ ^
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
CREATE VIEW schema_not_existing.view AS SELECT 1;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1;
+ ^
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
CREATE INDEX ON schema_not_existing.tab (id);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE INDEX ON schema_not_existing.tab (id);
+ ^
CREATE SCHEMA AUTHORIZATION regress_create_schema_role
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
EXECUTE FUNCTION schema_trig.no_func();
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi...
+ ^
-- Again, with a role specification and no schema names.
SET ROLE regress_create_schema_role;
CREATE SCHEMA AUTHORIZATION CURRENT_ROLE
CREATE SEQUENCE schema_not_existing.seq;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE SEQUENCE schema_not_existing.seq;
+ ^
CREATE SCHEMA AUTHORIZATION CURRENT_ROLE
CREATE TABLE schema_not_existing.tab (id int);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE TABLE schema_not_existing.tab (id int);
+ ^
CREATE SCHEMA AUTHORIZATION CURRENT_ROLE
CREATE VIEW schema_not_existing.view AS SELECT 1;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1;
+ ^
CREATE SCHEMA AUTHORIZATION CURRENT_ROLE
CREATE INDEX ON schema_not_existing.tab (id);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE INDEX ON schema_not_existing.tab (id);
+ ^
CREATE SCHEMA AUTHORIZATION CURRENT_ROLE
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
EXECUTE FUNCTION schema_trig.no_func();
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created
(regress_create_schema_role)
+LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi...
+ ^
-- Again, with a schema name and a role specification.
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
CREATE SEQUENCE schema_not_existing.seq;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
+LINE 2: CREATE SEQUENCE schema_not_existing.seq;
+ ^
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
CREATE TABLE schema_not_existing.tab (id int);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
+LINE 2: CREATE TABLE schema_not_existing.tab (id int);
+ ^
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
CREATE VIEW schema_not_existing.view AS SELECT 1;
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
+LINE 2: CREATE VIEW schema_not_existing.view AS SELECT 1;
+ ^
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
CREATE INDEX ON schema_not_existing.tab (id);
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
+LINE 2: CREATE INDEX ON schema_not_existing.tab (id);
+ ^
CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_existing.tab
EXECUTE FUNCTION schema_trig.no_func();
ERROR: CREATE specifies a schema (schema_not_existing) different from the one being created (regress_schema_1)
+LINE 2: CREATE TRIGGER schema_trig BEFORE INSERT ON schema_not_exi...
+ ^
RESET ROLE;
+-- Forward references no longer work in general.
+CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
+ CREATE VIEW abcd_view AS SELECT a FROM abcd
+ CREATE TABLE abcd (a int);
+ERROR: relation "abcd" does not exist
+LINE 2: CREATE VIEW abcd_view AS SELECT a FROM abcd
+ ^
-- 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/expected/create_view.out b/src/test/regress/expected/create_view.out
index bc4f79938b3..63cf4b4371d 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -128,6 +128,8 @@ ERROR: cannot create temporary relation in non-temporary schema
CREATE SCHEMA test_view_schema
CREATE TEMP VIEW testview AS SELECT 1;
ERROR: cannot create temporary relation in non-temporary schema
+LINE 2: CREATE TEMP VIEW testview AS SELECT 1;
+ ^
-- joins: if any of the join relations are temporary, the view
-- should also be temporary
-- should be non-temp
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index f897b079e67..4c32e1dcaf5 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -425,13 +425,13 @@ NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.one
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_pkey
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_a_seq
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq
+NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two
NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view
-NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx
-- View with column additions
CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id;
NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view
diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index dbbda72d395..2e582e783c2 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -10,13 +10,14 @@ SELECT pg_catalog.set_config('search_path', ' ', false);
(1 row)
CREATE SCHEMA test_ns_schema_1
- CREATE UNIQUE INDEX abc_a_idx ON abc (a)
- CREATE VIEW abc_view AS
- SELECT a+1 AS a, b+1 AS b FROM abc
CREATE TABLE abc (
a serial,
b int UNIQUE
- );
+ )
+ CREATE UNIQUE INDEX abc_a_idx ON abc (a)
+ CREATE VIEW abc_view AS
+ SELECT a+1 AS a, b+1 AS b FROM abc
+;
-- verify that the correct search_path restored on abort
SET search_path to public;
BEGIN;
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 1b7064247a1..62651342114 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -47,6 +47,11 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
EXECUTE FUNCTION schema_trig.no_func();
RESET ROLE;
+-- Forward references no longer work in general.
+CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
+ CREATE VIEW abcd_view AS SELECT a FROM abcd
+ CREATE TABLE abcd (a int);
+
-- 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/namespace.sql b/src/test/regress/sql/namespace.sql
index 306cdc2d8c6..a75d4f580d3 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -7,15 +7,16 @@
SELECT pg_catalog.set_config('search_path', ' ', false);
CREATE SCHEMA test_ns_schema_1
+ CREATE TABLE abc (
+ a serial,
+ b int UNIQUE
+ )
+
CREATE UNIQUE INDEX abc_a_idx ON abc (a)
CREATE VIEW abc_view AS
SELECT a+1 AS a, b+1 AS b FROM abc
-
- CREATE TABLE abc (
- a serial,
- b int UNIQUE
- );
+;
-- verify that the correct search_path restored on abort
SET search_path to public;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6a328fceaee..2e99972e0ad 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -585,7 +585,6 @@ CreateRangeStmt
CreateReplicationSlotCmd
CreateRoleStmt
CreateSchemaStmt
-CreateSchemaStmtContext
CreateSeqStmt
CreateStatsStmt
CreateStmt
--
2.43.7
From af2bfc3990df675b20a8ea90196ab5b2e3093950 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 5 Apr 2026 17:45:07 -0400
Subject: [PATCH v13 2/3] Execute foreign key constraints in CREATE SCHEMA at
the end.
The previous patch simplified CREATE SCHEMA's behavior to "execute all
subcommands in the order they are written". However, that's a bit too
simple, as the spec clearly requires forward references in foreign key
constraint clauses to work. (Most other SQL implementations seem to
read more into the spec than that, but it's not clear that there's
justification for more in the text, and this is the only case that
doesn't introduce unresolvable ambiguities.)
To fix that, transform FOREIGN KEY clauses into ALTER TABLE ... ADD
FOREIGN KEY commands and append them to the end of the CREATE SCHEMA's
subcommand list. This works because the foreign key constraints are
independent and don't affect any other DDL that might be in CREATE
SCHEMA. For simplicity, we do this for all FOREIGN KEY clauses even
if they would have worked where they were.
Author: jian he <jian.universality@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/1075425.1732993688@sss.pgh.pa.us
---
doc/src/sgml/ref/create_schema.sgml | 3 +
src/backend/parser/parse_utilcmd.c | 219 ++++++++++++++++--
.../expected/create_schema.out | 25 ++
.../test_ddl_deparse/sql/create_schema.sql | 11 +
src/test/regress/expected/create_schema.out | 46 ++++
src/test/regress/expected/event_trigger.out | 2 +-
src/test/regress/sql/create_schema.sql | 27 +++
7 files changed, 310 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 9e6f0e24339..96bc496e777 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -135,6 +135,9 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp
<para>
The <replaceable class="parameter">schema_element</replaceable>
subcommands, if any, are executed in the order they are written.
+ An exception is that foreign key constraint clauses in <command>CREATE
+ TABLE</command> subcommands are postponed and added at the end.
+ This allows circular foreign key references, which are sometimes useful.
</para>
</refsect1>
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index cb2e57610b2..1bc5df7e0ef 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -122,11 +122,14 @@ static void transformFKConstraints(CreateStmtContext *cxt,
bool isAddConstraint);
static void transformCheckConstraints(CreateStmtContext *cxt,
bool skipValidation);
-static void transformConstraintAttrs(CreateStmtContext *cxt,
+static void transformConstraintAttrs(ParseState *pstate,
List *constraintList);
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
static void checkSchemaName(ParseState *pstate, const char *context_schema,
RangeVar *relation);
+static CreateStmt *transformCreateSchemaCreateTable(ParseState *pstate,
+ CreateStmt *stmt,
+ List **fk_elements);
static void transformPartitionCmd(CreateStmtContext *cxt, PartitionBoundSpec *bound);
static List *transformPartitionRangeBounds(ParseState *pstate, List *blist,
Relation parent);
@@ -693,7 +696,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
}
/* Process column constraints, if any... */
- transformConstraintAttrs(cxt, column->constraints);
+ transformConstraintAttrs(cxt->pstate, column->constraints);
/*
* First, scan the column's constraints to see if a not-null constraint
@@ -4194,9 +4197,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
* NOTE: currently, attributes are only supported for FOREIGN KEY, UNIQUE,
* EXCLUSION, and PRIMARY KEY constraints, but someday they ought to be
* supported for other constraint types.
+ *
+ * NOTE: this must be idempotent in non-error cases; see
+ * transformCreateSchemaCreateTable.
*/
static void
-transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
+transformConstraintAttrs(ParseState *pstate, List *constraintList)
{
Constraint *lastprimarycon = NULL;
bool saw_deferrability = false;
@@ -4225,12 +4231,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced DEFERRABLE clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_deferrability)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_deferrability = true;
lastprimarycon->deferrable = true;
break;
@@ -4240,12 +4246,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced NOT DEFERRABLE clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_deferrability)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_deferrability = true;
lastprimarycon->deferrable = false;
if (saw_initially &&
@@ -4253,7 +4259,7 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
break;
case CONSTR_ATTR_DEFERRED:
@@ -4261,12 +4267,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced INITIALLY DEFERRED clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_initially)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_initially = true;
lastprimarycon->initdeferred = true;
@@ -4279,7 +4285,7 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
break;
case CONSTR_ATTR_IMMEDIATE:
@@ -4287,12 +4293,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced INITIALLY IMMEDIATE clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_initially)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_initially = true;
lastprimarycon->initdeferred = false;
break;
@@ -4304,12 +4310,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced ENFORCED clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_enforced)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_enforced = true;
lastprimarycon->is_enforced = true;
break;
@@ -4321,12 +4327,12 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("misplaced NOT ENFORCED clause"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
if (saw_enforced)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"),
- parser_errposition(cxt->pstate, con->location)));
+ parser_errposition(pstate, con->location)));
saw_enforced = true;
lastprimarycon->is_enforced = false;
@@ -4384,12 +4390,17 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column)
* transformCreateSchemaStmtElements -
* analyzes the elements of a CREATE SCHEMA statement
*
- * This is now somewhat vestigial: its only real responsibility is to complain
- * if any of the elements are trying to create objects outside the new schema.
+ * This presently has two responsibilities. We verify that no subcommands are
+ * trying to create objects outside the new schema. We also pull out any
+ * foreign-key constraint clauses embedded in CREATE TABLE subcommands, and
+ * convert them to ALTER TABLE ADD CONSTRAINT commands appended to the list.
+ * This supports forward references in foreign keys, which is required by the
+ * SQL standard.
+ *
* We used to try to re-order the commands in a way that would work even if
* the user-written order would not, but that's too hard (perhaps impossible)
* to do correctly with not-yet-parse-analyzed commands. Now we'll just
- * execute the elements in the order given.
+ * execute the elements in the order given, except for foreign keys.
*
* "schemaName" is the name of the schema that will be used for the creation
* of the objects listed. It may be obtained from the schema name defined
@@ -4398,12 +4409,17 @@ transformColumnType(CreateStmtContext *cxt, ColumnDef *column)
* The result is a list of parse nodes that still need to be analyzed ---
* but we can't analyze the later commands until we've executed the earlier
* ones, because of possible inter-object references.
+ *
+ * Note it's important that we not modify the input data structure. We create
+ * a new result List, and we copy any CREATE TABLE subcommands that we might
+ * modify.
*/
List *
transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
const char *schemaName)
{
List *elements = NIL;
+ List *fk_elements = NIL;
ListCell *lc;
/*
@@ -4430,7 +4446,11 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
CreateStmt *elp = (CreateStmt *) element;
checkSchemaName(pstate, schemaName, elp->relation);
- elements = lappend(elements, element);
+ /* Pull out any foreign key clauses, add to fk_elements */
+ elp = transformCreateSchemaCreateTable(pstate,
+ elp,
+ &fk_elements);
+ elements = lappend(elements, elp);
}
break;
@@ -4471,7 +4491,7 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
}
}
- return elements;
+ return list_concat(elements, fk_elements);
}
/*
@@ -4507,6 +4527,161 @@ checkSchemaName(ParseState *pstate, const char *context_schema,
}
}
+/*
+ * transformCreateSchemaCreateTable
+ * Process one CreateStmt for transformCreateSchemaStmtElements.
+ *
+ * We remove any foreign-key clauses in the statement and convert them into
+ * ALTER TABLE commands, which we append to *fk_elements.
+ */
+static CreateStmt *
+transformCreateSchemaCreateTable(ParseState *pstate,
+ CreateStmt *stmt,
+ List **fk_elements)
+{
+ CreateStmt *newstmt;
+ List *newElts = NIL;
+ ListCell *lc;
+
+ /*
+ * Flat-copy the CreateStmt node, allowing us to replace its tableElts
+ * list without damaging the input data structure. Most sub-nodes will be
+ * shared with the input, though.
+ */
+ newstmt = makeNode(CreateStmt);
+ memcpy(newstmt, stmt, sizeof(CreateStmt));
+
+ /* Scan for foreign-key constraints */
+ foreach(lc, stmt->tableElts)
+ {
+ Node *element = lfirst(lc);
+ AlterTableStmt *alterstmt;
+ AlterTableCmd *altercmd;
+
+ if (IsA(element, Constraint))
+ {
+ Constraint *constr = (Constraint *) element;
+
+ if (constr->contype != CONSTR_FOREIGN)
+ {
+ /* Other constraint types pass through unchanged */
+ newElts = lappend(newElts, constr);
+ continue;
+ }
+
+ /* Make it into an ALTER TABLE ADD CONSTRAINT command */
+ altercmd = makeNode(AlterTableCmd);
+ altercmd->subtype = AT_AddConstraint;
+ altercmd->name = NULL;
+ altercmd->def = (Node *) copyObject(constr);
+
+ alterstmt = makeNode(AlterTableStmt);
+ alterstmt->relation = copyObject(stmt->relation);
+ alterstmt->cmds = list_make1(altercmd);
+ alterstmt->objtype = OBJECT_TABLE;
+
+ *fk_elements = lappend(*fk_elements, alterstmt);
+ }
+ else if (IsA(element, ColumnDef))
+ {
+ ColumnDef *entry = (ColumnDef *) element;
+ ColumnDef *newentry;
+ List *entryconstraints;
+ bool afterFK = false;
+
+ /*
+ * We must preprocess the list of column constraints to attach
+ * attributes such as DEFERRED to the appropriate constraint node.
+ * Do this on a copy. (But execution of the CreateStmt will run
+ * transformConstraintAttrs on the copy, so we are nonetheless
+ * relying on transformConstraintAttrs to be idempotent.)
+ */
+ entryconstraints = copyObject(entry->constraints);
+ transformConstraintAttrs(pstate, entryconstraints);
+
+ /* Scan the column constraints ... */
+ foreach_node(Constraint, colconstr, entryconstraints)
+ {
+ switch (colconstr->contype)
+ {
+ case CONSTR_FOREIGN:
+ /* colconstr is already a copy, OK to modify */
+ colconstr->fk_attrs = list_make1(makeString(entry->colname));
+
+ /* Make it into an ALTER TABLE ADD CONSTRAINT command */
+ altercmd = makeNode(AlterTableCmd);
+ altercmd->subtype = AT_AddConstraint;
+ altercmd->name = NULL;
+ altercmd->def = (Node *) colconstr;
+
+ alterstmt = makeNode(AlterTableStmt);
+ alterstmt->relation = copyObject(stmt->relation);
+ alterstmt->cmds = list_make1(altercmd);
+ alterstmt->objtype = OBJECT_TABLE;
+
+ *fk_elements = lappend(*fk_elements, alterstmt);
+
+ /* Remove the Constraint node from entryconstraints */
+ entryconstraints =
+ foreach_delete_current(entryconstraints, colconstr);
+
+ /*
+ * Immediately-following attribute constraints should
+ * be dropped, too.
+ */
+ afterFK = true;
+ break;
+
+ /*
+ * Column constraint lists separate a Constraint node
+ * from its attributes (e.g. NOT ENFORCED); so a
+ * column-level foreign key constraint may be
+ * represented by multiple Constraint nodes. After
+ * transformConstraintAttrs, the foreign key
+ * Constraint node contains all required information,
+ * making it okay to put into *fk_elements as a
+ * stand-alone Constraint. But since we removed the
+ * foreign key Constraint node from entryconstraints,
+ * we must remove any dependent attribute nodes too,
+ * else the later re-execution of
+ * transformConstraintAttrs will misbehave.
+ */
+ case CONSTR_ATTR_DEFERRABLE:
+ case CONSTR_ATTR_NOT_DEFERRABLE:
+ case CONSTR_ATTR_DEFERRED:
+ case CONSTR_ATTR_IMMEDIATE:
+ case CONSTR_ATTR_ENFORCED:
+ case CONSTR_ATTR_NOT_ENFORCED:
+ if (afterFK)
+ entryconstraints =
+ foreach_delete_current(entryconstraints,
+ colconstr);
+ break;
+
+ default:
+ /* Any following constraint attributes are unrelated */
+ afterFK = false;
+ break;
+ }
+ }
+
+ /* Now make a modified ColumnDef to put into newElts */
+ newentry = makeNode(ColumnDef);
+ memcpy(newentry, entry, sizeof(ColumnDef));
+ newentry->constraints = entryconstraints;
+ newElts = lappend(newElts, newentry);
+ }
+ else
+ {
+ /* Other node types pass through unchanged */
+ newElts = lappend(newElts, element);
+ }
+ }
+
+ newstmt->tableElts = newElts;
+ return newstmt;
+}
+
/*
* transformPartitionCmd
* Analyze the ATTACH/DETACH/SPLIT PARTITION command
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 8ab4eb03385..6ed85ef7446 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -17,3 +17,28 @@ CREATE SCHEMA element_test
NOTICE: DDL test: type simple, tag CREATE SCHEMA
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE VIEW
+CREATE SCHEMA regress_schema_1
+CREATE TABLE t4(
+ b INT,
+ a INT REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
+ REFERENCES t6 DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE)
+CREATE TABLE t5 (a INT, b INT, PRIMARY KEY (a))
+CREATE TABLE t6 (a INT, b INT, PRIMARY KEY (a));
+NOTICE: DDL test: type simple, tag CREATE SCHEMA
+NOTICE: DDL test: type simple, tag CREATE TABLE
+NOTICE: DDL test: type simple, tag CREATE TABLE
+NOTICE: DDL test: type simple, tag CREATE INDEX
+NOTICE: DDL test: type simple, tag CREATE TABLE
+NOTICE: DDL test: type simple, tag CREATE INDEX
+NOTICE: DDL test: type alter table, tag ALTER TABLE
+NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint t4_a_fkey on table regress_schema_1.t4
+NOTICE: DDL test: type alter table, tag ALTER TABLE
+NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint t4_a_fkey1 on table regress_schema_1.t4
+NOTICE: DDL test: type alter table, tag ALTER TABLE
+NOTICE: subcommand: type ADD CONSTRAINT (and recurse) desc constraint fk on table regress_schema_1.t4
+DROP SCHEMA regress_schema_1 CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table regress_schema_1.t4
+drop cascades to table regress_schema_1.t5
+drop cascades to table regress_schema_1.t6
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 f314dc2b840..145aef2a75a 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -15,3 +15,14 @@ CREATE SCHEMA IF NOT EXISTS baz;
CREATE SCHEMA element_test
CREATE TABLE foo (id int)
CREATE VIEW bar AS SELECT * FROM foo;
+
+CREATE SCHEMA regress_schema_1
+CREATE TABLE t4(
+ b INT,
+ a INT REFERENCES t5 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
+ REFERENCES t6 DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE)
+CREATE TABLE t5 (a INT, b INT, PRIMARY KEY (a))
+CREATE TABLE t6 (a INT, b INT, PRIMARY KEY (a));
+
+DROP SCHEMA regress_schema_1 CASCADE;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index 4ab947a60a8..b34b9988962 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -131,5 +131,51 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
NOTICE: drop cascades to table regress_schema_1.tab
RESET ROLE;
+-- Test forward-referencing foreign key clauses.
+CREATE SCHEMA regress_schema_fk
+ CREATE TABLE regress_schema_fk.t2 (
+ b int,
+ a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
+ REFERENCES t3 DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 NOT DEFERRABLE)
+ CREATE TABLE regress_schema_fk.t1 (a int PRIMARY KEY)
+ CREATE TABLE t3 (a int PRIMARY KEY)
+ CREATE TABLE t4 (
+ b int,
+ a int REFERENCES t5 NOT DEFERRABLE ENFORCED
+ REFERENCES t6 DEFERRABLE INITIALLY IMMEDIATE,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED)
+ CREATE TABLE t5 (a int, b int, PRIMARY KEY (a))
+ CREATE TABLE t6 (a int, b int, PRIMARY KEY (a));
+\d regress_schema_fk.t2
+ Table "regress_schema_fk.t2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Foreign-key constraints:
+ "fk" FOREIGN KEY (a) REFERENCES regress_schema_fk.t1(a)
+ "t2_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_fk.t1(a) DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
+ "t2_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_fk.t3(a) DEFERRABLE INITIALLY DEFERRED
+
+\d regress_schema_fk.t4
+ Table "regress_schema_fk.t4"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Foreign-key constraints:
+ "fk" FOREIGN KEY (a) REFERENCES regress_schema_fk.t6(a) DEFERRABLE INITIALLY DEFERRED
+ "t4_a_fkey" FOREIGN KEY (a) REFERENCES regress_schema_fk.t5(a)
+ "t4_a_fkey1" FOREIGN KEY (a) REFERENCES regress_schema_fk.t6(a) DEFERRABLE
+
+DROP SCHEMA regress_schema_fk CASCADE;
+NOTICE: drop cascades to 6 other objects
+DETAIL: drop cascades to table regress_schema_fk.t2
+drop cascades to table regress_schema_fk.t1
+drop cascades to table regress_schema_fk.t3
+drop cascades to table regress_schema_fk.t4
+drop cascades to table regress_schema_fk.t5
+drop cascades to table regress_schema_fk.t6
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 4c32e1dcaf5..065f586310f 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -427,11 +427,11 @@ NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.one_col_c_seq
NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.two
-NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq
NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id
NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq
NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view
+NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two
-- View with column additions
CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id;
NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 62651342114..0f2accc59ec 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -71,5 +71,32 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
RESET ROLE;
+-- Test forward-referencing foreign key clauses.
+CREATE SCHEMA regress_schema_fk
+ CREATE TABLE regress_schema_fk.t2 (
+ b int,
+ a int REFERENCES t1 DEFERRABLE INITIALLY DEFERRED NOT ENFORCED
+ REFERENCES t3 DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 NOT DEFERRABLE)
+
+ CREATE TABLE regress_schema_fk.t1 (a int PRIMARY KEY)
+
+ CREATE TABLE t3 (a int PRIMARY KEY)
+
+ CREATE TABLE t4 (
+ b int,
+ a int REFERENCES t5 NOT DEFERRABLE ENFORCED
+ REFERENCES t6 DEFERRABLE INITIALLY IMMEDIATE,
+ CONSTRAINT fk FOREIGN KEY (a) REFERENCES t6 DEFERRABLE INITIALLY DEFERRED)
+
+ CREATE TABLE t5 (a int, b int, PRIMARY KEY (a))
+
+ CREATE TABLE t6 (a int, b int, PRIMARY KEY (a));
+
+\d regress_schema_fk.t2
+\d regress_schema_fk.t4
+
+DROP SCHEMA regress_schema_fk CASCADE;
+
-- Clean up
DROP ROLE regress_create_schema_role;
--
2.43.7
From 6e477e230c468f4f959388a3a7059ef9a79cce08 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 5 Apr 2026 17:57:10 -0400
Subject: [PATCH v13 3/3] Support more object types within CREATE SCHEMA.
Having rejected the principle that we should know how to re-order
the sub-commands of CREATE SCHEMA, there is not really anything
except a little coding to stop us from supporting more object types.
This patch adds support for creating functions (including procedures
and aggregates), operators, types (including domains), collations,
and text search objects.
SQL:2021 specifies that we should allow functions, procedures,
types, domains, and collations, so this moves us a great deal
closer to full SQL compatibility of CREATE SCHEMA. What remains
missing from their list are object types we don't have at all
(e.g. CREATE CHARACTER SET), casts, transforms, and roles.
Supporting casts or transforms would be problematic because
they don't have names at all, let alone schema-qualified names,
so it'd be quite a stretch to say that they belong to a schema.
Roles likewise are not schema-qualified, plus they are global
to a cluster, making it even less reasonable to consider them
as belonging to a schema. So I don't see us trying to complete
the list.
User-defined aggregates and operators are outside the spec's ken,
as are text search objects, so adding them does not do anything for
spec compatibility. But they go along with these other object types,
plus it takes no additional code to support them since they are
represented as DefineStmts like some variants of CREATE TYPE.
It would indeed take some effort to reject them.
Author: Kirill Reshke <reshkekirill@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/CALdSSPh4jUSDsWu3K58hjO60wnTRR0DuO4CKRcwa8EVuOSfXxg@mail.gmail.com
---
doc/src/sgml/ref/create_schema.sgml | 25 +++-
src/backend/parser/gram.y | 3 +
src/backend/parser/parse_utilcmd.c | 91 +++++++++++-
src/bin/psql/tab-complete.in.c | 22 +--
src/fe_utils/psqlscan.l | 22 +--
.../expected/create_schema.out | 38 ++++-
.../test_ddl_deparse/sql/create_schema.sql | 18 ++-
src/test/regress/expected/create_schema.out | 135 ++++++++++++++++++
src/test/regress/sql/create_schema.sql | 69 +++++++++
9 files changed, 398 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index 96bc496e777..4ecf82d6bcb 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -100,12 +100,27 @@ 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
- 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
+ schema. Currently, only
+ <command>CREATE AGGREGATE</command>,
+ <command>CREATE COLLATION</command>,
+ <command>CREATE DOMAIN</command>,
+ <command>CREATE FUNCTION</command>,
+ <command>CREATE INDEX</command>,
+ <command>CREATE OPERATOR</command>,
+ <command>CREATE PROCEDURE</command>,
+ <command>CREATE SEQUENCE</command>,
+ <command>CREATE TABLE</command>,
+ <command>CREATE TEXT SEARCH CONFIGURATION</command>,
+ <command>CREATE TEXT SEARCH DICTIONARY</command>,
+ <command>CREATE TEXT SEARCH PARSER</command>,
+ <command>CREATE TEXT SEARCH TEMPLATE</command>,
+ <command>CREATE TRIGGER</command>,
+ <command>CREATE TYPE</command>,
+ <command>CREATE VIEW</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.
+ be created within the schema in separate commands after the schema
+ is created.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f4a08baa95a..2d1b19d1f53 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -1679,8 +1679,11 @@ OptSchemaEltList:
schema_stmt:
CreateStmt
| IndexStmt
+ | CreateDomainStmt
+ | CreateFunctionStmt
| CreateSeqStmt
| CreateTrigStmt
+ | DefineStmt
| GrantStmt
| ViewStmt
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 1bc5df7e0ef..9348e594dab 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -127,6 +127,8 @@ static void transformConstraintAttrs(ParseState *pstate,
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
static void checkSchemaName(ParseState *pstate, const char *context_schema,
RangeVar *relation);
+static void checkSchemaNameList(const char *context_schema,
+ List *qualified_name);
static CreateStmt *transformCreateSchemaCreateTable(ParseState *pstate,
CreateStmt *stmt,
List **fk_elements);
@@ -4481,6 +4483,68 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
}
break;
+ case T_CreateDomainStmt:
+ {
+ CreateDomainStmt *elp = (CreateDomainStmt *) element;
+
+ checkSchemaNameList(schemaName, elp->domainname);
+ elements = lappend(elements, element);
+ }
+ break;
+
+ case T_CreateFunctionStmt:
+ {
+ CreateFunctionStmt *elp = (CreateFunctionStmt *) element;
+
+ checkSchemaNameList(schemaName, elp->funcname);
+ elements = lappend(elements, element);
+ }
+ break;
+
+ /*
+ * CREATE TYPE can produce a DefineStmt, but also
+ * CreateEnumStmt, CreateRangeStmt, and CompositeTypeStmt.
+ * Allowing DefineStmt also provides support for several other
+ * commands: currently, CREATE AGGREGATE, CREATE COLLATION,
+ * CREATE OPERATOR, and text search objects.
+ */
+
+ case T_DefineStmt:
+ {
+ DefineStmt *elp = (DefineStmt *) element;
+
+ checkSchemaNameList(schemaName, elp->defnames);
+ elements = lappend(elements, element);
+ }
+ break;
+
+ case T_CreateEnumStmt:
+ {
+ CreateEnumStmt *elp = (CreateEnumStmt *) element;
+
+ checkSchemaNameList(schemaName, elp->typeName);
+ elements = lappend(elements, element);
+ }
+ break;
+
+ case T_CreateRangeStmt:
+ {
+ CreateRangeStmt *elp = (CreateRangeStmt *) element;
+
+ checkSchemaNameList(schemaName, elp->typeName);
+ elements = lappend(elements, element);
+ }
+ break;
+
+ case T_CompositeTypeStmt:
+ {
+ CompositeTypeStmt *elp = (CompositeTypeStmt *) element;
+
+ checkSchemaName(pstate, schemaName, elp->typevar);
+ elements = lappend(elements, element);
+ }
+ break;
+
case T_GrantStmt:
elements = lappend(elements, element);
break;
@@ -4496,7 +4560,8 @@ transformCreateSchemaStmtElements(ParseState *pstate, List *schemaElts,
/*
* checkSchemaName
- * Check schema name in an element of a CREATE SCHEMA command
+ * Check schema name in an element of a CREATE SCHEMA command,
+ * where the element's name is given by a RangeVar
*
* It's okay if the command doesn't specify a target schema name, because
* CreateSchemaCommand will set up the default creation schema to be the
@@ -4527,6 +4592,30 @@ checkSchemaName(ParseState *pstate, const char *context_schema,
}
}
+/*
+ * checkSchemaNameList
+ * Check schema name in an element of a CREATE SCHEMA command,
+ * where the element's name is given by a List
+ *
+ * Much as above, but we don't have to worry about TEMP.
+ * Sadly, this also means we don't have a parse location to report.
+ */
+static void
+checkSchemaNameList(const char *context_schema, List *qualified_name)
+{
+ char *obj_schema;
+ char *obj_name;
+
+ DeconstructQualifiedName(qualified_name, &obj_schema, &obj_name);
+ if (obj_schema != NULL &&
+ strcmp(context_schema, obj_schema) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_SCHEMA_DEFINITION),
+ errmsg("CREATE specifies a schema (%s) "
+ "different from the one being created (%s)",
+ obj_schema, context_schema)));
+}
+
/*
* transformCreateSchemaCreateTable
* Process one CreateStmt for transformCreateSchemaStmtElements.
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 53bf1e21721..76d7edd4619 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2210,7 +2210,11 @@ match_previous_words(int pattern_id,
{
/* only some object types can be created as part of CREATE SCHEMA */
if (HeadMatches("CREATE", "SCHEMA"))
- COMPLETE_WITH("TABLE", "VIEW", "INDEX", "SEQUENCE", "TRIGGER",
+ COMPLETE_WITH("AGGREGATE", "COLLATION", "DOMAIN", "FUNCTION",
+ "INDEX", "OPERATOR", "PROCEDURE", "SEQUENCE", "TABLE",
+ "TEXT SEARCH CONFIGURATION", "TEXT SEARCH DICTIONARY",
+ "TEXT SEARCH PARSER", "TEXT SEARCH TEMPLATE",
+ "TRIGGER", "TYPE", "VIEW",
/* for INDEX and TABLE/SEQUENCE, respectively */
"UNIQUE", "UNLOGGED");
else
@@ -3534,15 +3538,15 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "DATABASE", MatchAny, "STRATEGY"))
COMPLETE_WITH("WAL_LOG", "FILE_COPY");
- /* CREATE DOMAIN */
- else if (Matches("CREATE", "DOMAIN", MatchAny))
+ /* CREATE DOMAIN --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny))
COMPLETE_WITH("AS");
- else if (Matches("CREATE", "DOMAIN", MatchAny, "AS"))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
- else if (Matches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
COMPLETE_WITH("COLLATE", "DEFAULT", "CONSTRAINT",
"NOT NULL", "NULL", "CHECK (");
- else if (Matches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
+ else if (TailMatches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations);
/* CREATE EXTENSION */
@@ -3906,10 +3910,10 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "TABLESPACE", MatchAny, "OWNER", MatchAny))
COMPLETE_WITH("LOCATION");
-/* CREATE TEXT SEARCH */
- else if (Matches("CREATE", "TEXT", "SEARCH"))
+/* CREATE TEXT SEARCH --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ else if (TailMatches("CREATE", "TEXT", "SEARCH"))
COMPLETE_WITH("CONFIGURATION", "DICTIONARY", "PARSER", "TEMPLATE");
- else if (Matches("CREATE", "TEXT", "SEARCH", "CONFIGURATION|DICTIONARY|PARSER|TEMPLATE", MatchAny))
+ else if (TailMatches("CREATE", "TEXT", "SEARCH", "CONFIGURATION|DICTIONARY|PARSER|TEMPLATE", MatchAny))
COMPLETE_WITH("(");
/* CREATE TRANSFORM */
diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l
index 7e1f9b22c49..d29dda4d8e1 100644
--- a/src/fe_utils/psqlscan.l
+++ b/src/fe_utils/psqlscan.l
@@ -928,19 +928,24 @@ other .
* writing a full parser here, the following heuristic
* should work. First, we track whether the beginning of
* the statement matches CREATE [OR REPLACE]
- * {FUNCTION|PROCEDURE}
+ * {FUNCTION|PROCEDURE|SCHEMA}. (Allowing this in
+ * CREATE SCHEMA, without tracking whether we're within a
+ * CREATE FUNCTION/PROCEDURE subcommand, is a bit shaky
+ * but should be okay with the present set of valid
+ * subcommands.)
*/
if (cur_state->identifier_count == 0)
memset(cur_state->identifiers, 0, sizeof(cur_state->identifiers));
- if (pg_strcasecmp(yytext, "create") == 0 ||
- pg_strcasecmp(yytext, "function") == 0 ||
- pg_strcasecmp(yytext, "procedure") == 0 ||
- pg_strcasecmp(yytext, "or") == 0 ||
- pg_strcasecmp(yytext, "replace") == 0)
+ if (cur_state->identifier_count < sizeof(cur_state->identifiers))
{
- if (cur_state->identifier_count < sizeof(cur_state->identifiers))
+ if (pg_strcasecmp(yytext, "create") == 0 ||
+ pg_strcasecmp(yytext, "function") == 0 ||
+ pg_strcasecmp(yytext, "procedure") == 0 ||
+ pg_strcasecmp(yytext, "or") == 0 ||
+ pg_strcasecmp(yytext, "replace") == 0 ||
+ pg_strcasecmp(yytext, "schema") == 0)
cur_state->identifiers[cur_state->identifier_count] = pg_tolower((unsigned char)
yytext[0]);
}
@@ -949,7 +954,8 @@ other .
if (cur_state->identifiers[0] == 'c' &&
(cur_state->identifiers[1] == 'f' || cur_state->identifiers[1] == 'p' ||
(cur_state->identifiers[1] == 'o' && cur_state->identifiers[2] == 'r' &&
- (cur_state->identifiers[3] == 'f' || cur_state->identifiers[3] == 'p'))) &&
+ (cur_state->identifiers[3] == 'f' || cur_state->identifiers[3] == 'p')) ||
+ cur_state->identifiers[1] == 's') &&
cur_state->paren_depth == 0)
{
if (pg_strcasecmp(yytext, "begin") == 0)
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 6ed85ef7446..a867786899b 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_schema.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out
@@ -13,10 +13,46 @@ CREATE SCHEMA IF NOT EXISTS baz;
NOTICE: schema "baz" already exists, skipping
CREATE SCHEMA element_test
CREATE TABLE foo (id int)
- CREATE VIEW bar AS SELECT * FROM foo;
+ CREATE VIEW bar AS SELECT * FROM foo
+ CREATE COLLATION coll (LOCALE="C")
+ CREATE DOMAIN d1 AS INT
+ CREATE FUNCTION et_add(int4, int4) RETURNS int4 LANGUAGE sql
+ AS 'SELECT $1 + $2'
+ CREATE PROCEDURE et_proc(int4, int4)
+ BEGIN ATOMIC SELECT et_add($1,$2); END
+ 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')
+ CREATE TEXT SEARCH PARSER et_ts_prs
+ (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end,
+ lextypes = prsd_lextype)
+;
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 COLLATION
+NOTICE: DDL test: type simple, tag CREATE DOMAIN
+NOTICE: DDL test: type simple, tag CREATE FUNCTION
+NOTICE: DDL test: type simple, tag CREATE PROCEDURE
+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
+NOTICE: DDL test: type simple, tag CREATE TEXT SEARCH PARSER
+DROP SCHEMA element_test CASCADE;
+NOTICE: drop cascades to 11 other objects
+DETAIL: drop cascades to table element_test.foo
+drop cascades to view element_test.bar
+drop cascades to collation element_test.coll
+drop cascades to type element_test.d1
+drop cascades to function element_test.et_add(integer,integer)
+drop cascades to function element_test.et_proc(integer,integer)
+drop cascades to type element_test.floatrange
+drop cascades to type element_test.ss
+drop cascades to type element_test.sss
+drop cascades to type element_test.rainbow
+drop cascades to text search parser element_test.et_ts_prs
CREATE SCHEMA regress_schema_1
CREATE TABLE t4(
b INT,
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 145aef2a75a..7ba641d06d6 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_schema.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql
@@ -14,7 +14,23 @@ CREATE SCHEMA IF NOT EXISTS baz;
CREATE SCHEMA element_test
CREATE TABLE foo (id int)
- CREATE VIEW bar AS SELECT * FROM foo;
+ CREATE VIEW bar AS SELECT * FROM foo
+ CREATE COLLATION coll (LOCALE="C")
+ CREATE DOMAIN d1 AS INT
+ CREATE FUNCTION et_add(int4, int4) RETURNS int4 LANGUAGE sql
+ AS 'SELECT $1 + $2'
+ CREATE PROCEDURE et_proc(int4, int4)
+ BEGIN ATOMIC SELECT et_add($1,$2); END
+ 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')
+ CREATE TEXT SEARCH PARSER et_ts_prs
+ (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end,
+ lextypes = prsd_lextype)
+;
+
+DROP SCHEMA element_test CASCADE;
CREATE SCHEMA regress_schema_1
CREATE TABLE t4(
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index b34b9988962..98dad2526a9 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -177,5 +177,140 @@ drop cascades to table regress_schema_fk.t3
drop cascades to table regress_schema_fk.t4
drop cascades to table regress_schema_fk.t5
drop cascades to table regress_schema_fk.t6
+-- Test miscellaneous object types within CREATE SCHEMA.
+CREATE SCHEMA regress_schema_misc
+ CREATE AGGREGATE cs_sum(int4)
+ (
+ SFUNC = int4_sum(int8, int4),
+ STYPE = int8,
+ INITCOND = '0'
+ )
+ CREATE COLLATION cs_builtin_c ( PROVIDER = builtin, LOCALE = "C" )
+ CREATE DOMAIN cs_positive AS integer CHECK (VALUE > 0)
+ CREATE FUNCTION cs_add(int4, int4) returns int4 language sql
+ as 'select $1 + $2'
+ CREATE OPERATOR + (function = cs_add, leftarg = int4, rightarg = int4)
+ CREATE PROCEDURE cs_proc(int4, int4)
+ BEGIN ATOMIC SELECT cs_add($1,$2); END
+ CREATE TEXT SEARCH CONFIGURATION cs_ts_conf (copy=english)
+ CREATE TEXT SEARCH DICTIONARY cs_ts_dict (template=simple)
+ CREATE TEXT SEARCH PARSER cs_ts_prs
+ (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end,
+ lextypes = prsd_lextype)
+ CREATE TEXT SEARCH TEMPLATE cs_ts_temp (lexize=dsimple_lexize)
+ CREATE TYPE regress_schema_misc.cs_enum AS ENUM ('red', 'orange')
+ CREATE TYPE cs_composite AS (a int, b float8)
+ CREATE TYPE cs_range AS RANGE (subtype = float8, subtype_diff = float8mi)
+ -- demonstrate creation of a base type with its I/O functions
+ CREATE TYPE cs_type
+ CREATE FUNCTION cs_type_in(cstring)
+ RETURNS cs_type LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
+ AS 'int4in'
+ CREATE FUNCTION cs_type_out(cs_type)
+ RETURNS cstring LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
+ AS 'int4out'
+ CREATE TYPE cs_type (
+ INPUT = cs_type_in,
+ OUTPUT = cs_type_out,
+ LIKE = int4
+ )
+;
+NOTICE: return type cs_type is only a shell
+NOTICE: argument type cs_type is only a shell
+LINE 29: CREATE FUNCTION cs_type_out(cs_type)
+ ^
+\df regress_schema_misc.cs_add
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+---------------------+--------+------------------+---------------------+------
+ regress_schema_misc | cs_add | integer | integer, integer | func
+(1 row)
+
+\df regress_schema_misc.cs_proc
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+---------------------+---------+------------------+------------------------+------
+ regress_schema_misc | cs_proc | | IN integer, IN integer | proc
+(1 row)
+
+\da regress_schema_misc.cs_sum
+ List of aggregate functions
+ Schema | Name | Result data type | Argument data types | Description
+---------------------+--------+------------------+---------------------+-------------
+ regress_schema_misc | cs_sum | bigint | integer |
+(1 row)
+
+\do regress_schema_misc.+
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+---------------------+------+---------------+----------------+-------------+-------------
+ regress_schema_misc | + | integer | integer | integer |
+(1 row)
+
+\dO regress_schema_misc.*
+ List of collations
+ Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic?
+---------------------+--------------+----------+---------+-------+--------+-----------+----------------
+ regress_schema_misc | cs_builtin_c | builtin | | | C | | yes
+(1 row)
+
+\dT regress_schema_misc.*
+ List of data types
+ Schema | Name | Description
+---------------------+-----------------------------------+-------------
+ regress_schema_misc | regress_schema_misc.cs_composite |
+ regress_schema_misc | regress_schema_misc.cs_enum |
+ regress_schema_misc | regress_schema_misc.cs_multirange |
+ regress_schema_misc | regress_schema_misc.cs_positive |
+ regress_schema_misc | regress_schema_misc.cs_range |
+ regress_schema_misc | regress_schema_misc.cs_type |
+(6 rows)
+
+\dF regress_schema_misc.*
+ List of text search configurations
+ Schema | Name | Description
+---------------------+------------+-------------
+ regress_schema_misc | cs_ts_conf |
+(1 row)
+
+\dFd regress_schema_misc.*
+ List of text search dictionaries
+ Schema | Name | Description
+---------------------+------------+-------------
+ regress_schema_misc | cs_ts_dict |
+(1 row)
+
+\dFp regress_schema_misc.*
+ List of text search parsers
+ Schema | Name | Description
+---------------------+-----------+-------------
+ regress_schema_misc | cs_ts_prs |
+(1 row)
+
+\dFt regress_schema_misc.*
+ List of text search templates
+ Schema | Name | Description
+---------------------+------------+-------------
+ regress_schema_misc | cs_ts_temp |
+(1 row)
+
+DROP SCHEMA regress_schema_misc CASCADE;
+NOTICE: drop cascades to 16 other objects
+DETAIL: drop cascades to function regress_schema_misc.cs_sum(integer)
+drop cascades to collation regress_schema_misc.cs_builtin_c
+drop cascades to type regress_schema_misc.cs_positive
+drop cascades to function regress_schema_misc.cs_add(integer,integer)
+drop cascades to operator regress_schema_misc.+(integer,integer)
+drop cascades to function regress_schema_misc.cs_proc(integer,integer)
+drop cascades to text search configuration regress_schema_misc.cs_ts_conf
+drop cascades to text search dictionary regress_schema_misc.cs_ts_dict
+drop cascades to text search parser regress_schema_misc.cs_ts_prs
+drop cascades to text search template regress_schema_misc.cs_ts_temp
+drop cascades to type regress_schema_misc.cs_enum
+drop cascades to type regress_schema_misc.cs_composite
+drop cascades to type regress_schema_misc.cs_range
+drop cascades to function regress_schema_misc.cs_type_out(regress_schema_misc.cs_type)
+drop cascades to type regress_schema_misc.cs_type
+drop cascades to function regress_schema_misc.cs_type_in(cstring)
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/create_schema.sql b/src/test/regress/sql/create_schema.sql
index 0f2accc59ec..08aebc8a5a1 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -98,5 +98,74 @@ CREATE SCHEMA regress_schema_fk
DROP SCHEMA regress_schema_fk CASCADE;
+-- Test miscellaneous object types within CREATE SCHEMA.
+CREATE SCHEMA regress_schema_misc
+ CREATE AGGREGATE cs_sum(int4)
+ (
+ SFUNC = int4_sum(int8, int4),
+ STYPE = int8,
+ INITCOND = '0'
+ )
+
+ CREATE COLLATION cs_builtin_c ( PROVIDER = builtin, LOCALE = "C" )
+
+ CREATE DOMAIN cs_positive AS integer CHECK (VALUE > 0)
+
+ CREATE FUNCTION cs_add(int4, int4) returns int4 language sql
+ as 'select $1 + $2'
+
+ CREATE OPERATOR + (function = cs_add, leftarg = int4, rightarg = int4)
+
+ CREATE PROCEDURE cs_proc(int4, int4)
+ BEGIN ATOMIC SELECT cs_add($1,$2); END
+
+ CREATE TEXT SEARCH CONFIGURATION cs_ts_conf (copy=english)
+
+ CREATE TEXT SEARCH DICTIONARY cs_ts_dict (template=simple)
+
+ CREATE TEXT SEARCH PARSER cs_ts_prs
+ (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end,
+ lextypes = prsd_lextype)
+
+ CREATE TEXT SEARCH TEMPLATE cs_ts_temp (lexize=dsimple_lexize)
+
+ CREATE TYPE regress_schema_misc.cs_enum AS ENUM ('red', 'orange')
+
+ CREATE TYPE cs_composite AS (a int, b float8)
+
+ CREATE TYPE cs_range AS RANGE (subtype = float8, subtype_diff = float8mi)
+
+ -- demonstrate creation of a base type with its I/O functions
+
+ CREATE TYPE cs_type
+
+ CREATE FUNCTION cs_type_in(cstring)
+ RETURNS cs_type LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
+ AS 'int4in'
+
+ CREATE FUNCTION cs_type_out(cs_type)
+ RETURNS cstring LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT
+ AS 'int4out'
+
+ CREATE TYPE cs_type (
+ INPUT = cs_type_in,
+ OUTPUT = cs_type_out,
+ LIKE = int4
+ )
+;
+
+\df regress_schema_misc.cs_add
+\df regress_schema_misc.cs_proc
+\da regress_schema_misc.cs_sum
+\do regress_schema_misc.+
+\dO regress_schema_misc.*
+\dT regress_schema_misc.*
+\dF regress_schema_misc.*
+\dFd regress_schema_misc.*
+\dFp regress_schema_misc.*
+\dFt regress_schema_misc.*
+
+DROP SCHEMA regress_schema_misc CASCADE;
+
-- Clean up
DROP ROLE regress_create_schema_role;
--
2.43.7
pgsql-hackers by date: