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

Commitff1ea21

Browse files
committed
Allow CREATE OR REPLACE VIEW to add columns to the _end_ of the view.
Robert Haas
1 parent31076c8 commitff1ea21

File tree

7 files changed

+95
-47
lines changed

7 files changed

+95
-47
lines changed

‎doc/src/sgml/ref/create_view.sgml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.37 2008/11/14 10:22:46petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.38 2008/12/06 23:22:46momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -37,9 +37,10 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
3737

3838
<para>
3939
<command>CREATE OR REPLACE VIEW</command> is similar, but if a view
40-
of the same name already exists, it is replaced. You can only replace
41-
a view with a new query that generates the identical set of columns
42-
(i.e., same column names and data types).
40+
of the same name already exists, it is replaced. The new query must
41+
generate all of the same columns that were generated by the original query
42+
in the same order and with the same data types, but may add additional
43+
columns to the end of the list.
4344
</para>
4445

4546
<para>

‎src/backend/commands/tablecmds.c‎

Lines changed: 46 additions & 33 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.271 2008/11/19 10:34:51 heikki Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.272 2008/12/06 23:22:46 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2334,6 +2334,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
23342334
ATPrepAddColumn(wqueue,rel,recurse,cmd);
23352335
pass=AT_PASS_ADD_COL;
23362336
break;
2337+
caseAT_AddColumnToView:/* add column via CREATE OR REPLACE VIEW */
2338+
ATSimplePermissions(rel, true);
2339+
/* Performs own recursion */
2340+
ATPrepAddColumn(wqueue,rel,recurse,cmd);
2341+
pass=AT_PASS_ADD_COL;
2342+
break;
23372343
caseAT_ColumnDefault:/* ALTER COLUMN DEFAULT */
23382344

23392345
/*
@@ -2555,6 +2561,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
25552561
switch (cmd->subtype)
25562562
{
25572563
caseAT_AddColumn:/* ADD COLUMN */
2564+
caseAT_AddColumnToView:/* add column via CREATE OR REPLACE VIEW */
25582565
ATExecAddColumn(tab,rel, (ColumnDef*)cmd->def);
25592566
break;
25602567
caseAT_ColumnDefault:/* ALTER COLUMN DEFAULT */
@@ -3455,6 +3462,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
34553462
inti;
34563463
intminattnum,
34573464
maxatts;
3465+
charrelkind;
34583466
HeapTupletypeTuple;
34593467
OidtypeOid;
34603468
int32typmod;
@@ -3527,6 +3535,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
35273535
colDef->colname,RelationGetRelationName(rel))));
35283536

35293537
minattnum= ((Form_pg_class)GETSTRUCT(reltup))->relnatts;
3538+
relkind= ((Form_pg_class)GETSTRUCT(reltup))->relkind;
35303539
maxatts=minattnum+1;
35313540
if (maxatts>MaxHeapAttributeNumber)
35323541
ereport(ERROR,
@@ -3625,44 +3634,48 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
36253634
* Note: we use build_column_default, and not just the cooked default
36263635
* returned by AddRelationNewConstraints, so that the right thing happens
36273636
* when a datatype's default applies.
3637+
*
3638+
* We skip this logic completely for views.
36283639
*/
3629-
defval= (Expr*)build_column_default(rel,attribute.attnum);
3640+
if (relkind!=RELKIND_VIEW) {
3641+
defval= (Expr*)build_column_default(rel,attribute.attnum);
36303642

3631-
if (!defval&&GetDomainConstraints(typeOid)!=NIL)
3632-
{
3633-
OidbaseTypeId;
3634-
int32baseTypeMod;
3635-
3636-
baseTypeMod=typmod;
3637-
baseTypeId=getBaseTypeAndTypmod(typeOid,&baseTypeMod);
3638-
defval= (Expr*)makeNullConst(baseTypeId,baseTypeMod);
3639-
defval= (Expr*)coerce_to_target_type(NULL,
3640-
(Node*)defval,
3641-
baseTypeId,
3642-
typeOid,
3643-
typmod,
3644-
COERCION_ASSIGNMENT,
3645-
COERCE_IMPLICIT_CAST,
3646-
-1);
3647-
if (defval==NULL)/* should not happen */
3648-
elog(ERROR,"failed to coerce base type to domain");
3649-
}
3643+
if (!defval&&GetDomainConstraints(typeOid)!=NIL)
3644+
{
3645+
OidbaseTypeId;
3646+
int32baseTypeMod;
3647+
3648+
baseTypeMod=typmod;
3649+
baseTypeId=getBaseTypeAndTypmod(typeOid,&baseTypeMod);
3650+
defval= (Expr*)makeNullConst(baseTypeId,baseTypeMod);
3651+
defval= (Expr*)coerce_to_target_type(NULL,
3652+
(Node*)defval,
3653+
baseTypeId,
3654+
typeOid,
3655+
typmod,
3656+
COERCION_ASSIGNMENT,
3657+
COERCE_IMPLICIT_CAST,
3658+
-1);
3659+
if (defval==NULL)/* should not happen */
3660+
elog(ERROR,"failed to coerce base type to domain");
3661+
}
36503662

3651-
if (defval)
3652-
{
3653-
NewColumnValue*newval;
3663+
if (defval)
3664+
{
3665+
NewColumnValue*newval;
36543666

3655-
newval= (NewColumnValue*)palloc0(sizeof(NewColumnValue));
3656-
newval->attnum=attribute.attnum;
3657-
newval->expr=defval;
3667+
newval= (NewColumnValue*)palloc0(sizeof(NewColumnValue));
3668+
newval->attnum=attribute.attnum;
3669+
newval->expr=defval;
36583670

3659-
tab->newvals=lappend(tab->newvals,newval);
3660-
}
3671+
tab->newvals=lappend(tab->newvals,newval);
3672+
}
36613673

3662-
/*
3663-
* If the new column is NOT NULL, tell Phase 3 it needs to test that.
3664-
*/
3665-
tab->new_notnull |=colDef->is_not_null;
3674+
/*
3675+
* If the new column is NOT NULL, tell Phase 3 it needs to test that.
3676+
*/
3677+
tab->new_notnull |=colDef->is_not_null;
3678+
}
36663679

36673680
/*
36683681
* Add needed dependency entries for the new column.

‎src/backend/commands/view.c‎

Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.107 2008/08/2522:42:32 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.108 2008/12/06 23:22:46 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -172,9 +172,34 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
172172
*/
173173
Assert(relation->istemp==rel->rd_istemp);
174174

175+
/*
176+
* If new attributes have been added, we must modify the pre-existing
177+
* view.
178+
*/
179+
if (list_length(attrList)>rel->rd_att->natts) {
180+
List*atcmds=NIL;
181+
ListCell*c;
182+
intskip=rel->rd_att->natts;
183+
184+
foreach(c,attrList) {
185+
AlterTableCmd*atcmd;
186+
187+
if (skip>0) {
188+
--skip;
189+
continue;
190+
}
191+
atcmd=makeNode(AlterTableCmd);
192+
atcmd->subtype=AT_AddColumnToView;
193+
atcmd->def=lfirst(c);
194+
atcmds=lappend(atcmds,atcmd);
195+
}
196+
AlterTableInternal(viewOid,atcmds, true);
197+
}
198+
175199
/*
176200
* Create a tuple descriptor to compare against the existing view, and
177-
* verify it matches.
201+
* verify that the old column list is an initial prefix of the new
202+
* column list.
178203
*/
179204
descriptor=BuildDescForRelation(attrList);
180205
checkViewTupleDesc(descriptor,rel->rd_att);
@@ -219,13 +244,13 @@ checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
219244
{
220245
inti;
221246

222-
if (newdesc->natts!=olddesc->natts)
247+
if (newdesc->natts<olddesc->natts)
223248
ereport(ERROR,
224249
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
225-
errmsg("cannotchange number ofcolumnsin view")));
250+
errmsg("cannotdropcolumnsfrom view")));
226251
/* we can ignore tdhasoid */
227252

228-
for (i=0;i<newdesc->natts;i++)
253+
for (i=0;i<olddesc->natts;i++)
229254
{
230255
Form_pg_attributenewattr=newdesc->attrs[i];
231256
Form_pg_attributeoldattr=olddesc->attrs[i];
@@ -234,7 +259,7 @@ checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
234259
if (newattr->attisdropped!=oldattr->attisdropped)
235260
ereport(ERROR,
236261
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
237-
errmsg("cannotchange number ofcolumnsin view")));
262+
errmsg("cannotdropcolumnsfrom view")));
238263

239264
if (strcmp(NameStr(newattr->attname),NameStr(oldattr->attname))!=0)
240265
ereport(ERROR,

‎src/backend/parser/parse_utilcmd.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@
1919
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
2020
* Portions Copyright (c) 1994, Regents of the University of California
2121
*
22-
*$PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.17 2008/09/01 20:42:45 tgl Exp $
22+
*$PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.18 2008/12/06 23:22:46 momjian Exp $
2323
*
2424
*-------------------------------------------------------------------------
2525
*/
@@ -1721,6 +1721,7 @@ transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
17211721
switch (cmd->subtype)
17221722
{
17231723
caseAT_AddColumn:
1724+
caseAT_AddColumnToView:
17241725
{
17251726
ColumnDef*def= (ColumnDef*)cmd->def;
17261727

‎src/include/nodes/parsenodes.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
1414
* Portions Copyright (c) 1994, Regents of the University of California
1515
*
16-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.381 2008/12/04 17:51:27 petere Exp $
16+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.382 2008/12/06 23:22:46 momjian Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -989,6 +989,7 @@ typedef struct AlterTableStmt
989989
typedefenumAlterTableType
990990
{
991991
AT_AddColumn,/* add column */
992+
AT_AddColumnToView,/* implicitly via CREATE OR REPLACE VIEW */
992993
AT_ColumnDefault,/* alter column default */
993994
AT_DropNotNull,/* alter column drop not null */
994995
AT_SetNotNull,/* alter column set not null */

‎src/test/regress/expected/create_view.out‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -49,15 +49,18 @@ SELECT * FROM viewtest;
4949
-- should fail
5050
CREATE OR REPLACE VIEW viewtest AS
5151
SELECT a FROM viewtest_tbl WHERE a <> 20;
52-
ERROR: cannotchange number ofcolumnsin view
52+
ERROR: cannotdropcolumnsfrom view
5353
-- should fail
5454
CREATE OR REPLACE VIEW viewtest AS
5555
SELECT 1, * FROM viewtest_tbl;
56-
ERROR:cannot change numberofcolumns in view
56+
ERROR:column "b"ofrelation "viewtest" already exists
5757
-- should fail
5858
CREATE OR REPLACE VIEW viewtest AS
5959
SELECT a, b::numeric FROM viewtest_tbl;
6060
ERROR: cannot change data type of view column "b"
61+
-- should work
62+
CREATE OR REPLACE VIEW viewtest AS
63+
SELECT a, b, 0 AS c FROM viewtest_tbl;
6164
DROP VIEW viewtest;
6265
DROP TABLE viewtest_tbl;
6366
-- tests for temporary views

‎src/test/regress/sql/create_view.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,10 @@ CREATE OR REPLACE VIEW viewtest AS
6161
CREATE OR REPLACEVIEWviewtestAS
6262
SELECT a, b::numericFROM viewtest_tbl;
6363

64+
-- should work
65+
CREATE OR REPLACEVIEWviewtestAS
66+
SELECT a, b,0AS cFROM viewtest_tbl;
67+
6468
DROPVIEW viewtest;
6569
DROPTABLE viewtest_tbl;
6670

0 commit comments

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