Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita0b012a

Browse files
committed
Rearrange ALTER TABLE syntax processing as per my recent proposal: the
grammar allows ALTER TABLE/INDEX/SEQUENCE/VIEW interchangeably for allsubforms of those commands, and then we sort out what's really legalat execution time. This allows the ALTER SEQUENCE/VIEW reference pagesto fully document all the ALTER forms available for sequences and viewsrespectively, and eliminates a longstanding cause of confusion for users.The net effect is that the following forms are allowed that weren't before:ALTER SEQUENCE OWNER TOALTER VIEW ALTER COLUMN SET/DROP DEFAULTALTER VIEW OWNER TOALTER VIEW SET SCHEMA(There's no actual functionality gain here, but formerly you had to sayALTER TABLE instead.)Interestingly, the grammar tables actually get smaller, probably becausethere are fewer special cases to keep track of.I did not disallow using ALTER TABLE for these operations. Perhaps weshould, but there's a backwards-compatibility issue if we do; in factit would break existing pg_dump scripts. I did however tighten upALTER SEQUENCE and ALTER VIEW to reject non-sequences and non-viewsin the new cases as well as a couple of cases where they didn't before.The patch doesn't change pg_dump to use the new syntaxes, either.
1 parentbd2ef87 commita0b012a

File tree

7 files changed

+235
-81
lines changed

7 files changed

+235
-81
lines changed

‎doc/src/sgml/ref/alter_sequence.sgml

Lines changed: 23 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.21 2008/05/17 01:20:39 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.22 2008/06/15 01:25:53 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -30,6 +30,7 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ B
3030
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
3131
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
3232
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
33+
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
3334
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
3435
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
3536
</synopsis>
@@ -48,6 +49,11 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
4849
You must own the sequence to use <command>ALTER SEQUENCE</>.
4950
To change a sequence's schema, you must also have <literal>CREATE</>
5051
privilege on the new schema.
52+
To alter the owner, you must also be a direct or indirect member of the new
53+
owning role, and that role must have <literal>CREATE</literal> privilege on
54+
the sequence's schema. (These restrictions enforce that altering the owner
55+
doesn't do anything you couldn't do by dropping and recreating the sequence.
56+
However, a superuser can alter ownership of any sequence anyway.)
5157
</para>
5258
</refsect1>
5359

@@ -205,6 +211,15 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
205211
</listitem>
206212
</varlistentry>
207213

214+
<varlistentry>
215+
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
216+
<listitem>
217+
<para>
218+
The user name of the new owner of the sequence.
219+
</para>
220+
</listitem>
221+
</varlistentry>
222+
208223
<varlistentry>
209224
<term><replaceable class="parameter">new_name</replaceable></term>
210225
<listitem>
@@ -233,9 +248,9 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
233248
<para>
234249
To avoid blocking of concurrent transactions that obtain numbers from the
235250
same sequence, <command>ALTER SEQUENCE</command>'s effects on the sequence
236-
generation parameters are never rolled back;
237-
those changes take effectimmediately and are not reversible. However,
238-
the<literal>OWNED BY</>, <literal>RENAME</>, and <literal>SET SCHEMA</>
251+
generation parameters are never rolled back; those changes take effect
252+
immediately and are not reversible. However, the <literal>OWNED BY</>,
253+
<literal>OWNER TO</>, <literal>RENAME TO</>, and <literal>SET SCHEMA</>
239254
clauses cause ordinary catalog updates that can be rolled back.
240255
</para>
241256

@@ -255,9 +270,9 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
255270
</para>
256271

257272
<para>
258-
Some variants of <command>ALTER TABLE</command> can be used with
259-
sequencesas well; for example, to rename a sequence it is also
260-
possibletouse <command>ALTER TABLE RENAME</command>.
273+
For historical reasons, <command>ALTER TABLE</command> can be used with
274+
sequencestoo; but the only variants of <command>ALTER TABLE</command>
275+
that are allowed with sequences are equivalenttothe forms shown above.
261276
</para>
262277
</refsect1>
263278

