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

Commite39b6f9

Browse files
committed
Add CINE option for CREATE TABLE AS and CREATE MATERIALIZED VIEW
Fabrízio de Royes Mello reviewed by Rushabh Lathia.
1 parente311cd6 commite39b6f9

File tree

11 files changed

+90
-2
lines changed

11 files changed

+90
-2
lines changed

‎doc/src/sgml/ref/create_materialized_view.sgml

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE MATERIALIZED VIEW <replaceable>table_name</replaceable>
24+
CREATE MATERIALIZED VIEW[ IF NOT EXISTS ]<replaceable>table_name</replaceable>
2525
[ (<replaceable>column_name</replaceable> [, ...] ) ]
2626
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
2727
[ TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable> ]
@@ -53,6 +53,18 @@ CREATE MATERIALIZED VIEW <replaceable>table_name</replaceable>
5353
<refsect1>
5454
<title>Parameters</title>
5555

56+
<varlistentry>
57+
<term><literal>IF NOT EXISTS</></term>
58+
<listitem>
59+
<para>
60+
Do not throw an error if a materialized view with the same name already
61+
exists. A notice is issued in this case. Note that there is no guarantee
62+
that the existing materialized view is anything like the one that would
63+
have been created.
64+
</para>
65+
</listitem>
66+
</varlistentry>
67+
5668
<variablelist>
5769
<varlistentry>
5870
<term><replaceable>table_name</replaceable></term>

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <replaceable>table_name</replaceable>
24+
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE[ IF NOT EXISTS ]<replaceable>table_name</replaceable>
2525
[ (<replaceable>column_name</replaceable> [, ...] ) ]
2626
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
2727
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
@@ -90,6 +90,17 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <replaceable
9090
</listitem>
9191
</varlistentry>
9292

93+
<varlistentry>
94+
<term><literal>IF NOT EXISTS</></term>
95+
<listitem>
96+
<para>
97+
Do not throw an error if a relation with the same name already exists.
98+
A notice is issued in this case. Refer to <xref linkend="sql-createtable">
99+
for details.
100+
</para>
101+
</listitem>
102+
</varlistentry>
103+
93104
<varlistentry>
94105
<term><replaceable>table_name</replaceable></term>
95106
<listitem>

‎src/backend/commands/createas.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@
2828
#include"access/sysattr.h"
2929
#include"access/xact.h"
3030
#include"access/xlog.h"
31+
#include"catalog/namespace.h"
3132
#include"catalog/toasting.h"
3233
#include"commands/createas.h"
3334
#include"commands/matview.h"
@@ -86,6 +87,22 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
8687
QueryDesc*queryDesc;
8788
ScanDirectiondir;
8889

90+
if (stmt->if_not_exists)
91+
{
92+
Oidnspid;
93+
94+
nspid=RangeVarGetCreationNamespace(stmt->into->rel);
95+
96+
if (get_relname_relid(stmt->into->rel->relname,nspid))
97+
{
98+
ereport(NOTICE,
99+
(errcode(ERRCODE_DUPLICATE_TABLE),
100+
errmsg("relation \"%s\" already exists, skipping",
101+
stmt->into->rel->relname)));
102+
returnInvalidOid;
103+
}
104+
}
105+
89106
/*
90107
* Create the tuple receiver object and insert info it will need
91108
*/

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3317,6 +3317,7 @@ _copyCreateTableAsStmt(const CreateTableAsStmt *from)
33173317
COPY_NODE_FIELD(into);
33183318
COPY_SCALAR_FIELD(relkind);
33193319
COPY_SCALAR_FIELD(is_select_into);
3320+
COPY_SCALAR_FIELD(if_not_exists);
33203321

33213322
returnnewnode;
33223323
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1530,6 +1530,7 @@ _equalCreateTableAsStmt(const CreateTableAsStmt *a, const CreateTableAsStmt *b)
15301530
COMPARE_NODE_FIELD(into);
15311531
COMPARE_SCALAR_FIELD(relkind);
15321532
COMPARE_SCALAR_FIELD(is_select_into);
1533+
COMPARE_SCALAR_FIELD(if_not_exists);
15331534

15341535
return true;
15351536
}

‎src/backend/parser/gram.y

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3401,11 +3401,25 @@ CreateAsStmt:
34013401
ctas->into =$4;
34023402
ctas->relkind = OBJECT_TABLE;
34033403
ctas->is_select_into =false;
3404+
ctas->if_not_exists =false;
34043405
/* cram additional flags into the IntoClause*/
34053406
$4->rel->relpersistence =$2;
34063407
$4->skipData = !($7);
34073408
$$ = (Node *) ctas;
34083409
}
3410+
|CREATEOptTempTABLEIF_PNOTEXISTScreate_as_targetASSelectStmtopt_with_data
3411+
{
3412+
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
3413+
ctas->query =$9;
3414+
ctas->into =$7;
3415+
ctas->relkind = OBJECT_TABLE;
3416+
ctas->is_select_into =false;
3417+
ctas->if_not_exists =true;
3418+
/* cram additional flags into the IntoClause*/
3419+
$7->rel->relpersistence =$2;
3420+
$7->skipData = !($10);
3421+
$$ = (Node *) ctas;
3422+
}
34093423
;
34103424

