On 10/09/2012 11:09 AM, Simon Riggs wrote: > On 9 October 2012 09:33, Sébastien Lardière <slardi...@hi-media.com> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers