Here's a patch that allows CREATE OR REPLACE VIEW to add new columns
to an existing view.
Any feedback would be appreciated, especially if it meant that I could
fix any problems before the next commitfest.
Thanks,
...Robert
Index: doc/src/sgml/ref/create_view.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v
retrieving revision 1.36
diff -c -r1.36 create_view.sgml
*** doc/src/sgml/ref/create_view.sgml 3 Jul 2007 01:30:35 -0000 1.36
--- doc/src/sgml/ref/create_view.sgml 7 Aug 2008 11:22:33 -0000
***************
*** 36,44 ****
<para>
<command>CREATE OR REPLACE VIEW</command> is similar, but if a view
! of the same name already exists, it is replaced. You can only replace
! a view with a new query that generates the identical set of columns
! (i.e., same column names and data types).
</para>
<para>
--- 36,45 ----
<para>
<command>CREATE OR REPLACE VIEW</command> is similar, but if a view
! of the same name already exists, it is replaced. The new query must
! generate all of the same columns that were generated by the original query
! in the same order and with the same data types, but may add additional
! columns to the end of the list.
</para>
<para>
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.261
diff -c -r1.261 tablecmds.c
*** src/backend/commands/tablecmds.c 16 Jul 2008 19:33:25 -0000 1.261
--- src/backend/commands/tablecmds.c 7 Aug 2008 11:22:39 -0000
***************
*** 2327,2332 ****
--- 2327,2338 ----
ATPrepAddColumn(wqueue, rel, recurse, cmd);
pass = AT_PASS_ADD_COL;
break;
+ case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */
+ ATSimplePermissions(rel, true);
+ /* Performs own recursion */
+ ATPrepAddColumn(wqueue, rel, recurse, cmd);
+ pass = AT_PASS_ADD_COL;
+ break;
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
/*
***************
*** 2548,2553 ****
--- 2554,2560 ----
switch (cmd->subtype)
{
case AT_AddColumn: /* ADD COLUMN */
+ case AT_AddColumnToView: /* add column via CREATE OR REPLACE VIEW */
ATExecAddColumn(tab, rel, (ColumnDef *) cmd->def);
break;
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
Index: src/backend/commands/view.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/view.c,v
retrieving revision 1.106
diff -c -r1.106 view.c
*** src/backend/commands/view.c 19 Jun 2008 00:46:04 -0000 1.106
--- src/backend/commands/view.c 7 Aug 2008 11:22:39 -0000
***************
*** 174,181 ****
Assert(relation->istemp == rel->rd_istemp);
/*
* Create a tuple descriptor to compare against the existing view, and
! * verify it matches.
*/
descriptor = BuildDescForRelation(attrList);
checkViewTupleDesc(descriptor, rel->rd_att);
--- 174,206 ----
Assert(relation->istemp == rel->rd_istemp);
/*
+ * If new attributes have been added, we must modify the pre-existing
+ * view.
+ */
+ if (list_length(attrList) > rel->rd_att->natts) {
+ List *atcmds = NIL;
+ ListCell *c;
+ int skip = rel->rd_att->natts;
+
+ foreach(c, attrList) {
+ AlterTableCmd *atcmd;
+
+ if (skip > 0) {
+ --skip;
+ continue;
+ }
+ atcmd = makeNode(AlterTableCmd);
+ atcmd->subtype = AT_AddColumnToView;
+ atcmd->def = lfirst(c);
+ atcmds = lappend(atcmds, atcmd);
+ }
+ AlterTableInternal(viewOid, atcmds, true);
+ }
+
+ /*
* Create a tuple descriptor to compare against the existing view, and
! * verify that the old column list is an initial prefix of the new
! * column list.
*/
descriptor = BuildDescForRelation(attrList);
checkViewTupleDesc(descriptor, rel->rd_att);
***************
*** 220,232 ****
{
int i;
! if (newdesc->natts != olddesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
! errmsg("cannot change number of columns in view")));
/* we can ignore tdhasoid */
! for (i = 0; i < newdesc->natts; i++)
{
Form_pg_attribute newattr = newdesc->attrs[i];
Form_pg_attribute oldattr = olddesc->attrs[i];
--- 245,257 ----
{
int i;
! if (newdesc->natts < olddesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
! errmsg("cannot drop columns from view")));
/* we can ignore tdhasoid */
! for (i = 0; i < olddesc->natts; i++)
{
Form_pg_attribute newattr = newdesc->attrs[i];
Form_pg_attribute oldattr = olddesc->attrs[i];
***************
*** 235,241 ****
if (newattr->attisdropped != oldattr->attisdropped)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
! errmsg("cannot change number of columns in view")));
if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
ereport(ERROR,
--- 260,266 ----
if (newattr->attisdropped != oldattr->attisdropped)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
! errmsg("cannot drop columns from view")));
if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
ereport(ERROR,
Index: src/backend/parser/parse_utilcmd.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/parse_utilcmd.c,v
retrieving revision 2.14
diff -c -r2.14 parse_utilcmd.c
*** src/backend/parser/parse_utilcmd.c 16 Jul 2008 01:30:22 -0000 2.14
--- src/backend/parser/parse_utilcmd.c 7 Aug 2008 11:22:41 -0000
***************
*** 1719,1724 ****
--- 1719,1725 ----
switch (cmd->subtype)
{
case AT_AddColumn:
+ case AT_AddColumnToView:
{
ColumnDef *def = (ColumnDef *) cmd->def;
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.371
diff -c -r1.371 parsenodes.h
*** src/include/nodes/parsenodes.h 7 Aug 2008 01:11:51 -0000 1.371
--- src/include/nodes/parsenodes.h 7 Aug 2008 11:22:43 -0000
***************
*** 907,912 ****
--- 907,913 ----
typedef enum AlterTableType
{
AT_AddColumn, /* add column */
+ AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */
AT_ColumnDefault, /* alter column default */
AT_DropNotNull, /* alter column drop not null */
AT_SetNotNull, /* alter column set not null */
Index: src/test/regress/expected/create_view.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/create_view.out,v
retrieving revision 1.13
diff -c -r1.13 create_view.out
*** src/test/regress/expected/create_view.out 11 Jun 2008 21:53:49 -0000 1.13
--- src/test/regress/expected/create_view.out 7 Aug 2008 11:22:45 -0000
***************
*** 49,63 ****
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a FROM viewtest_tbl WHERE a <> 20;
! ERROR: cannot change number of columns in view
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT 1, * FROM viewtest_tbl;
! ERROR: cannot change number of columns in view
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b::numeric FROM viewtest_tbl;
ERROR: cannot change data type of view column "b"
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;
-- tests for temporary views
--- 49,66 ----
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a FROM viewtest_tbl WHERE a <> 20;
! ERROR: cannot drop columns from view
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT 1, * FROM viewtest_tbl;
! ERROR: column "b" of relation "viewtest" already exists
-- should fail
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b::numeric FROM viewtest_tbl;
ERROR: cannot change data type of view column "b"
+ -- should work
+ CREATE OR REPLACE VIEW viewtest AS
+ SELECT a, b, 0 AS c FROM viewtest_tbl;
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;
-- tests for temporary views
Index: src/test/regress/sql/create_view.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/create_view.sql,v
retrieving revision 1.7
diff -c -r1.7 create_view.sql
*** src/test/regress/sql/create_view.sql 7 Apr 2005 01:51:41 -0000 1.7
--- src/test/regress/sql/create_view.sql 7 Aug 2008 11:22:45 -0000
***************
*** 61,66 ****
--- 61,70 ----
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b::numeric FROM viewtest_tbl;
+ -- should work
+ CREATE OR REPLACE VIEW viewtest AS
+ SELECT a, b, 0 AS c FROM viewtest_tbl;
+
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