34113425
create_as_target:
@@ -3444,11 +3458,25 @@ CreateMatViewStmt:
34443458
ctas->into =$5;
34453459
ctas->relkind = OBJECT_MATVIEW;
34463460
ctas->is_select_into =false;
3461+
ctas->if_not_exists =false;
34473462
/* cram additional flags into the IntoClause*/
34483463
$5->rel->relpersistence =$2;
34493464
$5->skipData = !($8);
34503465
$$ = (Node *) ctas;
34513466
}
3467+
|CREATEOptNoLogMATERIALIZEDVIEWIF_PNOTEXISTScreate_mv_targetASSelectStmtopt_with_data
3468+
{
3469+
CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
3470+
ctas->query =$10;
3471+
ctas->into =$8;
3472+
ctas->relkind = OBJECT_MATVIEW;
3473+
ctas->is_select_into =false;
3474+
ctas->if_not_exists =true;
3475+
/* cram additional flags into the IntoClause*/
3476+
$8->rel->relpersistence =$2;
3477+
$8->skipData = !($11);
3478+
$$ = (Node *) ctas;
3479+
}
34523480
;
34533481

34543482
create_mv_target:

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2652,6 +2652,7 @@ typedef struct CreateTableAsStmt
26522652
IntoClause*into;/* destination table */
26532653
ObjectTyperelkind;/* OBJECT_TABLE or OBJECT_MATVIEW */
26542654
boolis_select_into;/* it was written as SELECT INTO */
2655+
boolif_not_exists;/* just do nothing if it already exists? */
26552656
}CreateTableAsStmt;
26562657

26572658
/* ----------------------

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -218,3 +218,9 @@ CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key);-- also OK
218218
CREATE TEMP TABLE public.temp_to_perm (a int primary key);-- not OK
219219
ERROR: cannot create temporary relation in non-temporary schema
220220
DROP TABLE unlogged1, public.unlogged2;
221+
CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
222+
CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
223+
ERROR: relation "as_select1" already exists
224+
CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r';
225+
NOTICE: relation "as_select1" already exists, skipping
226+
DROP TABLE as_select1;

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -508,6 +508,10 @@ SET ROLE user_dw;
508508
CREATE TABLE foo_data AS SELECT i, md5(random()::text)
509509
FROM generate_series(1, 10) i;
510510
CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
511+
CREATE MATERIALIZED VIEW mv_foo AS SELECT * FROM foo_data;
512+
ERROR: relation "mv_foo" already exists
513+
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_foo AS SELECT * FROM foo_data;
514+
NOTICE: relation "mv_foo" already exists, skipping
511515
CREATE UNIQUE INDEX ON mv_foo (i);
512516
RESET ROLE;
513517
REFRESH MATERIALIZED VIEW mv_foo;

‎src/test/regress/sql/create_table.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,3 +254,8 @@ CREATE TEMP TABLE explicitly_temp (a int primary key);-- also OK
254254
CREATE TEMP TABLEpg_temp.doubly_temp (aintprimary key);-- also OK
255255
CREATE TEMP TABLEpublic.temp_to_perm (aintprimary key);-- not OK
256256
DROPTABLE unlogged1,public.unlogged2;
257+
258+
CREATETABLEas_select1ASSELECT*FROM pg_classWHERE relkind='r';
259+
CREATETABLEas_select1ASSELECT*FROM pg_classWHERE relkind='r';
260+
CREATETABLEIF NOT EXISTS as_select1ASSELECT*FROM pg_classWHERE relkind='r';
261+
DROPTABLE as_select1;

‎src/test/regress/sql/matview.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,6 +201,8 @@ SET ROLE user_dw;
201201
CREATETABLEfoo_dataASSELECT i, md5(random()::text)
202202
FROM generate_series(1,10) i;
203203
CREATE MATERIALIZED VIEW mv_fooASSELECT*FROM foo_data;
204+
CREATE MATERIALIZED VIEW mv_fooASSELECT*FROM foo_data;
205+
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_fooASSELECT*FROM foo_data;
204206
CREATEUNIQUE INDEXON mv_foo (i);
205207
RESET ROLE;
206208
REFRESH MATERIALIZED VIEW mv_foo;

0 commit comments

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