Hi,
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.
Regards,
--
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..f48dbf8 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,22 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = interpretInhOption(rv->inhOpt);
Oid myrelid;
- rel = heap_openrv(rv, AccessExclusiveLock);
+ rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt->missing_ok);
+ /* TODO : if oid is not valid
+ * NOTICE with table name
+ * 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
