Skip to content

Commit ff1ea21

Browse files
bmomjianbmomjian
committed
Allow CREATE OR REPLACE VIEW to add columns to the _end_ of the view.
Robert Haas
1 parent 31076c8 commit ff1ea21

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:46 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.38 2008/12/06 23:22:46 momjian 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+
case AT_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
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
23382344

23392345
/*
@@ -2555,6 +2561,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
25552561
switch (cmd->subtype)
25562562
{
25572563
case AT_AddColumn: /* ADD COLUMN */
2564+
case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */
25582565
ATExecAddColumn(tab, rel, (ColumnDef *) cmd->def);
25592566
break;
25602567
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
@@ -3455,6 +3462,7 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
34553462
int i;
34563463
int minattnum,
34573464
maxatts;
3465+
char relkind;
34583466
HeapTuple typeTuple;
34593467
Oid typeOid;
34603468
int32 typmod;
@@ -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-
Oid baseTypeId;
3634-
int32 baseTypeMod;
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+
Oid baseTypeId;
3646+
int32 baseTypeMod;
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/25 22: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+
int skip = 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
int i;
221246

222-
if (newdesc->natts != olddesc->natts)
247+
if (newdesc->natts < olddesc->natts)
223248
ereport(ERROR,
224249
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
225-
errmsg("cannot change number of columns in view")));
250+
errmsg("cannot drop columns from 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_attribute newattr = newdesc->attrs[i];
231256
Form_pg_attribute oldattr = 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("cannot change number of columns in view")));
262+
errmsg("cannot drop columns from 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
case AT_AddColumn:
1724+
case AT_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
typedef enum AlterTableType
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: cannot change number of columns in view
52+
ERROR: cannot drop columns from view
5353
-- should fail
5454
CREATE OR REPLACE VIEW viewtest AS
5555
SELECT 1, * FROM viewtest_tbl;
56-
ERROR: cannot change number of columns in view
56+
ERROR: column "b" of relation "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 REPLACE VIEW viewtest AS
6262
SELECT a, b::numeric FROM viewtest_tbl;
6363

64+
-- should work
65+
CREATE OR REPLACE VIEW viewtest AS
66+
SELECT a, b, 0 AS c FROM viewtest_tbl;
67+
6468
DROP VIEW viewtest;
6569
DROP TABLE viewtest_tbl;
6670

0 commit comments

Comments
 (0)