@@ -278,7 +293,7 @@ ALTER SEQUENCE serial RESTART WITH 105;
278293
<para>
279294
<command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
280295
standard, except for the <literal>START WITH</>,
281-
<literal>OWNED BY</>, <literal>RENAME</>, and
296+
<literal>OWNED BY</>, <literal>OWNER TO</>, <literal>RENAME TO</>, and
282297
<literal>SET SCHEMA</literal> clauses, which are
283298
<productname>PostgreSQL</productname> extensions.
284299
</para>

‎doc/src/sgml/ref/alter_view.sgml

Lines changed: 56 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_view.sgml,v 1.3 2007/10/03 16:48:43 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_view.sgml,v 1.4 2008/06/15 01:25:53 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,17 +20,32 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
ALTER VIEW <replaceable>name</replaceable> RENAME TO <replaceable>newname</replaceable>
23+
ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
24+
ALTER VIEW <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
25+
ALTER VIEW <replaceable class="parameter">name</replaceable> OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
26+
ALTER VIEW <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
27+
ALTER VIEW <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
2428
</synopsis>
2529
</refsynopsisdiv>
2630

2731
<refsect1>
2832
<title>Description</title>
2933

3034
<para>
31-
<command>ALTER VIEW</command> changes the definition of a view.
32-
The only currently available functionality is to rename the view.
33-
To execute this command you must be the owner of the view.
35+
<command>ALTER VIEW</command> changes various auxiliary properties
36+
of a view. (If you want to modify the view's defining query,
37+
use <command>CREATE OR REPLACE VIEW</>.)
38+
</para>
39+
40+
<para>
41+
You must own the view to use <command>ALTER VIEW</>.
42+
To change a view's schema, you must also have <literal>CREATE</>
43+
privilege on the new schema.
44+
To alter the owner, you must also be a direct or indirect member of the new
45+
owning role, and that role must have <literal>CREATE</literal> privilege on
46+
the view's schema. (These restrictions enforce that altering the owner
47+
doesn't do anything you couldn't do by dropping and recreating the view.
48+
However, a superuser can alter ownership of any view anyway.)
3449
</para>
3550
</refsect1>
3651

@@ -48,10 +63,41 @@ ALTER VIEW <replaceable>name</replaceable> RENAME TO <replaceable>newname</repla
4863
</varlistentry>
4964

5065
<varlistentry>
51-
<term><replaceable class="parameter">newname</replaceable></term>
66+
<term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
67+
<listitem>
68+
<para>
69+
These forms set or remove the default value for a column.
70+
A default value associated with a view column is
71+
inserted into <command>INSERT</> statements on the view before
72+
the view's <literal>ON INSERT</literal> rule is applied, if
73+
the <command>INSERT</> does not specify a value for the column.
74+
</para>
75+
</listitem>
76+
</varlistentry>
77+
78+
<varlistentry>
79+
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
80+
<listitem>
81+
<para>
82+
The user name of the new owner of the view.
83+
</para>
84+
</listitem>
85+
</varlistentry>
86+
87+
<varlistentry>
88+
<term><replaceable class="parameter">new_name</replaceable></term>
89+
<listitem>
90+
<para>
91+
The new name for the view.
92+
</para>
93+
</listitem>
94+
</varlistentry>
95+
96+
<varlistentry>
97+
<term><replaceable class="parameter">new_schema</replaceable></term>
5298
<listitem>
5399
<para>
54-
The newname of the view.
100+
The newschema for the view.
55101
</para>
56102
</listitem>
57103
</varlistentry>
@@ -62,11 +108,9 @@ ALTER VIEW <replaceable>name</replaceable> RENAME TO <replaceable>newname</repla
62108
<title>Notes</title>
63109

64110
<para>
65-
Some variants of <command>ALTER TABLE</command> can be used with
66-
views as well; for example, to rename a view it is also
67-
possible to use <command>ALTER TABLE RENAME</command>. To change
68-
the schema or owner of a view, you currently must use <command>ALTER
69-
TABLE</>.
111+
For historical reasons, <command>ALTER TABLE</command> can be used with
112+
views too; but the only variants of <command>ALTER TABLE</command>
113+
that are allowed with views are equivalent to the ones shown above.
70114
</para>
71115
</refsect1>
72116

‎src/backend/commands/alter.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/alter.c,v 1.28 2008/03/19 18:38:30 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/alter.c,v 1.29 2008/06/15 01:25:53 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -185,8 +185,10 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt)
185185

