Hello here is updated patch
Regards Pavel 2012/1/2 Simon Riggs <si...@2ndquadrant.com>: > On Mon, Jan 2, 2012 at 1:11 PM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > >> this is relative simple patch that add possibility to skip noexisting >> tables. It is necessary for silent cleaning when dump is loaded. > > Agreed, nice simple and uncontentious patch. > > All good apart from two minor things: > > * doc page needs to explain what IF EXISTS does, like DROP TABLE, e.g. > > IF EXISTS > Do not throw an error if the table does not exist. A notice is > issued in this case. > > * the error message is "does not exist" rather than "does not exists", > which means the code, a comment and a test need changing > > Other than that, happy to apply as soon as you make those changes. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
*** ./doc/src/sgml/ref/alter_table.sgml.orig 2012-01-02 17:04:00.295638725 +0100 --- ./doc/src/sgml/ref/alter_table.sgml 2012-01-02 17:52:24.153449002 +0100 *************** *** 21,33 **** <refsynopsisdiv> <synopsis> ! ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] <replaceable class="PARAMETER">action</replaceable> [, ... ] ! ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable> ! ALTER TABLE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> ! ALTER TABLE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable> <phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase> --- 21,33 ---- <refsynopsisdiv> <synopsis> ! ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] <replaceable class="PARAMETER">action</replaceable> [, ... ] ! ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable> ! ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> ! ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable> <phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase> *************** *** 110,115 **** --- 110,125 ---- </varlistentry> <varlistentry> + <term><literal>IF EXISTS</literal></term> + <listitem> + <para> + Do not throw an error if the table does not exist. A notice is issued + in this case. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>SET DATA TYPE</literal></term> <listitem> <para> *** ./src/backend/nodes/copyfuncs.c.orig 2012-01-02 17:01:00.373650482 +0100 --- ./src/backend/nodes/copyfuncs.c 2012-01-02 17:04:12.472637933 +0100 *************** *** 2540,2545 **** --- 2540,2546 ---- COPY_NODE_FIELD(relation); COPY_NODE_FIELD(cmds); COPY_SCALAR_FIELD(relkind); + COPY_SCALAR_FIELD(missing_ok); return newnode; } *** ./src/backend/nodes/equalfuncs.c.orig 2012-01-02 17:01:00.374650482 +0100 --- ./src/backend/nodes/equalfuncs.c 2012-01-02 17:04:12.474637924 +0100 *************** *** 1010,1015 **** --- 1010,1016 ---- COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(cmds); COMPARE_SCALAR_FIELD(relkind); + COMPARE_SCALAR_FIELD(missing_ok); return true; } *** ./src/backend/parser/gram.y.orig 2012-01-02 17:01:00.391650482 +0100 --- ./src/backend/parser/gram.y 2012-01-02 17:04:12.517637925 +0100 *************** *** 1629,1634 **** --- 1629,1644 ---- n->relation = $3; n->cmds = $4; n->relkind = OBJECT_TABLE; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER TABLE IF_P EXISTS relation_expr alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $5; + n->cmds = $6; + n->relkind = OBJECT_TABLE; + n->missing_ok = true; $$ = (Node *)n; } | ALTER INDEX qualified_name alter_table_cmds *************** *** 1637,1642 **** --- 1647,1653 ---- n->relation = $3; n->cmds = $4; n->relkind = OBJECT_INDEX; + n->missing_ok = false; $$ = (Node *)n; } | ALTER SEQUENCE qualified_name alter_table_cmds *************** *** 1645,1650 **** --- 1656,1662 ---- n->relation = $3; n->cmds = $4; n->relkind = OBJECT_SEQUENCE; + n->missing_ok = false; $$ = (Node *)n; } | ALTER VIEW qualified_name alter_table_cmds *************** *** 1653,1658 **** --- 1665,1671 ---- n->relation = $3; n->cmds = $4; n->relkind = OBJECT_VIEW; + n->missing_ok = false; $$ = (Node *)n; } ; *** ./src/backend/parser/parse_utilcmd.c.orig 2012-01-02 17:01:00.395650481 +0100 --- ./src/backend/parser/parse_utilcmd.c 2012-01-02 17:38:11.043504706 +0100 *************** *** 2285,2291 **** * new commands we add after this must not upgrade the lock level * requested here. */ ! rel = relation_openrv(stmt->relation, lockmode); /* Set up pstate and CreateStmtContext */ pstate = make_parsestate(NULL); --- 2285,2306 ---- * new commands we add after this must not upgrade the lock level * requested here. */ ! ! if (stmt->missing_ok) ! { ! rel = heap_openrv_extended(stmt->relation, lockmode, true); ! ! /* leave when relation doesn't exist */ ! if (rel == NULL) ! { ! ereport(NOTICE, ! (errmsg("table \"%s\" does not exist, skipping", ! stmt->relation->relname))); ! return NIL; ! } ! } ! else ! rel = relation_openrv(stmt->relation, lockmode); /* Set up pstate and CreateStmtContext */ pstate = make_parsestate(NULL); *** ./src/include/nodes/parsenodes.h.orig 2012-01-02 17:01:00.599650468 +0100 --- ./src/include/nodes/parsenodes.h 2012-01-02 17:04:12.520637926 +0100 *************** *** 1171,1176 **** --- 1171,1177 ---- RangeVar *relation; /* table to work on */ List *cmds; /* list of subcommands */ ObjectType relkind; /* type of object */ + bool missing_ok; /* skip error if table missing */ } AlterTableStmt; typedef enum AlterTableType *** ./src/test/regress/expected/alter_table.out.orig 2012-01-02 17:01:00.000000000 +0100 --- ./src/test/regress/expected/alter_table.out 2012-01-02 17:39:33.000000000 +0100 *************** *** 2130,2132 **** --- 2130,2146 ---- DETAIL: Failing row contains (null). DROP TABLE test_drop_constr_parent CASCADE; NOTICE: drop cascades to table test_drop_constr_child + -- + -- IF EXISTS test + -- + ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; + NOTICE: table "tt8" does not exist, skipping + CREATE TABLE tt8(a int); + ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; + \d tt8 + Table "public.tt8" + Column | Type | Modifiers + --------+---------+----------- + a | integer | + f | integer | + *** ./src/test/regress/sql/alter_table.sql.orig 2012-01-02 17:01:00.000000000 +0100 --- ./src/test/regress/sql/alter_table.sql 2012-01-02 17:23:37.834561770 +0100 *************** *** 1463,1465 **** --- 1463,1475 ---- -- should fail INSERT INTO test_drop_constr_child (c) VALUES (NULL); DROP TABLE test_drop_constr_parent CASCADE; + + + -- + -- IF EXISTS test + -- + ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; + + CREATE TABLE tt8(a int); + ALTER TABLE IF EXISTS tt8 ADD COLUMN f int; + \d tt8
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers