patch: ALTER TABLE IF EXISTS
Started by Pavel Stehuleabout 14 years ago14 messages
Hello
this is relative simple patch that add possibility to skip noexisting
tables. It is necessary for silent cleaning when dump is loaded.
Regards
Pavel Stehule
Attachments:
alter_table_if_exists.difftext/x-patch; charset=US-ASCII; name=alter_table_if_exists.diffDownload
*** ./doc/src/sgml/ref/alter_table.sgml.orig 2011-12-01 22:47:20.000000000 +0100
--- ./doc/src/sgml/ref/alter_table.sgml 2012-01-02 13:59:16.390363069 +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>
*** ./src/backend/nodes/copyfuncs.c.orig 2011-12-01 22:47:20.000000000 +0100
--- ./src/backend/nodes/copyfuncs.c 2012-01-02 11:16:05.521002912 +0100
***************
*** 2517,2522 ****
--- 2517,2523 ----
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 2011-12-01 22:47:20.000000000 +0100
--- ./src/backend/nodes/equalfuncs.c 2012-01-02 11:16:33.871001059 +0100
***************
*** 1009,1014 ****
--- 1009,1015 ----
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 2011-12-01 22:47:20.000000000 +0100
--- ./src/backend/parser/gram.y 2012-01-02 13:53:13.001386815 +0100
***************
*** 1621,1626 ****
--- 1621,1636 ----
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
***************
*** 1629,1634 ****
--- 1639,1645 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_INDEX;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name alter_table_cmds
***************
*** 1637,1642 ****
--- 1648,1654 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_SEQUENCE;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name alter_table_cmds
***************
*** 1645,1650 ****
--- 1657,1663 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_VIEW;
+ n->missing_ok = false;
$$ = (Node *)n;
}
;
*** ./src/backend/parser/parse_utilcmd.c.orig 2011-12-01 22:47:20.000000000 +0100
--- ./src/backend/parser/parse_utilcmd.c 2012-01-02 13:47:11.937410429 +0100
***************
*** 2256,2262 ****
* 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);
--- 2256,2278 ----
* new commands we add after this must not upgrade the lock level
* requested here.
*/
!
! if (stmt->missing_ok)
! {
! rel = try_relation_openrv(stmt->relation, lockmode);
!
! /* leave when relation doesn't exists */
! if (rel == NULL)
! {
! ereport(NOTICE,
! (errcode(ERRCODE_DUPLICATE_TABLE),
! errmsg("table \"%s\" does not exists, 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 2011-12-01 22:47:20.000000000 +0100
--- ./src/include/nodes/parsenodes.h 2012-01-02 11:15:04.643006891 +0100
***************
*** 1169,1174 ****
--- 1169,1175 ----
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 2011-12-01 22:47:20.000000000 +0100
--- ./src/test/regress/expected/alter_table.out 2012-01-02 14:04:03.000000000 +0100
***************
*** 2002,2004 ****
--- 2002,2018 ----
x | integer |
y | numeric(8,2) |
+ --
+ -- IF EXISTS test
+ --
+ ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
+ NOTICE: table "tt8" does not exists, 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 2011-12-01 22:47:20.000000000 +0100
--- ./src/test/regress/sql/alter_table.sql 2012-01-02 14:03:34.319346213 +0100
***************
*** 1410,1412 ****
--- 1410,1421 ----
ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed table
ALTER TABLE tt7 NOT OF;
\d tt7
+
+ --
+ -- 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
Re: patch: ALTER TABLE IF EXISTS
On Mon, Jan 2, 2012 at 1:11 PM, Pavel Stehule <pavel.stehule@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
Re: patch: ALTER TABLE IF EXISTS
On 2 Leden 2012, 14:11, Pavel Stehule wrote:
Hello
this is relative simple patch that add possibility to skip noexisting
tables. It is necessary for silent cleaning when dump is loaded.
Just a curious question - what use case is solved by this? Under what
circumstances you get an ALTER TABLE without a preceding CREATE TABLE? I
can't think of such scenario ...
Or is this meant for scripts written manually so that it's possible to do
alter if the table already exists and create if it does not exist?
Tomas
Re: patch: ALTER TABLE IF EXISTS
Hello
2012/1/2 Tomas Vondra <tv@fuzzy.cz>:
On 2 Leden 2012, 14:11, Pavel Stehule wrote:
Hello
this is relative simple patch that add possibility to skip noexisting
tables. It is necessary for silent cleaning when dump is loaded.Just a curious question - what use case is solved by this? Under what
circumstances you get an ALTER TABLE without a preceding CREATE TABLE? I
can't think of such scenario ...Or is this meant for scripts written manually so that it's possible to do
alter if the table already exists and create if it does not exist?
this is necessary for "silent" cleaning in pg_dump
this is fragment of dump with -c option
ALTER TABLE ONLY public.b DROP CONSTRAINT b_b_fkey;
DROP INDEX public.a_a_idx;
ALTER TABLE ONLY public.a DROP CONSTRAINT a_pkey;
DROP TABLE public.b;
DROP TABLE public.a;
DROP EXTENSION plpgsql;
DROP SCHEMA public;
I am working on "silent cleaning" and I am able generate a sequence of
statements:
ALTER TABLE IF EXISTS ONLY public.b DROP CONSTRAINT b_b_fkey;
DROP INDEX IF EXISTS public.a_a_idx;
ALTER TABLE IF EXISTS ONLY public.a DROP CONSTRAINT a_pkey;
DROP TABLE IF EXISTS public.b;
DROP TABLE IF EXISTS public.a;
DROP EXTENSION IF EXISTS plpgsql;
DROP SCHEMA IF EXISTS public;
constraint b_b_fkey should be removed before dropping index a_a_idx
Now we have DROP .. IF EXISTS statements, but ALTER TABLE .. IF EXISTS missing
Regards
Pavel
Show quoted text
Tomas
Re: patch: ALTER TABLE IF EXISTS
Hello
here is updated patch
Regards
Pavel
2012/1/2 Simon Riggs <simon@2ndquadrant.com>:
Show quoted text
On Mon, Jan 2, 2012 at 1:11 PM, Pavel Stehule <pavel.stehule@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 changingOther than that, happy to apply as soon as you make those changes.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
alter_table_if_exists_01.patchtext/x-patch; charset=US-ASCII; name=alter_table_if_exists_01.patchDownload
*** ./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
Re: patch: ALTER TABLE IF EXISTS
On Mon, Jan 2, 2012 at 12:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
here is updated patch
I think the comments in parse_utilcmd.c probably need a bit of adjustment.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: patch: ALTER TABLE IF EXISTS
Hello
2012/1/3 Robert Haas <robertmhaas@gmail.com>:
On Mon, Jan 2, 2012 at 12:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
here is updated patch
I think the comments in parse_utilcmd.c probably need a bit of adjustment.
I don't see it - there is only one comment and it is adjusted with
"if" statement.
please, show it
Regards
Pavel
Show quoted text
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: patch: ALTER TABLE IF EXISTS
On Tue, Jan 3, 2012 at 10:38 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2012/1/3 Robert Haas <robertmhaas@gmail.com>:
On Mon, Jan 2, 2012 at 12:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
here is updated patch
I think the comments in parse_utilcmd.c probably need a bit of adjustment.
I don't see it - there is only one comment and it is adjusted with
"if" statement.please, show it
Well, basically, the comment preceding the part you altered say "the
lock level requested here", but "here" is getting spread out quite a
bit more with this code change. Maybe that doesn't matter.
However, on further examination, this is a pretty awkward way to write
the code. Why not something like this:
rel = relation_openrv_extended(stmt->relation, lockmode, stmt->missing_ok);
if (rel == NULL)
{
ereport(...);
return NIL;
}
Maybe the intent of sticking heap_openrv_extended() into the upper
branch of the if statement is to try to bounce relations that aren't
tables, but that's not actually what that function does (e.g. a
foreign table will slip through). And I think if we're going to have
IF EXISTS support for ALTER TABLE at all, we ought to have it for the
whole family of related statements: ALTER VIEW, ALTER SEQUENCE, ALTER
FOREIGN TABLE, etc., not just ALTER TABLE itself.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: patch: ALTER TABLE IF EXISTS
Hello
2012/1/3 Robert Haas <robertmhaas@gmail.com>:
On Tue, Jan 3, 2012 at 10:38 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2012/1/3 Robert Haas <robertmhaas@gmail.com>:
On Mon, Jan 2, 2012 at 12:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
here is updated patch
I think the comments in parse_utilcmd.c probably need a bit of adjustment.
I don't see it - there is only one comment and it is adjusted with
"if" statement.please, show it
Well, basically, the comment preceding the part you altered say "the
lock level requested here", but "here" is getting spread out quite a
bit more with this code change. Maybe that doesn't matter.However, on further examination, this is a pretty awkward way to write
the code. Why not something like this:rel = relation_openrv_extended(stmt->relation, lockmode, stmt->missing_ok);
if (rel == NULL)
{
ereport(...);
return NIL;
}Maybe the intent of sticking heap_openrv_extended() into the upper
branch of the if statement is to try to bounce relations that aren't
tables, but that's not actually what that function does (e.g. a
foreign table will slip through). And I think if we're going to have
IF EXISTS support for ALTER TABLE at all, we ought to have it for the
whole family of related statements: ALTER VIEW, ALTER SEQUENCE, ALTER
FOREIGN TABLE, etc., not just ALTER TABLE itself.
jup, we can continue in enhancing step by step.
I change a patch and now ALTER TABLE, ALTER INDEX, ALTER SEQUENCE and
ALTER VIEW has IF EXISTS clause
Regards
Pavel
Show quoted text
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
alter_table_if_exists_02.patchtext/x-patch; charset=US-ASCII; name=alter_table_if_exists_02.patchDownload
*** ./doc/src/sgml/ref/alter_index.sgml.orig 2012-01-02 17:01:00.000000000 +0100
--- ./doc/src/sgml/ref/alter_index.sgml 2012-01-03 19:45:24.210189185 +0100
***************
*** 21,30 ****
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
--- 21,30 ----
<refsynopsisdiv>
<synopsis>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
***************
*** 38,43 ****
--- 38,53 ----
<variablelist>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the index does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>RENAME</literal></term>
<listitem>
<para>
*** ./doc/src/sgml/ref/alter_sequence.sgml.orig 2012-01-02 17:01:00.000000000 +0100
--- ./doc/src/sgml/ref/alter_sequence.sgml 2012-01-03 18:44:14.397429013 +0100
***************
*** 23,37 ****
<refsynopsisdiv>
<synopsis>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
</synopsis>
</refsynopsisdiv>
--- 23,37 ----
<refsynopsisdiv>
<synopsis>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 71,76 ****
--- 71,86 ----
</varlistentry>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the sequence does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">increment</replaceable></term>
<listitem>
<para>
*** ./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>
*** ./doc/src/sgml/ref/alter_view.sgml.orig 2012-01-02 17:01:00.000000000 +0100
--- ./doc/src/sgml/ref/alter_view.sgml 2012-01-03 19:46:23.473185308 +0100
***************
*** 21,33 ****
<refsynopsisdiv>
<synopsis>
! ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
! ALTER VIEW <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
! ALTER VIEW <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
--- 21,33 ----
<refsynopsisdiv>
<synopsis>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
***************
*** 66,71 ****
--- 66,81 ----
</varlistentry>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the view does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
<listitem>
<para>
*** ./src/backend/commands/alter.c.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/backend/commands/alter.c 2012-01-03 20:00:08.007131425 +0100
***************
*** 193,199 ****
case OBJECT_VIEW:
case OBJECT_FOREIGN_TABLE:
AlterTableNamespace(stmt->relation, stmt->newschema,
! stmt->objectType, AccessExclusiveLock);
break;
case OBJECT_TSPARSER:
--- 193,200 ----
case OBJECT_VIEW:
case OBJECT_FOREIGN_TABLE:
AlterTableNamespace(stmt->relation, stmt->newschema,
! stmt->objectType, AccessExclusiveLock,
! stmt->missing_ok);
break;
case OBJECT_TSPARSER:
*** ./src/backend/commands/sequence.c.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/backend/commands/sequence.c 2012-01-03 20:27:50.235022797 +0100
***************
*** 425,431 ****
List *owned_by;
/* Open and lock sequence. */
! relid = RangeVarGetRelid(stmt->sequence, AccessShareLock, false);
init_sequence(relid, &elm, &seqrel);
/* allow ALTER to sequence owner only */
--- 425,439 ----
List *owned_by;
/* Open and lock sequence. */
! relid = RangeVarGetRelid(stmt->sequence, AccessShareLock, stmt->missing_ok);
! if (relid == InvalidOid)
! {
! ereport(NOTICE,
! (errmsg("relation \"%s\" does not exist, skipping",
! stmt->sequence->relname)));
! return;
! }
!
init_sequence(relid, &elm, &seqrel);
/* allow ALTER to sequence owner only */
*** ./src/backend/commands/tablecmds.c.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/backend/commands/tablecmds.c 2012-01-03 20:01:52.232124614 +0100
***************
*** 2409,2417 ****
* lock escalation.
*/
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! false, false,
RangeVarCallbackForRenameRelation,
(void *) stmt);
/* Do the work */
RenameRelationInternal(relid, stmt->newname);
--- 2409,2424 ----
* lock escalation.
*/
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! stmt->missing_ok, false,
RangeVarCallbackForRenameRelation,
(void *) stmt);
+ if (relid == InvalidOid)
+ {
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ stmt->relation->relname)));
+ return;
+ }
/* Do the work */
RenameRelationInternal(relid, stmt->newname);
***************
*** 9627,9633 ****
*/
void
AlterTableNamespace(RangeVar *relation, const char *newschema,
! ObjectType stmttype, LOCKMODE lockmode)
{
Relation rel;
Oid relid;
--- 9634,9641 ----
*/
void
AlterTableNamespace(RangeVar *relation, const char *newschema,
! ObjectType stmttype, LOCKMODE lockmode,
! bool missing_ok)
{
Relation rel;
Oid relid;
***************
*** 9635,9643 ****
Oid nspOid;
Relation classRel;
! relid = RangeVarGetRelidExtended(relation, lockmode, false, false,
RangeVarCallbackForAlterTableNamespace,
(void *) &stmttype);
rel = relation_open(relid, NoLock);
oldNspOid = RelationGetNamespace(rel);
--- 9643,9658 ----
Oid nspOid;
Relation classRel;
! relid = RangeVarGetRelidExtended(relation, lockmode, missing_ok, false,
RangeVarCallbackForAlterTableNamespace,
(void *) &stmttype);
+ if (relid == InvalidOid)
+ {
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ relation->relname)));
+ return;
+ }
rel = relation_open(relid, NoLock);
oldNspOid = RelationGetNamespace(rel);
*** ./src/backend/nodes/copyfuncs.c.orig 2012-01-02 17:01:00.373650482 +0100
--- ./src/backend/nodes/copyfuncs.c 2012-01-03 20:15:14.371072201 +0100
***************
*** 2540,2545 ****
--- 2540,2546 ----
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(cmds);
COPY_SCALAR_FIELD(relkind);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 2903,2908 ****
--- 2904,2910 ----
COPY_STRING_FIELD(subname);
COPY_STRING_FIELD(newname);
COPY_SCALAR_FIELD(behavior);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 2918,2923 ****
--- 2920,2926 ----
COPY_NODE_FIELD(objarg);
COPY_STRING_FIELD(addname);
COPY_STRING_FIELD(newschema);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 3221,3226 ****
--- 3224,3230 ----
COPY_NODE_FIELD(sequence);
COPY_NODE_FIELD(options);
+ 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-03 20:15:30.763071125 +0100
***************
*** 1010,1015 ****
--- 1010,1016 ----
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(cmds);
COMPARE_SCALAR_FIELD(relkind);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1309,1314 ****
--- 1310,1316 ----
COMPARE_STRING_FIELD(subname);
COMPARE_STRING_FIELD(newname);
COMPARE_SCALAR_FIELD(behavior);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1322,1327 ****
--- 1324,1330 ----
COMPARE_NODE_FIELD(objarg);
COMPARE_STRING_FIELD(addname);
COMPARE_STRING_FIELD(newschema);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1575,1580 ****
--- 1578,1584 ----
{
COMPARE_NODE_FIELD(sequence);
COMPARE_NODE_FIELD(options);
+ 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-03 20:28:28.330020308 +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,1662 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_INDEX;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_INDEX;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name alter_table_cmds
***************
*** 1645,1650 ****
--- 1665,1680 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_SEQUENCE;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_SEQUENCE;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name alter_table_cmds
***************
*** 1653,1658 ****
--- 1683,1698 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_VIEW;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_VIEW;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
***************
*** 3076,3083 ****
--- 3116,3133 ----
AlterSeqStmt *n = makeNode(AlterSeqStmt);
n->sequence = $3;
n->options = $4;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name SeqOptList
+ {
+ AlterSeqStmt *n = makeNode(AlterSeqStmt);
+ n->sequence = $5;
+ n->options = $6;
+ n->missing_ok = true;
$$ = (Node *)n;
}
+
;
OptSeqOptList: SeqOptList { $$ = $1; }
***************
*** 6425,6430 ****
--- 6475,6481 ----
n->object = $3;
n->objarg = $4;
n->newname = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER COLLATION any_name RENAME TO name
***************
*** 6433,6438 ****
--- 6484,6490 ----
n->renameType = OBJECT_COLLATION;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER CONVERSION_P any_name RENAME TO name
***************
*** 6441,6446 ****
--- 6493,6499 ----
n->renameType = OBJECT_CONVERSION;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DATABASE database_name RENAME TO database_name
***************
*** 6449,6454 ****
--- 6502,6508 ----
n->renameType = OBJECT_DATABASE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DOMAIN_P any_name RENAME TO name
***************
*** 6457,6462 ****
--- 6511,6517 ----
n->renameType = OBJECT_DOMAIN;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FOREIGN DATA_P WRAPPER name RENAME TO name
***************
*** 6465,6470 ****
--- 6520,6526 ----
n->renameType = OBJECT_FDW;
n->subname = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FUNCTION function_with_argtypes RENAME TO name
***************
*** 6474,6479 ****
--- 6530,6536 ----
n->object = $3->funcname;
n->objarg = $3->funcargs;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER GROUP_P RoleId RENAME TO RoleId
***************
*** 6482,6487 ****
--- 6539,6545 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER opt_procedural LANGUAGE name RENAME TO name
***************
*** 6490,6495 ****
--- 6548,6554 ----
n->renameType = OBJECT_LANGUAGE;
n->subname = $4;
n->newname = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR CLASS any_name USING access_method RENAME TO name
***************
*** 6499,6504 ****
--- 6558,6564 ----
n->object = $4;
n->subname = $6;
n->newname = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR FAMILY any_name USING access_method RENAME TO name
***************
*** 6508,6513 ****
--- 6568,6574 ----
n->object = $4;
n->subname = $6;
n->newname = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SCHEMA name RENAME TO name
***************
*** 6516,6521 ****
--- 6577,6583 ----
n->renameType = OBJECT_SCHEMA;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SERVER name RENAME TO name
***************
*** 6524,6529 ****
--- 6586,6592 ----
n->renameType = OBJECT_FOREIGN_SERVER;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr RENAME TO name
***************
*** 6533,6538 ****
--- 6596,6612 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE IF_P EXISTS relation_expr RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_TABLE;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name RENAME TO name
***************
*** 6542,6547 ****
--- 6616,6632 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_SEQUENCE;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name RENAME TO name
***************
*** 6551,6556 ****
--- 6636,6652 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_VIEW;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER INDEX qualified_name RENAME TO name
***************
*** 6560,6565 ****
--- 6656,6672 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_INDEX;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr RENAME TO name
***************
*** 6569,6574 ****
--- 6676,6682 ----
n->relation = $4;
n->subname = NULL;
n->newname = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr RENAME opt_column name TO name
***************
*** 6579,6584 ****
--- 6687,6693 ----
n->relation = $3;
n->subname = $6;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr RENAME opt_column name TO name
***************
*** 6589,6594 ****
--- 6698,6704 ----
n->relation = $4;
n->subname = $7;
n->newname = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TRIGGER name ON qualified_name RENAME TO name
***************
*** 6598,6603 ****
--- 6708,6714 ----
n->relation = $5;
n->subname = $3;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER ROLE RoleId RENAME TO RoleId
***************
*** 6606,6611 ****
--- 6717,6723 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER USER RoleId RENAME TO RoleId
***************
*** 6614,6619 ****
--- 6726,6732 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLESPACE name RENAME TO name
***************
*** 6622,6627 ****
--- 6735,6741 ----
n->renameType = OBJECT_TABLESPACE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLESPACE name SET reloptions
***************
*** 6648,6653 ****
--- 6762,6768 ----
n->renameType = OBJECT_TSPARSER;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH DICTIONARY any_name RENAME TO name
***************
*** 6656,6661 ****
--- 6771,6777 ----
n->renameType = OBJECT_TSDICTIONARY;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH TEMPLATE any_name RENAME TO name
***************
*** 6664,6669 ****
--- 6780,6786 ----
n->renameType = OBJECT_TSTEMPLATE;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH CONFIGURATION any_name RENAME TO name
***************
*** 6672,6677 ****
--- 6789,6795 ----
n->renameType = OBJECT_TSCONFIGURATION;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name RENAME TO name
***************
*** 6680,6685 ****
--- 6798,6804 ----
n->renameType = OBJECT_TYPE;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name RENAME ATTRIBUTE name TO name opt_drop_behavior
***************
*** 6691,6696 ****
--- 6810,6816 ----
n->subname = $6;
n->newname = $8;
n->behavior = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
;
***************
*** 6717,6722 ****
--- 6837,6843 ----
n->object = $3;
n->objarg = $4;
n->newschema = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER COLLATION any_name SET SCHEMA name
***************
*** 6725,6730 ****
--- 6846,6852 ----
n->objectType = OBJECT_COLLATION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER CONVERSION_P any_name SET SCHEMA name
***************
*** 6733,6738 ****
--- 6855,6861 ----
n->objectType = OBJECT_CONVERSION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DOMAIN_P any_name SET SCHEMA name
***************
*** 6741,6746 ****
--- 6864,6870 ----
n->objectType = OBJECT_DOMAIN;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER EXTENSION any_name SET SCHEMA name
***************
*** 6749,6754 ****
--- 6873,6879 ----
n->objectType = OBJECT_EXTENSION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FUNCTION function_with_argtypes SET SCHEMA name
***************
*** 6758,6763 ****
--- 6883,6889 ----
n->object = $3->funcname;
n->objarg = $3->funcargs;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR any_operator oper_argtypes SET SCHEMA name
***************
*** 6767,6772 ****
--- 6893,6899 ----
n->object = $3;
n->objarg = $4;
n->newschema = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR CLASS any_name USING access_method SET SCHEMA name
***************
*** 6776,6781 ****
--- 6903,6909 ----
n->object = $4;
n->addname = $6;
n->newschema = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR FAMILY any_name USING access_method SET SCHEMA name
***************
*** 6785,6790 ****
--- 6913,6919 ----
n->object = $4;
n->addname = $6;
n->newschema = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr SET SCHEMA name
***************
*** 6793,6798 ****
--- 6922,6937 ----
n->objectType = OBJECT_TABLE;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE IF_P EXISTS relation_expr SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_TABLE;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH PARSER any_name SET SCHEMA name
***************
*** 6801,6806 ****
--- 6940,6946 ----
n->objectType = OBJECT_TSPARSER;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH DICTIONARY any_name SET SCHEMA name
***************
*** 6809,6814 ****
--- 6949,6955 ----
n->objectType = OBJECT_TSDICTIONARY;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH TEMPLATE any_name SET SCHEMA name
***************
*** 6817,6822 ****
--- 6958,6964 ----
n->objectType = OBJECT_TSTEMPLATE;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH CONFIGURATION any_name SET SCHEMA name
***************
*** 6825,6830 ****
--- 6967,6973 ----
n->objectType = OBJECT_TSCONFIGURATION;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name SET SCHEMA name
***************
*** 6833,6838 ****
--- 6976,6991 ----
n->objectType = OBJECT_SEQUENCE;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_SEQUENCE;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name SET SCHEMA name
***************
*** 6841,6846 ****
--- 6994,7009 ----
n->objectType = OBJECT_VIEW;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_VIEW;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr SET SCHEMA name
***************
*** 6849,6854 ****
--- 7012,7018 ----
n->objectType = OBJECT_FOREIGN_TABLE;
n->relation = $4;
n->newschema = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name SET SCHEMA name
***************
*** 6857,6862 ****
--- 7021,7027 ----
n->objectType = OBJECT_TYPE;
n->object = $3;
n->newschema = $6;
+ 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-03 18:42:36.189435428 +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,2299 ----
* new commands we add after this must not upgrade the lock level
* requested here.
*/
! rel = relation_openrv_extended(stmt->relation, lockmode, stmt->missing_ok);
! if (rel == NULL)
! {
! /* this message is consistent with relation_openrv */
! ereport(NOTICE,
! (errmsg("relation \"%s\" does not exist, skipping",
! stmt->relation->relname)));
! return NIL;
! }
/* Set up pstate and CreateStmtContext */
pstate = make_parsestate(NULL);
*** ./src/include/commands/tablecmds.h.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/include/commands/tablecmds.h 2012-01-03 20:02:37.791121635 +0100
***************
*** 33,39 ****
extern void AlterTableInternal(Oid relid, List *cmds, bool recurse);
extern void AlterTableNamespace(RangeVar *relation, const char *newschema,
! ObjectType stmttype, LOCKMODE lockmode);
extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid,
Oid oldNspOid, Oid newNspOid,
--- 33,40 ----
extern void AlterTableInternal(Oid relid, List *cmds, bool recurse);
extern void AlterTableNamespace(RangeVar *relation, const char *newschema,
! ObjectType stmttype, LOCKMODE lockmode,
! bool missing_ok);
extern void AlterRelationNamespaceInternal(Relation classRel, Oid relOid,
Oid oldNspOid, Oid newNspOid,
*** ./src/include/nodes/parsenodes.h.orig 2012-01-02 17:01:00.599650468 +0100
--- ./src/include/nodes/parsenodes.h 2012-01-03 20:15:59.041069245 +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
***************
*** 1806,1811 ****
--- 1807,1813 ----
NodeTag type;
RangeVar *sequence; /* the sequence to alter */
List *options;
+ bool missing_ok; /* skip error if a role is missing? */
} AlterSeqStmt;
/* ----------------------
***************
*** 2116,2121 ****
--- 2118,2124 ----
* trigger, etc) */
char *newname; /* the new name */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if missing? */
} RenameStmt;
/* ----------------------
***************
*** 2131,2136 ****
--- 2134,2140 ----
List *objarg; /* argument types, if applicable */
char *addname; /* additional name if needed */
char *newschema; /* the new schema */
+ bool missing_ok; /* skip error if missing? */
} AlterObjectSchemaStmt;
/* ----------------------
*** ./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-03 20:41:54.000000000 +0100
***************
*** 128,133 ****
--- 128,137 ----
DROP TABLE tmp_new2;
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
+ ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
+ NOTICE: relation "__onek_unique1" does not exist, skipping
+ ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
+ NOTICE: relation "__tmp_onek_unique1" does not exist, skipping
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
***************
*** 2130,2132 ****
--- 2134,2150 ----
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: relation "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/expected/sequence.out.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/test/regress/expected/sequence.out 2012-01-03 20:41:53.000000000 +0100
***************
*** 241,246 ****
--- 241,249 ----
--
-- Alter sequence
--
+ ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ NOTICE: relation "sequence_test2" does not exist, skipping
CREATE SEQUENCE sequence_test2 START WITH 32;
SELECT nextval('sequence_test2');
nextval
*** ./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-03 20:41:35.207968887 +0100
***************
*** 166,171 ****
--- 166,174 ----
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
+ ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
+ ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
+
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
***************
*** 1463,1465 ****
--- 1466,1478 ----
-- 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
*** ./src/test/regress/sql/sequence.sql.orig 2012-01-02 17:01:00.000000000 +0100
--- ./src/test/regress/sql/sequence.sql 2012-01-03 18:12:02.702555250 +0100
***************
*** 113,118 ****
--- 113,122 ----
--
-- Alter sequence
--
+
+ ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+
CREATE SEQUENCE sequence_test2 START WITH 32;
SELECT nextval('sequence_test2');
Re: patch: ALTER TABLE IF EXISTS
On Tue, Jan 3, 2012 at 7:49 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
I change a patch and now ALTER TABLE, ALTER INDEX, ALTER SEQUENCE and
ALTER VIEW has IF EXISTS clause
Patch no longer applies. Pls update.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Re: patch: ALTER TABLE IF EXISTS
On Tue, Jan 3, 2012 at 2:49 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
jup, we can continue in enhancing step by step.
I change a patch and now ALTER TABLE, ALTER INDEX, ALTER SEQUENCE and
ALTER VIEW has IF EXISTS clause
ALTER FOREIGN TABLE should be parallel as well, I think.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: patch: ALTER TABLE IF EXISTS
Hello
2012/1/23 Robert Haas <robertmhaas@gmail.com>:
On Tue, Jan 3, 2012 at 2:49 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
jup, we can continue in enhancing step by step.
I change a patch and now ALTER TABLE, ALTER INDEX, ALTER SEQUENCE and
ALTER VIEW has IF EXISTS clauseALTER FOREIGN TABLE should be parallel as well, I think.
refreshed + ALTER FOREIGN TABLE IF EXISTS ... support
Regards
Pavel
Show quoted text
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
alter_table_if_exists-120123-1.difftext/x-patch; charset=US-ASCII; name=alter_table_if_exists-120123-1.diffDownload
*** ./doc/src/sgml/ref/alter_foreign_table.sgml.orig 2012-01-23 17:29:50.000000000 +0100
--- ./doc/src/sgml/ref/alter_foreign_table.sgml 2012-01-23 20:12:21.228139425 +0100
***************
*** 21,33 ****
<refsynopsisdiv>
<synopsis>
! ALTER FOREIGN TABLE <replaceable class="PARAMETER">name</replaceable>
<replaceable class="PARAMETER">action</replaceable> [, ... ]
! ALTER FOREIGN TABLE <replaceable class="PARAMETER">name</replaceable>
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
! ALTER FOREIGN TABLE <replaceable class="PARAMETER">name</replaceable>
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER FOREIGN 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 FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
<replaceable class="PARAMETER">action</replaceable> [, ... ]
! ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
! ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER FOREIGN 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>
***************
*** 76,81 ****
--- 76,91 ----
</varlistentry>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the sequence does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SET DATA TYPE</literal></term>
<listitem>
<para>
*** ./doc/src/sgml/ref/alter_index.sgml.orig 2012-01-23 18:58:47.067427897 +0100
--- ./doc/src/sgml/ref/alter_index.sgml 2012-01-23 18:59:38.219424553 +0100
***************
*** 21,30 ****
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
--- 21,30 ----
<refsynopsisdiv>
<synopsis>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
! ALTER INDEX [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
***************
*** 38,43 ****
--- 38,53 ----
<variablelist>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the index does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>RENAME</literal></term>
<listitem>
<para>
*** ./doc/src/sgml/ref/alter_sequence.sgml.orig 2012-01-23 18:58:47.069427894 +0100
--- ./doc/src/sgml/ref/alter_sequence.sgml 2012-01-23 18:59:38.220424552 +0100
***************
*** 23,37 ****
<refsynopsisdiv>
<synopsis>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
</synopsis>
</refsynopsisdiv>
--- 23,37 ----
<refsynopsisdiv>
<synopsis>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 71,76 ****
--- 71,86 ----
</varlistentry>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the sequence does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">increment</replaceable></term>
<listitem>
<para>
*** ./doc/src/sgml/ref/alter_table.sgml.orig 2012-01-23 18:58:47.071427896 +0100
--- ./doc/src/sgml/ref/alter_table.sgml 2012-01-23 18:59:38.227424553 +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>
*** ./doc/src/sgml/ref/alter_view.sgml.orig 2012-01-23 18:58:47.072427897 +0100
--- ./doc/src/sgml/ref/alter_view.sgml 2012-01-23 18:59:38.228424553 +0100
***************
*** 21,33 ****
<refsynopsisdiv>
<synopsis>
! ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
! ALTER VIEW <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
! ALTER VIEW <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
! ALTER VIEW <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
--- 21,33 ----
<refsynopsisdiv>
<synopsis>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )
! ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">view_option_name</replaceable> [, ... ] )
</synopsis>
</refsynopsisdiv>
***************
*** 66,71 ****
--- 66,81 ----
</varlistentry>
<varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the view does not exist. A notice is issued
+ in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
<listitem>
<para>
*** ./src/backend/commands/sequence.c.orig 2012-01-23 18:58:47.075427897 +0100
--- ./src/backend/commands/sequence.c 2012-01-23 18:59:38.230424553 +0100
***************
*** 425,431 ****
List *owned_by;
/* Open and lock sequence. */
! relid = RangeVarGetRelid(stmt->sequence, AccessShareLock, false);
init_sequence(relid, &elm, &seqrel);
/* allow ALTER to sequence owner only */
--- 425,439 ----
List *owned_by;
/* Open and lock sequence. */
! relid = RangeVarGetRelid(stmt->sequence, AccessShareLock, stmt->missing_ok);
! if (relid == InvalidOid)
! {
! ereport(NOTICE,
! (errmsg("relation \"%s\" does not exist, skipping",
! stmt->sequence->relname)));
! return;
! }
!
init_sequence(relid, &elm, &seqrel);
/* allow ALTER to sequence owner only */
*** ./src/backend/commands/tablecmds.c.orig 2012-01-23 17:29:50.000000000 +0100
--- ./src/backend/commands/tablecmds.c 2012-01-23 20:54:00.738976086 +0100
***************
*** 2310,2318 ****
/* lock level taken here should match renameatt_internal */
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! false, false,
RangeVarCallbackForRenameAttribute,
NULL);
renameatt_internal(relid,
stmt->subname, /* old att name */
stmt->newname, /* new att name */
--- 2310,2327 ----
/* lock level taken here should match renameatt_internal */
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! stmt->missing_ok, false,
RangeVarCallbackForRenameAttribute,
NULL);
+
+ if (!OidIsValid(relid))
+ {
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ stmt->relation->relname)));
+ return;
+ }
+
renameatt_internal(relid,
stmt->subname, /* old att name */
stmt->newname, /* new att name */
***************
*** 2338,2347 ****
* lock escalation.
*/
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! false, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
/* Do the work */
RenameRelationInternal(relid, stmt->newname);
}
--- 2347,2364 ----
* lock escalation.
*/
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! stmt->missing_ok, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
+ if (!OidIsValid(relid))
+ {
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ stmt->relation->relname)));
+ return;
+ }
+
/* Do the work */
RenameRelationInternal(relid, stmt->newname);
}
***************
*** 2482,2488 ****
Oid
AlterTableLookupRelation(AlterTableStmt *stmt, LOCKMODE lockmode)
{
! return RangeVarGetRelidExtended(stmt->relation, lockmode, false, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
}
--- 2499,2505 ----
Oid
AlterTableLookupRelation(AlterTableStmt *stmt, LOCKMODE lockmode)
{
! return RangeVarGetRelidExtended(stmt->relation, lockmode, stmt->missing_ok, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
}
***************
*** 9434,9442 ****
RangeVar *newrv;
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! false, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
rel = relation_open(relid, NoLock);
oldNspOid = RelationGetNamespace(rel);
--- 9451,9468 ----
RangeVar *newrv;
relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
! stmt->missing_ok, false,
RangeVarCallbackForAlterRelation,
(void *) stmt);
+
+ if (!OidIsValid(relid))
+ {
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ stmt->relation->relname)));
+ return;
+ }
+
rel = relation_open(relid, NoLock);
oldNspOid = RelationGetNamespace(rel);
*** ./src/backend/nodes/copyfuncs.c.orig 2012-01-23 17:29:50.965776617 +0100
--- ./src/backend/nodes/copyfuncs.c 2012-01-23 18:59:38.237424551 +0100
***************
*** 2540,2545 ****
--- 2540,2546 ----
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(cmds);
COPY_SCALAR_FIELD(relkind);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 2904,2909 ****
--- 2905,2911 ----
COPY_STRING_FIELD(subname);
COPY_STRING_FIELD(newname);
COPY_SCALAR_FIELD(behavior);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 2919,2924 ****
--- 2921,2927 ----
COPY_NODE_FIELD(objarg);
COPY_STRING_FIELD(addname);
COPY_STRING_FIELD(newschema);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
***************
*** 3222,3227 ****
--- 3225,3231 ----
COPY_NODE_FIELD(sequence);
COPY_NODE_FIELD(options);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
*** ./src/backend/nodes/equalfuncs.c.orig 2012-01-23 17:29:50.966776617 +0100
--- ./src/backend/nodes/equalfuncs.c 2012-01-23 18:59:38.239424553 +0100
***************
*** 1010,1015 ****
--- 1010,1016 ----
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(cmds);
COMPARE_SCALAR_FIELD(relkind);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1310,1315 ****
--- 1311,1317 ----
COMPARE_STRING_FIELD(subname);
COMPARE_STRING_FIELD(newname);
COMPARE_SCALAR_FIELD(behavior);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1323,1328 ****
--- 1325,1331 ----
COMPARE_NODE_FIELD(objarg);
COMPARE_STRING_FIELD(addname);
COMPARE_STRING_FIELD(newschema);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
***************
*** 1576,1581 ****
--- 1579,1585 ----
{
COMPARE_NODE_FIELD(sequence);
COMPARE_NODE_FIELD(options);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
*** ./src/backend/parser/gram.y.orig 2012-01-23 17:29:50.982776616 +0100
--- ./src/backend/parser/gram.y 2012-01-23 20:45:09.883010773 +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,1662 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_INDEX;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_INDEX;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name alter_table_cmds
***************
*** 1645,1650 ****
--- 1665,1680 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_SEQUENCE;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_SEQUENCE;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name alter_table_cmds
***************
*** 1653,1658 ****
--- 1683,1698 ----
n->relation = $3;
n->cmds = $4;
n->relkind = OBJECT_VIEW;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_VIEW;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
***************
*** 3068,3075 ****
--- 3108,3125 ----
AlterSeqStmt *n = makeNode(AlterSeqStmt);
n->sequence = $3;
n->options = $4;
+ n->missing_ok = false;
$$ = (Node *)n;
}
+ | ALTER SEQUENCE IF_P EXISTS qualified_name SeqOptList
+ {
+ AlterSeqStmt *n = makeNode(AlterSeqStmt);
+ n->sequence = $5;
+ n->options = $6;
+ n->missing_ok = true;
+ $$ = (Node *)n;
+ }
+
;
OptSeqOptList: SeqOptList { $$ = $1; }
***************
*** 3906,3911 ****
--- 3956,3971 ----
n->relation = $4;
n->cmds = $5;
n->relkind = OBJECT_FOREIGN_TABLE;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER FOREIGN TABLE IF_P EXISTS relation_expr alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $6;
+ n->cmds = $7;
+ n->relkind = OBJECT_FOREIGN_TABLE;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
***************
*** 6417,6422 ****
--- 6477,6483 ----
n->object = $3;
n->objarg = $4;
n->newname = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER COLLATION any_name RENAME TO name
***************
*** 6425,6430 ****
--- 6486,6492 ----
n->renameType = OBJECT_COLLATION;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER CONVERSION_P any_name RENAME TO name
***************
*** 6433,6438 ****
--- 6495,6501 ----
n->renameType = OBJECT_CONVERSION;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DATABASE database_name RENAME TO database_name
***************
*** 6441,6446 ****
--- 6504,6510 ----
n->renameType = OBJECT_DATABASE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DOMAIN_P any_name RENAME TO name
***************
*** 6449,6454 ****
--- 6513,6519 ----
n->renameType = OBJECT_DOMAIN;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FOREIGN DATA_P WRAPPER name RENAME TO name
***************
*** 6457,6462 ****
--- 6522,6528 ----
n->renameType = OBJECT_FDW;
n->subname = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FUNCTION function_with_argtypes RENAME TO name
***************
*** 6466,6471 ****
--- 6532,6538 ----
n->object = $3->funcname;
n->objarg = $3->funcargs;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER GROUP_P RoleId RENAME TO RoleId
***************
*** 6474,6479 ****
--- 6541,6547 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER opt_procedural LANGUAGE name RENAME TO name
***************
*** 6482,6487 ****
--- 6550,6556 ----
n->renameType = OBJECT_LANGUAGE;
n->subname = $4;
n->newname = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR CLASS any_name USING access_method RENAME TO name
***************
*** 6491,6496 ****
--- 6560,6566 ----
n->object = $4;
n->subname = $6;
n->newname = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR FAMILY any_name USING access_method RENAME TO name
***************
*** 6500,6505 ****
--- 6570,6576 ----
n->object = $4;
n->subname = $6;
n->newname = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SCHEMA name RENAME TO name
***************
*** 6508,6513 ****
--- 6579,6585 ----
n->renameType = OBJECT_SCHEMA;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SERVER name RENAME TO name
***************
*** 6516,6521 ****
--- 6588,6594 ----
n->renameType = OBJECT_FOREIGN_SERVER;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr RENAME TO name
***************
*** 6525,6530 ****
--- 6598,6614 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE IF_P EXISTS relation_expr RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_TABLE;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name RENAME TO name
***************
*** 6534,6539 ****
--- 6618,6634 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_SEQUENCE;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name RENAME TO name
***************
*** 6543,6548 ****
--- 6638,6654 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_VIEW;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER INDEX qualified_name RENAME TO name
***************
*** 6552,6557 ****
--- 6658,6674 ----
n->relation = $3;
n->subname = NULL;
n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER INDEX IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_INDEX;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr RENAME TO name
***************
*** 6561,6566 ****
--- 6678,6694 ----
n->relation = $4;
n->subname = NULL;
n->newname = $7;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER FOREIGN TABLE IF_P EXISTS relation_expr RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_FOREIGN_TABLE;
+ n->relation = $6;
+ n->subname = NULL;
+ n->newname = $9;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr RENAME opt_column name TO name
***************
*** 6571,6576 ****
--- 6699,6716 ----
n->relation = $3;
n->subname = $6;
n->newname = $8;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE IF_P EXISTS relation_expr RENAME opt_column name TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_COLUMN;
+ n->relationType = OBJECT_TABLE;
+ n->relation = $5;
+ n->subname = $8;
+ n->newname = $10;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr RENAME opt_column name TO name
***************
*** 6581,6586 ****
--- 6721,6738 ----
n->relation = $4;
n->subname = $7;
n->newname = $9;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER FOREIGN TABLE IF_P EXISTS relation_expr RENAME opt_column name TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_COLUMN;
+ n->relationType = OBJECT_FOREIGN_TABLE;
+ n->relation = $6;
+ n->subname = $9;
+ n->newname = $11;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER TRIGGER name ON qualified_name RENAME TO name
***************
*** 6590,6595 ****
--- 6742,6748 ----
n->relation = $5;
n->subname = $3;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER ROLE RoleId RENAME TO RoleId
***************
*** 6598,6603 ****
--- 6751,6757 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER USER RoleId RENAME TO RoleId
***************
*** 6606,6611 ****
--- 6760,6766 ----
n->renameType = OBJECT_ROLE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLESPACE name RENAME TO name
***************
*** 6614,6619 ****
--- 6769,6775 ----
n->renameType = OBJECT_TABLESPACE;
n->subname = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLESPACE name SET reloptions
***************
*** 6640,6645 ****
--- 6796,6802 ----
n->renameType = OBJECT_TSPARSER;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH DICTIONARY any_name RENAME TO name
***************
*** 6648,6653 ****
--- 6805,6811 ----
n->renameType = OBJECT_TSDICTIONARY;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH TEMPLATE any_name RENAME TO name
***************
*** 6656,6661 ****
--- 6814,6820 ----
n->renameType = OBJECT_TSTEMPLATE;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH CONFIGURATION any_name RENAME TO name
***************
*** 6664,6669 ****
--- 6823,6829 ----
n->renameType = OBJECT_TSCONFIGURATION;
n->object = $5;
n->newname = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name RENAME TO name
***************
*** 6672,6677 ****
--- 6832,6838 ----
n->renameType = OBJECT_TYPE;
n->object = $3;
n->newname = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name RENAME ATTRIBUTE name TO name opt_drop_behavior
***************
*** 6683,6688 ****
--- 6844,6850 ----
n->subname = $6;
n->newname = $8;
n->behavior = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
;
***************
*** 6709,6714 ****
--- 6871,6877 ----
n->object = $3;
n->objarg = $4;
n->newschema = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER COLLATION any_name SET SCHEMA name
***************
*** 6717,6722 ****
--- 6880,6886 ----
n->objectType = OBJECT_COLLATION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER CONVERSION_P any_name SET SCHEMA name
***************
*** 6725,6730 ****
--- 6889,6895 ----
n->objectType = OBJECT_CONVERSION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER DOMAIN_P any_name SET SCHEMA name
***************
*** 6733,6738 ****
--- 6898,6904 ----
n->objectType = OBJECT_DOMAIN;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER EXTENSION any_name SET SCHEMA name
***************
*** 6741,6746 ****
--- 6907,6913 ----
n->objectType = OBJECT_EXTENSION;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER FUNCTION function_with_argtypes SET SCHEMA name
***************
*** 6750,6755 ****
--- 6917,6923 ----
n->object = $3->funcname;
n->objarg = $3->funcargs;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR any_operator oper_argtypes SET SCHEMA name
***************
*** 6759,6764 ****
--- 6927,6933 ----
n->object = $3;
n->objarg = $4;
n->newschema = $7;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR CLASS any_name USING access_method SET SCHEMA name
***************
*** 6768,6773 ****
--- 6937,6943 ----
n->object = $4;
n->addname = $6;
n->newschema = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER OPERATOR FAMILY any_name USING access_method SET SCHEMA name
***************
*** 6777,6782 ****
--- 6947,6953 ----
n->object = $4;
n->addname = $6;
n->newschema = $9;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TABLE relation_expr SET SCHEMA name
***************
*** 6785,6790 ****
--- 6956,6971 ----
n->objectType = OBJECT_TABLE;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE IF_P EXISTS relation_expr SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_TABLE;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH PARSER any_name SET SCHEMA name
***************
*** 6793,6798 ****
--- 6974,6980 ----
n->objectType = OBJECT_TSPARSER;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH DICTIONARY any_name SET SCHEMA name
***************
*** 6801,6806 ****
--- 6983,6989 ----
n->objectType = OBJECT_TSDICTIONARY;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH TEMPLATE any_name SET SCHEMA name
***************
*** 6809,6814 ****
--- 6992,6998 ----
n->objectType = OBJECT_TSTEMPLATE;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER TEXT_P SEARCH CONFIGURATION any_name SET SCHEMA name
***************
*** 6817,6822 ****
--- 7001,7007 ----
n->objectType = OBJECT_TSCONFIGURATION;
n->object = $5;
n->newschema = $8;
+ n->missing_ok = false;
$$ = (Node *)n;
}
| ALTER SEQUENCE qualified_name SET SCHEMA name
***************
*** 6825,6830 ****
--- 7010,7025 ----
n->objectType = OBJECT_SEQUENCE;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE IF_P EXISTS qualified_name SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_SEQUENCE;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER VIEW qualified_name SET SCHEMA name
***************
*** 6833,6838 ****
--- 7028,7043 ----
n->objectType = OBJECT_VIEW;
n->relation = $3;
n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VIEW IF_P EXISTS qualified_name SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_VIEW;
+ n->relation = $5;
+ n->newschema = $8;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER FOREIGN TABLE relation_expr SET SCHEMA name
***************
*** 6841,6846 ****
--- 7046,7061 ----
n->objectType = OBJECT_FOREIGN_TABLE;
n->relation = $4;
n->newschema = $7;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER FOREIGN TABLE IF_P EXISTS relation_expr SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+ n->objectType = OBJECT_FOREIGN_TABLE;
+ n->relation = $6;
+ n->newschema = $9;
+ n->missing_ok = true;
$$ = (Node *)n;
}
| ALTER TYPE_P any_name SET SCHEMA name
***************
*** 6849,6854 ****
--- 7064,7070 ----
n->objectType = OBJECT_TYPE;
n->object = $3;
n->newschema = $6;
+ n->missing_ok = false;
$$ = (Node *)n;
}
;
*** ./src/backend/parser/parse_utilcmd.c.orig 2012-01-23 17:29:50.986776614 +0100
--- ./src/backend/parser/parse_utilcmd.c 2012-01-23 18:59:38.247424549 +0100
***************
*** 2283,2289 ****
* 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);
--- 2283,2297 ----
* new commands we add after this must not upgrade the lock level
* requested here.
*/
! rel = relation_openrv_extended(stmt->relation, lockmode, stmt->missing_ok);
! if (rel == NULL)
! {
! /* this message is consistent with relation_openrv */
! ereport(NOTICE,
! (errmsg("relation \"%s\" does not exist, skipping",
! stmt->relation->relname)));
! return NIL;
! }
/* Set up pstate and CreateStmtContext */
pstate = make_parsestate(NULL);
*** ./src/backend/tcop/utility.c.orig 2012-01-23 17:29:51.000000000 +0100
--- ./src/backend/tcop/utility.c 2012-01-23 19:54:30.170209421 +0100
***************
*** 714,747 ****
lockmode = AlterTableGetLockLevel(atstmt->cmds);
relid = AlterTableLookupRelation(atstmt, lockmode);
! /* Run parse analysis ... */
! stmts = transformAlterTableStmt(atstmt, queryString);
!
! /* ... and do it */
! foreach(l, stmts)
{
! Node *stmt = (Node *) lfirst(l);
! if (IsA(stmt, AlterTableStmt))
{
! /* Do the table alteration proper */
! AlterTable(relid, lockmode, (AlterTableStmt *) stmt);
! }
! else
! {
! /* Recurse for anything else */
! ProcessUtility(stmt,
! queryString,
! params,
! false,
! None_Receiver,
! NULL);
! }
! /* Need CCI between commands */
! if (lnext(l) != NULL)
! CommandCounterIncrement();
}
}
break;
--- 714,754 ----
lockmode = AlterTableGetLockLevel(atstmt->cmds);
relid = AlterTableLookupRelation(atstmt, lockmode);
! if (OidIsValid(relid))
{
! /* Run parse analysis ... */
! stmts = transformAlterTableStmt(atstmt, queryString);
! /* ... and do it */
! foreach(l, stmts)
{
! Node *stmt = (Node *) lfirst(l);
!
! if (IsA(stmt, AlterTableStmt))
! {
! /* Do the table alteration proper */
! AlterTable(relid, lockmode, (AlterTableStmt *) stmt);
! }
! else
! {
! /* Recurse for anything else */
! ProcessUtility(stmt,
! queryString,
! params,
! false,
! None_Receiver,
! NULL);
! }
! /* Need CCI between commands */
! if (lnext(l) != NULL)
! CommandCounterIncrement();
! }
}
+ else
+ ereport(NOTICE,
+ (errmsg("relation \"%s\" does not exist, skipping",
+ atstmt->relation->relname)));
}
break;
*** ./src/include/nodes/parsenodes.h.orig 2012-01-23 17:29:51.182776603 +0100
--- ./src/include/nodes/parsenodes.h 2012-01-23 18:59:38.249424552 +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
***************
*** 1807,1812 ****
--- 1808,1814 ----
NodeTag type;
RangeVar *sequence; /* the sequence to alter */
List *options;
+ bool missing_ok; /* skip error if a role is missing? */
} AlterSeqStmt;
/* ----------------------
***************
*** 2117,2122 ****
--- 2119,2125 ----
* trigger, etc) */
char *newname; /* the new name */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if missing? */
} RenameStmt;
/* ----------------------
***************
*** 2132,2137 ****
--- 2135,2141 ----
List *objarg; /* argument types, if applicable */
char *addname; /* additional name if needed */
char *newschema; /* the new schema */
+ bool missing_ok; /* skip error if missing? */
} AlterObjectSchemaStmt;
/* ----------------------
*** ./src/test/regress/expected/alter_table.out.orig 2012-01-23 18:58:47.085427896 +0100
--- ./src/test/regress/expected/alter_table.out 2012-01-23 21:10:00.000000000 +0100
***************
*** 128,133 ****
--- 128,137 ----
DROP TABLE tmp_new2;
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
+ ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
+ NOTICE: relation "__onek_unique1" does not exist, skipping
+ ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
+ NOTICE: relation "__tmp_onek_unique1" does not exist, skipping
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
***************
*** 1185,1190 ****
--- 1189,1199 ----
-- these should work
alter table renameColumn rename column a to d;
alter table renameColumnChild rename column b to a;
+ -- these should work
+ alter table if exists doesnt_exist_tab rename column a to d;
+ NOTICE: relation "doesnt_exist_tab" does not exist, skipping
+ alter table if exists doesnt_exist_tab rename column b to a;
+ NOTICE: relation "doesnt_exist_tab" does not exist, skipping
-- this should work
alter table renameColumn add column w int;
-- this should fail
***************
*** 2130,2132 ****
--- 2139,2178 ----
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: relation "tt8" does not exist, skipping
+ ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+ NOTICE: relation "tt8" does not exist, skipping
+ ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+ NOTICE: relation "tt8" does not exist, skipping
+ ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+ NOTICE: relation "tt8" does not exist, skipping
+ ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+ NOTICE: relation "tt8" does not exist, skipping
+ ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+ NOTICE: relation "tt8" does not exist, skipping
+ CREATE TABLE tt8(a int);
+ CREATE SCHEMA alter2;
+ ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
+ ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+ NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "xxx" for table "tt8"
+ ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+ ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+ ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+ ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+ \d alter2.tt8
+ Table "alter2.tt8"
+ Column | Type | Modifiers
+ --------+---------+--------------------
+ a | integer |
+ f1 | integer | not null default 0
+ Indexes:
+ "xxx" PRIMARY KEY, btree (f1)
+ Check constraints:
+ "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10)
+
+ DROP TABLE alter2.tt8;
+ DROP SCHEMA alter2;
*** ./src/test/regress/expected/foreign_data.out.orig 2012-01-23 17:29:51.000000000 +0100
--- ./src/test/regress/expected/foreign_data.out 2012-01-23 20:55:43.000000000 +0100
***************
*** 789,794 ****
--- 789,838 ----
Server: s0
FDW Options: (quote '~', "be quoted" 'value', escape '@')
+ -- alter noexisting table
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10);
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1;
+ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
foreign_data_wrapper_catalog | foreign_data_wrapper_name | authorization_identifier | library_name | foreign_data_wrapper_language
*** ./src/test/regress/expected/sequence.out.orig 2012-01-23 18:58:47.086427896 +0100
--- ./src/test/regress/expected/sequence.out 2012-01-23 18:59:38.253424552 +0100
***************
*** 241,246 ****
--- 241,249 ----
--
-- Alter sequence
--
+ ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ NOTICE: relation "sequence_test2" does not exist, skipping
CREATE SEQUENCE sequence_test2 START WITH 32;
SELECT nextval('sequence_test2');
nextval
*** ./src/test/regress/sql/alter_table.sql.orig 2012-01-23 18:58:47.087427896 +0100
--- ./src/test/regress/sql/alter_table.sql 2012-01-23 21:09:40.917914639 +0100
***************
*** 166,171 ****
--- 166,174 ----
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
+ ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
+ ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
+
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
***************
*** 898,903 ****
--- 901,910 ----
alter table renameColumn rename column a to d;
alter table renameColumnChild rename column b to a;
+ -- these should work
+ alter table if exists doesnt_exist_tab rename column a to d;
+ alter table if exists doesnt_exist_tab rename column b to a;
+
-- this should work
alter table renameColumn add column w int;
***************
*** 1463,1465 ****
--- 1470,1497 ----
-- 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;
+ ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+ ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+ ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+ ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+ ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+
+ CREATE TABLE tt8(a int);
+ CREATE SCHEMA alter2;
+
+ ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
+ ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
+ ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
+ ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
+ ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
+ ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
+
+ \d alter2.tt8
+
+ DROP TABLE alter2.tt8;
+ DROP SCHEMA alter2;
*** ./src/test/regress/sql/foreign_data.sql.orig 2012-01-23 17:29:51.000000000 +0100
--- ./src/test/regress/sql/foreign_data.sql 2012-01-23 20:55:07.059971747 +0100
***************
*** 328,333 ****
--- 328,359 ----
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
\d foreign_schema.foreign_table_1
+ -- alter noexisting table
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c4 integer;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c6 integer;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c7 integer NOT NULL;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c8 integer;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c9 integer;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ADD COLUMN c10 integer OPTIONS (p1 'v1');
+
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c6 SET NOT NULL;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 DROP NOT NULL;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 TYPE char(10);
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c7 OPTIONS (ADD p1 'v1', ADD p2 'v2'),
+ ALTER COLUMN c8 OPTIONS (ADD p1 'v1', ADD p2 'v2');
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
+
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT IF EXISTS no_const;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP CONSTRAINT ft1_c1_check;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OWNER TO regress_test_role;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN IF EXISTS no_column;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 DROP COLUMN c9;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 SET SCHEMA foreign_schema;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME c1 TO foreign_column_1;
+ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1;
+
-- Information schema
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
*** ./src/test/regress/sql/sequence.sql.orig 2012-01-23 18:58:47.089427896 +0100
--- ./src/test/regress/sql/sequence.sql 2012-01-23 18:59:38.255424549 +0100
***************
*** 113,118 ****
--- 113,122 ----
--
-- Alter sequence
--
+
+ ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+
CREATE SEQUENCE sequence_test2 START WITH 32;
SELECT nextval('sequence_test2');
Re: patch: ALTER TABLE IF EXISTS
On 23 January 2012 20:14, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2012/1/23 Robert Haas <robertmhaas@gmail.com>:
On Tue, Jan 3, 2012 at 2:49 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
jup, we can continue in enhancing step by step.
I change a patch and now ALTER TABLE, ALTER INDEX, ALTER SEQUENCE and
ALTER VIEW has IF EXISTS clauseALTER FOREIGN TABLE should be parallel as well, I think.
refreshed + ALTER FOREIGN TABLE IF EXISTS ... support
I just noticed this copy-and-paste error in the ALTER FOREIGN TABLE docs:
IF EXISTS:
Do not throw an error if the sequence does not exist. A notice is issued
in this case.
That should be "foreign table" not "sequence".
Regards,
Dean
Re: patch: ALTER TABLE IF EXISTS
On 27.01.2012 11:57, Dean Rasheed wrote:
I just noticed this copy-and-paste error in the ALTER FOREIGN TABLE docs:
IF EXISTS:
Do not throw an error if the sequence does not exist. A notice is issued
in this case.That should be "foreign table" not "sequence".
Thanks, fixed.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com