186186
caseOBJECT_SEQUENCE:
187187
caseOBJECT_TABLE:
188+
caseOBJECT_VIEW:
188189
CheckRelationOwnership(stmt->relation, true);
189-
AlterTableNamespace(stmt->relation,stmt->newschema);
190+
AlterTableNamespace(stmt->relation,stmt->newschema,
191+
stmt->objectType);
190192
break;
191193

192194
caseOBJECT_TYPE:

‎src/backend/commands/tablecmds.c

Lines changed: 71 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.256 2008/06/14 18:04:33 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.257 2008/06/15 01:25:53 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2175,6 +2175,44 @@ AlterTable(AlterTableStmt *stmt)
21752175

21762176
CheckTableNotInUse(rel,"ALTER TABLE");
21772177

2178+
/* Check relation type against type specified in the ALTER command */
2179+
switch (stmt->relkind)
2180+
{
2181+
caseOBJECT_TABLE:
2182+
/*
2183+
* For mostly-historical reasons, we allow ALTER TABLE to apply
2184+
* to all relation types.
2185+
*/
2186+
break;
2187+
2188+
caseOBJECT_INDEX:
2189+
if (rel->rd_rel->relkind!=RELKIND_INDEX)
2190+
ereport(ERROR,
2191+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
2192+
errmsg("\"%s\" is not an index",
2193+
RelationGetRelationName(rel))));
2194+
break;
2195+
2196+
caseOBJECT_SEQUENCE:
2197+
if (rel->rd_rel->relkind!=RELKIND_SEQUENCE)
2198+
ereport(ERROR,
2199+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
2200+
errmsg("\"%s\" is not a sequence",
2201+
RelationGetRelationName(rel))));
2202+
break;
2203+
2204+
caseOBJECT_VIEW:
2205+
if (rel->rd_rel->relkind!=RELKIND_VIEW)
2206+
ereport(ERROR,
2207+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
2208+
errmsg("\"%s\" is not a view",
2209+
RelationGetRelationName(rel))));
2210+
break;
2211+
2212+
default:
2213+
elog(ERROR,"unrecognized object type: %d", (int)stmt->relkind);
2214+
}
2215+
21782216
ATController(rel,stmt->cmds,interpretInhOption(stmt->relation->inhOpt));
21792217
}
21802218

@@ -7191,7 +7229,8 @@ ATExecDropInherit(Relation rel, RangeVar *parent)
71917229
* Note: caller must have checked ownership of the relation already
71927230
*/
71937231
void
7194-
AlterTableNamespace(RangeVar*relation,constchar*newschema)
7232+
AlterTableNamespace(RangeVar*relation,constchar*newschema,
7233+
ObjectTypestmttype)
71957234
{
71967235
Relationrel;
71977236
Oidrelid;
@@ -7204,6 +7243,36 @@ AlterTableNamespace(RangeVar *relation, const char *newschema)
72047243
relid=RelationGetRelid(rel);
72057244
oldNspOid=RelationGetNamespace(rel);
72067245

7246+
/* Check relation type against type specified in the ALTER command */
7247+
switch (stmttype)
7248+
{
7249+
caseOBJECT_TABLE:
7250+
/*
7251+
* For mostly-historical reasons, we allow ALTER TABLE to apply
7252+
* to all relation types.
7253+
*/
7254+
break;
7255+
7256+
caseOBJECT_SEQUENCE:
7257+
if (rel->rd_rel->relkind!=RELKIND_SEQUENCE)
7258+
ereport(ERROR,
7259+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
7260+
errmsg("\"%s\" is not a sequence",
7261+
RelationGetRelationName(rel))));
7262+
break;
7263+
7264+
caseOBJECT_VIEW:
7265+
if (rel->rd_rel->relkind!=RELKIND_VIEW)
7266+
ereport(ERROR,
7267+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
7268+
errmsg("\"%s\" is not a view",
7269+
RelationGetRelationName(rel))));
7270+
break;
7271+
7272+
default:
7273+
elog(ERROR,"unrecognized object type: %d", (int)stmttype);
7274+
}
7275+
72077276
/* Can we change the schema of this tuple? */
72087277
switch (rel->rd_rel->relkind)
72097278
{

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp