On 10/09/2012 11:09 AM, Simon Riggs wrote:
> On 9 October 2012 09:33, Sébastien Lardière <[email protected]> wrote:
>
>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
>> command, adding the IF EXISTS option to allow the presence in the list
>> of tables of a missing or invisible table.
>>
>> This meets the needs of scripts that should be run in different stages,
>> and do not always have the same visibility on the tables, as well as
>> DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
>> prefer to ignore the absence of the table.
>>
>> It is a small patch which changes very little code, but that could be
>> quite useful.
> Agreed.
>
> Patch looks fine, but please observe the coding standards wrt nested brackets.
>
> Will apply in 48 hours barring objections.
>
> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>
Indeed, brackets was not correct, it's better now (I think), and correct
some comments.
Thanks,
--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media
diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index 7b9c2f3..8af9f0b 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ... ]
+TRUNCATE [ TABLE ] [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -44,6 +44,16 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [
<variablelist>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if a table does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
@@ -222,7 +232,8 @@ TRUNCATE othertable CASCADE;
also appear in that standard, but have slightly different though related
meanings. Some of the concurrency behavior of this command is left
implementation-defined by the standard, so the above notes should be
- considered and compared with other implementations if necessary.
+ considered and compared with other implementations if necessary. The
+ <literal>IF EXISTS</> option is a <productname>PostgreSQL</> extension.
</para>
</refsect1>
</refentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 359d478..cb08bac 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -948,6 +948,9 @@ ExecuteTruncate(TruncateStmt *stmt)
ResultRelInfo *resultRelInfo;
SubTransactionId mySubid;
ListCell *cell;
+ ListCell *prev;
+
+ prev = NULL;
/*
* Open, exclusive-lock, and check all the explicitly-specified relations
@@ -959,7 +962,23 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = interpretInhOption(rv->inhOpt);
Oid myrelid;
- rel = heap_openrv(rv, AccessExclusiveLock);
+ rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt->missing_ok);
+ /* if oid is not valid
+ * NOTICE with table name
+ * then invalid oid in the list
+ */
+ if(rel == NULL)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation %s does not exist", rv->relname )
+ )
+ );
+ list_delete_cell(stmt->relations, cell, prev) ;
+ continue;
+ }
+ prev = cell;
+
myrelid = RelationGetRelid(rel);
/* don't throw error for "TRUNCATE foo, foo" */
if (list_member_oid(relids, myrelid))
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e4ff76e..f6a03f3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5065,6 +5065,16 @@ TruncateStmt:
n->relations = $3;
n->restart_seqs = $4;
n->behavior = $5;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | TRUNCATE opt_table IF_P EXISTS relation_expr_list opt_restart_seqs opt_drop_behavior
+ {
+ TruncateStmt *n = makeNode(TruncateStmt);
+ n->relations = $5;
+ n->restart_seqs = $6;
+ n->behavior = $7;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 09b15e7..814b129 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1954,6 +1954,7 @@ typedef struct TruncateStmt
List *relations; /* relations (RangeVars) to be truncated */
bool restart_seqs; /* restart owned sequences? */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} TruncateStmt;
/* ----------------------
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 5c5277e..839104d 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -420,3 +420,13 @@ SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
ERROR: relation "truncate_a_id1" does not exist
LINE 1: SELECT nextval('truncate_a_id1');
^
+-- test IF EXISTS
+CREATE TABLE truncate_a(id int);
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a ;
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a, truncate_b ;
+NOTICE: relation truncate_b does not exist
+DROP TABLE truncate_a ;
+TRUNCATE TABLE IF EXISTS truncate_a ;
+NOTICE: relation truncate_a does not exist
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index a3d6f53..ed39360 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -215,3 +215,16 @@ SELECT * FROM truncate_a;
DROP TABLE truncate_a;
SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
+
+-- test IF EXISTS
+
+CREATE TABLE truncate_a(id int);
+
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a ;
+
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a, truncate_b ;
+
+DROP TABLE truncate_a ;
+TRUNCATE TABLE IF EXISTS truncate_a ;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
