Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfb34e94

Browse files
committed
Support CREATE SCHEMA IF NOT EXISTS.
Per discussion, schema-element subcommands are not allowed together withthis option, since it's not very obvious what should happen to the elementobjects.Fabrízio de Royes Mello
1 parent994c36e commitfb34e94

File tree

9 files changed

+105
-0
lines changed

9 files changed

+105
-0
lines changed

‎doc/src/sgml/ref/create_schema.sgml

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@ PostgreSQL documentation
2323
<synopsis>
2424
CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
2525
CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">user_name</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
26+
CREATE SCHEMA IF NOT EXISTS <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ]
27+
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">user_name</replaceable>
2628
</synopsis>
2729
</refsynopsisdiv>
2830

@@ -98,6 +100,17 @@ CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">user_name</replaceabl
98100
</para>
99101
</listitem>
100102
</varlistentry>
103+
104+
<varlistentry>
105+
<term><literal>IF NOT EXISTS</literal></term>
106+
<listitem>
107+
<para>
108+
Do nothing (except issuing a notice) if a schema with the same name
109+
already exists. <replaceable class="parameter">schema_element</>
110+
subcommands cannot be included when this option is used.
111+
</para>
112+
</listitem>
113+
</varlistentry>
101114
</variablelist>
102115
</refsect1>
103116

@@ -129,6 +142,15 @@ CREATE SCHEMA AUTHORIZATION joe;
129142
</programlisting>
130143
</para>
131144

145+
<para>
146+
Create a schema named <literal>test</> that will be owned by user
147+
<literal>joe</>, unless there already is a schema named <literal>test</>.
148+
(It does not matter whether <literal>joe</> owns the pre-existing schema.)
149+
<programlisting>
150+
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
151+
</programlisting>
152+
</para>
153+
132154
<para>
133155
Create a schema and create a table and view within it:
134156
<programlisting>
@@ -177,6 +199,11 @@ CREATE VIEW hollywood.winners AS
177199
schema owner. This can happen only if the schema owner grants the
178200
<literal>CREATE</> privilege on his schema to someone else.
179201
</para>
202+
203+
<para>
204+
The <literal>IF NOT EXISTS</literal> option is a
205+
<productname>PostgreSQL</productname> extension.
206+
</para>
180207
</refsect1>
181208

182209
<refsect1>

‎src/backend/commands/extension.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1376,6 +1376,7 @@ CreateExtension(CreateExtensionStmt *stmt)
13761376
csstmt->schemaname=schemaName;
13771377
csstmt->authid=NULL;/* will be created by current user */
13781378
csstmt->schemaElts=NIL;
1379+
csstmt->if_not_exists= false;
13791380
CreateSchemaCommand(csstmt,NULL);
13801381

