diff options
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 25 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 21 | ||||
| -rw-r--r-- | doc/src/sgml/release.sgml | 3 | ||||
| -rw-r--r-- | src/backend/commands/view.c | 53 | ||||
| -rw-r--r-- | src/test/regress/expected/create_view.out | 6 |
5 files changed, 84 insertions, 24 deletions
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 86046359b4a..998d075a645 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.36 2002/05/18 15:44:47 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.37 2002/09/02 20:04:39 tgl Exp $ PostgreSQL documentation --> @@ -21,17 +21,15 @@ PostgreSQL documentation <date>2001-01-05</date> </refsynopsisdivinfo> <synopsis> -CREATE RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable> +CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable> TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ] DO [ INSTEAD ] <replaceable class="parameter">action</replaceable> where <replaceable class="PARAMETER">action</replaceable> can be: NOTHING -| -<replaceable class="parameter">query</replaceable> -| -( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... ) +| <replaceable class="parameter">query</replaceable> +| ( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... ) </synopsis> <refsect2 id="R2-SQL-CREATERULE-1"> @@ -76,9 +74,10 @@ NOTHING <term><replaceable class="parameter">condition</replaceable></term> <listitem> <para> - Any SQL conditional expression (returning <type>boolean</type>). The condition expression may not + Any SQL conditional expression (returning <type>boolean</type>). + The condition expression may not refer to any tables except <literal>new</literal> and - <literal>old</literal>. + <literal>old</literal>, and may not contain aggregate functions. </para> </listitem> </varlistentry> @@ -143,6 +142,14 @@ CREATE RULE </title> <para> + <command>CREATE RULE</command> defines a new rule applying to a specified + table or view. + <command>CREATE OR REPLACE RULE</command> will either create a + new rule, or replace an existing rule of the same name for the same + table. + </para> + + <para> The <productname>PostgreSQL</productname> <firstterm>rule system</firstterm> allows one to define an alternate action to be performed on inserts, updates, or deletions @@ -318,7 +325,7 @@ UPDATE mytable SET name = 'foo' WHERE id = 42; </title> <para> - <command>CREATE RULE</command> statement is a <productname>PostgreSQL</productname> + <command>CREATE RULE</command> is a <productname>PostgreSQL</productname> language extension. There is no <command>CREATE RULE</command> statement in <acronym>SQL92</acronym>. </para> diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index aa7c869285f..1692f9f0f77 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v 1.18 2002/05/18 15:44:47 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v 1.19 2002/09/02 20:04:39 tgl Exp $ PostgreSQL documentation --> @@ -21,7 +21,7 @@ PostgreSQL documentation <date>2000-03-25</date> </refsynopsisdivinfo> <synopsis> -CREATE VIEW <replaceable class="PARAMETER">view</replaceable> [ ( <replaceable +CREATE [ OR REPLACE ] VIEW <replaceable class="PARAMETER">view</replaceable> [ ( <replaceable class="PARAMETER">column name list</replaceable> ) ] AS SELECT <replaceable class="PARAMETER">query</replaceable> </synopsis> @@ -132,14 +132,22 @@ CREATE VIEW vista AS SELECT text 'Hello World' <title> Description </title> + <para> - <command>CREATE VIEW</command> will define a view of a query. + <command>CREATE VIEW</command> defines a view of a query. The view is not physically materialized. Instead, a query rewrite rule (an <literal>ON SELECT</> rule) is automatically generated to support SELECT operations on views. </para> <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> If a schema name is given (for example, <literal>CREATE VIEW myschema.myview ...</>) then the view is created in the specified schema. Otherwise it is created in the current schema (the one @@ -206,6 +214,7 @@ SELECT * FROM kinds; <title> SQL92 </title> + <para> SQL92 specifies some additional capabilities for the <command>CREATE VIEW</command> statement: @@ -253,6 +262,12 @@ CREATE VIEW <replaceable class="parameter">view</replaceable> [ <replaceable cla </varlistentry> </variablelist> </para> + + <para> + <command>CREATE OR REPLACE VIEW</command> is a + <productname>PostgreSQL</productname> language extension. + </para> + </refsect2> </refsect1> </refentry> diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 3ba21bc1ab7..d0fc445380c 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.156 2002/08/30 22:18:05 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.157 2002/09/02 20:04:39 tgl Exp $ --> <appendix id="release"> @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> <literallayout><![CDATA[ +CREATE OR REPLACE VIEW, CREATE OR REPLACE RULE are available No-autocommit mode is available (set autocommit to off) Substantial improvements in functionality for functions returning sets Client libraries older than 6.3 no longer supported (version 0 protocol removed) diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 46cdf0aca0b..91420319a88 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.69 2002/09/02 02:13:01 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.70 2002/09/02 20:04:40 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -29,6 +29,9 @@ #include "utils/lsyscache.h" +static void checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc); + + /*--------------------------------------------------------------------- * DefineVirtualRelation * @@ -111,15 +114,9 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) /* * Create a tuple descriptor to compare against the existing view, * and verify it matches. - * - * XXX the error message is a bit cheesy here: would be useful to - * give a more specific complaint about the difference in the - * descriptors. No time for it at the moment though. */ descriptor = BuildDescForRelation(attrList); - if (!equalTupleDescs(descriptor, rel->rd_att)) - elog(ERROR, "Cannot change column set of existing view %s", - RelationGetRelationName(rel)); + checkViewTupleDesc(descriptor, rel->rd_att); /* * Seems okay, so return the OID of the pre-existing view. @@ -149,6 +146,46 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) } } +/* + * Verify that tupledesc associated with proposed new view definition + * matches tupledesc of old view. This is basically a cut-down version + * of equalTupleDescs(), with code added to generate specific complaints. + */ +static void +checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc) +{ + int i; + + if (newdesc->natts != olddesc->natts) + elog(ERROR, "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]; + + /* XXX not right, but we don't support DROP COL on view anyway */ + if (newattr->attisdropped != oldattr->attisdropped) + elog(ERROR, "Cannot change number of columns in view"); + + if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0) + elog(ERROR, "Cannot change name of view column \"%s\"", + NameStr(oldattr->attname)); + /* XXX would it be safe to allow atttypmod to change? Not sure */ + if (newattr->atttypid != oldattr->atttypid || + newattr->atttypmod != oldattr->atttypmod) + elog(ERROR, "Cannot change datatype of view column \"%s\"", + NameStr(oldattr->attname)); + /* We can ignore the remaining attributes of an attribute... */ + } + /* + * We ignore the constraint fields. The new view desc can't have any + * constraints, and the only ones that could be on the old view are + * defaults, which we are happy to leave in place. + */ +} + static RuleStmt * FormViewRetrieveRule(const RangeVar *view, Query *viewParse, bool replace) { diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index ade98e6c311..e4b4d7484b6 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -44,14 +44,14 @@ SELECT * FROM viewtest; -- should fail CREATE OR REPLACE VIEW viewtest AS SELECT a FROM viewtest_tbl WHERE a <> 20; -ERROR: Cannot change column set of existing view viewtest +ERROR: Cannot change number of columns in view -- should fail CREATE OR REPLACE VIEW viewtest AS SELECT 1, * FROM viewtest_tbl; -ERROR: Cannot change column set of existing view viewtest +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 column set of existing view viewtest +ERROR: Cannot change datatype of view column "b" DROP VIEW viewtest; DROP TABLE viewtest_tbl; |