13811382
/*

‎src/backend/commands/schemacmds.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,23 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString)
8383
errmsg("unacceptable schema name \"%s\"",schemaName),
8484
errdetail("The prefix \"pg_\" is reserved for system schemas.")));
8585

86+
/*
87+
* If if_not_exists was given and the schema already exists, bail out.
88+
* (Note: we needn't check this when not if_not_exists, because
89+
* NamespaceCreate will complain anyway.) We could do this before making
90+
* the permissions checks, but since CREATE TABLE IF NOT EXISTS makes its
91+
* creation-permission check first, we do likewise.
92+
*/
93+
if (stmt->if_not_exists&&
94+
SearchSysCacheExists1(NAMESPACENAME,PointerGetDatum(schemaName)))
95+
{
96+
ereport(NOTICE,
97+
(errcode(ERRCODE_DUPLICATE_SCHEMA),
98+
errmsg("schema \"%s\" already exists, skipping",
99+
schemaName)));
100+
return;
101+
}
102+
86103
/*
87104
* If the requested authorization is different from the current user,
88105
* temporarily set the current user so that the object(s) will be created

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3613,6 +3613,7 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from)
36133613
COPY_STRING_FIELD(schemaname);
36143614
COPY_STRING_FIELD(authid);
36153615
COPY_NODE_FIELD(schemaElts);
3616+
COPY_SCALAR_FIELD(if_not_exists);
36163617

36173618
returnnewnode;
36183619
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1909,6 +1909,7 @@ _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b)
19091909
COMPARE_STRING_FIELD(schemaname);
19101910
COMPARE_STRING_FIELD(authid);
19111911
COMPARE_NODE_FIELD(schemaElts);
1912+
COMPARE_SCALAR_FIELD(if_not_exists);
19121913

19131914
return true;
19141915
}

‎src/backend/parser/gram.y

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1169,6 +1169,7 @@ CreateSchemaStmt:
11691169
n->schemaname =$5;
11701170
n->authid =$5;
11711171
n->schemaElts =$6;
1172+
n->if_not_exists =false;
11721173
$$ = (Node *)n;
11731174
}
11741175
|CREATESCHEMAColIdOptSchemaEltList
@@ -1178,6 +1179,40 @@ CreateSchemaStmt:
11781179
n->schemaname =$3;
11791180
n->authid =NULL;
11801181
n->schemaElts =$4;
1182+
n->if_not_exists =false;
1183+
$$ = (Node *)n;
1184+
}
1185+
|CREATESCHEMAIF_PNOTEXISTSOptSchemaNameAUTHORIZATIONRoleIdOptSchemaEltList
1186+
{
1187+
CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
1188+
/* One can omit the schema name or the authorization id.*/
1189+
if ($6 !=NULL)
1190+
n->schemaname =$6;
1191+
else
1192+
n->schemaname =$8;
1193+
n->authid =$8;
1194+
if ($9 != NIL)
1195+
ereport(ERROR,
1196+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1197+
errmsg("CREATE SCHEMA IF NOT EXISTS cannot include schema elements"),
1198+
parser_errposition(@9)));
1199+
n->schemaElts =$9;
1200+
n->if_not_exists =true;
1201+
$$ = (Node *)n;
1202+
}
1203+
|CREATESCHEMAIF_PNOTEXISTSColIdOptSchemaEltList
1204+
{
1205+
CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
1206+
/* ...but not both*/
1207+
n->schemaname =$6;
1208+
n->authid =NULL;
1209+
if ($7 != NIL)
1210+
ereport(ERROR,
1211+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1212+
errmsg("CREATE SCHEMA IF NOT EXISTS cannot include schema elements"),
1213+
parser_errposition(@7)));
1214+
n->schemaElts =$7;
1215+
n->if_not_exists =true;
11811216
$$ = (Node *)n;
11821217
}
11831218
;

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1157,6 +1157,7 @@ typedef struct CreateSchemaStmt
11571157
char*schemaname;/* the name of the schema to create */
11581158
char*authid;/* the owner of the created schema */
11591159
List*schemaElts;/* schema components (list of parsenodes) */
1160+
boolif_not_exists;/* just do nothing if schema already exists? */
11601161
}CreateSchemaStmt;
11611162

11621163
typedefenumDropBehavior

‎src/test/regress/expected/namespace.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,19 @@ SELECT * FROM test_schema_1.abc_view;
3636
4 |
3737
(3 rows)
3838

39+
-- test IF NOT EXISTS cases
40+
CREATE SCHEMA test_schema_1; -- fail, already exists
41+
ERROR: schema "test_schema_1" already exists
42+
CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice
43+
NOTICE: schema "test_schema_1" already exists, skipping
44+
CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed
45+
CREATE TABLE abc (
46+
a serial,
47+
b int UNIQUE
48+
);
49+
ERROR: CREATE SCHEMA IF NOT EXISTS cannot include schema elements
50+
LINE 1: CREATE SCHEMA IF NOT EXISTS test_schema_1
51+
^
3952
DROP SCHEMA test_schema_1 CASCADE;
4053
NOTICE: drop cascades to 2 other objects
4154
DETAIL: drop cascades to table test_schema_1.abc

‎src/test/regress/sql/namespace.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,15 @@ INSERT INTO test_schema_1.abc DEFAULT VALUES;
2424
SELECT*FROMtest_schema_1.abc;
2525
SELECT*FROMtest_schema_1.abc_view;
2626

27+
-- test IF NOT EXISTS cases
28+
CREATESCHEMAtest_schema_1;-- fail, already exists
29+
CREATESCHEMAIF NOT EXISTS test_schema_1;-- ok with notice
30+
CREATESCHEMAIF NOT EXISTS test_schema_1-- fail, disallowed
31+
CREATETABLEabc (
32+
aserial,
33+
bint UNIQUE
34+
);
35+
2736
DROPSCHEMA test_schema_1 CASCADE;
2837

2938
-- verify that the objects were dropped

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp