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

Commit8f60f43

Browse files
committed
Department of second thoughts: make checks for replacing a view slightly
more flexible, and improve the error reporting. Also, add documentationfor REPLACE RULE/VIEW.
1 parentb4d24d7 commit8f60f43

File tree

5 files changed

+84
-24
lines changed

5 files changed

+84
-24
lines changed

‎doc/src/sgml/ref/create_rule.sgml‎

Lines changed: 16 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.36 2002/05/18 15:44:47 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.37 2002/09/02 20:04:39 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,17 +21,15 @@ PostgreSQL documentation
2121
<date>2001-01-05</date>
2222
</refsynopsisdivinfo>
2323
<synopsis>
24-
CREATE RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
24+
CREATE[ OR REPLACE ]RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
2525
TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
2626
DO [ INSTEAD ] <replaceable class="parameter">action</replaceable>
2727

2828
where <replaceable class="PARAMETER">action</replaceable> can be:
2929

3030
NOTHING
31-
|
32-
<replaceable class="parameter">query</replaceable>
33-
|
34-
( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... )
31+
| <replaceable class="parameter">query</replaceable>
32+
| ( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... )
3533
</synopsis>
3634

3735
<refsect2 id="R2-SQL-CREATERULE-1">
@@ -76,9 +74,10 @@ NOTHING
7674
<term><replaceable class="parameter">condition</replaceable></term>
7775
<listitem>
7876
<para>
79-
Any SQL conditional expression (returning <type>boolean</type>). The condition expression may not
77+
Any SQL conditional expression (returning <type>boolean</type>).
78+
The condition expression may not
8079
refer to any tables except <literal>new</literal> and
81-
<literal>old</literal>.
80+
<literal>old</literal>, and may not contain aggregate functions.
8281
</para>
8382
</listitem>
8483
</varlistentry>
@@ -142,6 +141,14 @@ CREATE RULE
142141
Description
143142
</title>
144143

144+
<para>
145+
<command>CREATE RULE</command> defines a new rule applying to a specified
146+
table or view.
147+
<command>CREATE OR REPLACE RULE</command> will either create a
148+
new rule, or replace an existing rule of the same name for the same
149+
table.
150+
</para>
151+
145152
<para>
146153
The <productname>PostgreSQL</productname>
147154
<firstterm>rule system</firstterm> allows one to define an
@@ -318,7 +325,7 @@ UPDATE mytable SET name = 'foo' WHERE id = 42;
318325
</title>
319326

320327
<para>
321-
<command>CREATE RULE</command>statementis a <productname>PostgreSQL</productname>
328+
<command>CREATE RULE</command> is a <productname>PostgreSQL</productname>
322329
language extension.
323330
There is no <command>CREATE RULE</command> statement in <acronym>SQL92</acronym>.
324331
</para>

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

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v 1.18 2002/05/18 15:44:47 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v 1.19 2002/09/02 20:04:39 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,7 +21,7 @@ PostgreSQL documentation
2121
<date>2000-03-25</date>
2222
</refsynopsisdivinfo>
2323
<synopsis>
24-
CREATE VIEW <replaceable class="PARAMETER">view</replaceable> [ ( <replaceable
24+
CREATE[ OR REPLACE ]VIEW <replaceable class="PARAMETER">view</replaceable> [ ( <replaceable
2525
class="PARAMETER">column name list</replaceable> ) ] AS SELECT <replaceable class="PARAMETER">query</replaceable>
2626
</synopsis>
2727

@@ -132,13 +132,21 @@ CREATE VIEW vista AS SELECT text 'Hello World'
132132
<title>
133133
Description
134134
</title>
135+
135136
<para>
136-
<command>CREATE VIEW</command>will define a view of a query.
137+
<command>CREATE VIEW</command>defines a view of a query.
137138
The view is not physically materialized. Instead, a query
138139
rewrite rule (an <literal>ON SELECT</> rule) is automatically generated to
139140
support SELECT operations on views.
140141
</para>
141142

143+
<para>
144+
<command>CREATE OR REPLACE VIEW</command> is similar, but if a view
145+
of the same name already exists, it is replaced. You can only replace
146+
a view with a new query that generates the identical set of columns
147+
(i.e., same column names and data types).
148+
</para>
149+
142150
<para>
143151
If a schema name is given (for example, <literal>CREATE VIEW
144152
myschema.myview ...</>) then the view is created in the
@@ -206,6 +214,7 @@ SELECT * FROM kinds;
206214
<title>
207215
SQL92
208216
</title>
217+
209218
<para>
210219
SQL92 specifies some additional capabilities for the
211220
<command>CREATE VIEW</command> statement:
@@ -253,6 +262,12 @@ CREATE VIEW <replaceable class="parameter">view</replaceable> [ <replaceable cla
253262
</varlistentry>
254263
</variablelist>
255264
</para>
265+
266+
<para>
267+
<command>CREATE OR REPLACE VIEW</command> is a
268+
<productname>PostgreSQL</productname> language extension.
269+
</para>
270+
256271
</refsect2>
257272
</refsect1>
258273
</refentry>

‎doc/src/sgml/release.sgml‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.156 2002/08/30 22:18:05 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.157 2002/09/02 20:04:39 tgl Exp $
33
-->
44

55
<appendix id="release">
@@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without
2424
worries about funny characters.
2525
-->
2626
<literallayout><![CDATA[
27+
CREATE OR REPLACE VIEW, CREATE OR REPLACE RULE are available
2728
No-autocommit mode is available (set autocommit to off)
2829
Substantial improvements in functionality for functions returning sets
2930
Client libraries older than 6.3 no longer supported (version 0 protocol removed)

‎src/backend/commands/view.c‎

Lines changed: 45 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.69 2002/09/0202:13:01 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.70 2002/09/0220:04:40 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -29,6 +29,9 @@
2929
#include"utils/lsyscache.h"
3030

3131

32+
staticvoidcheckViewTupleDesc(TupleDescnewdesc,TupleDescolddesc);
33+
34+
3235
/*---------------------------------------------------------------------
3336
* DefineVirtualRelation
3437
*
@@ -111,15 +114,9 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
111114
/*
112115
* Create a tuple descriptor to compare against the existing view,
113116
* and verify it matches.
114-
*
115-
* XXX the error message is a bit cheesy here: would be useful to
116-
* give a more specific complaint about the difference in the
117-
* descriptors. No time for it at the moment though.
118117
*/
119118
descriptor=BuildDescForRelation(attrList);
120-
if (!equalTupleDescs(descriptor,rel->rd_att))
121-
elog(ERROR,"Cannot change column set of existing view %s",
122-
RelationGetRelationName(rel));
119+
checkViewTupleDesc(descriptor,rel->rd_att);
123120

124121
/*
125122
* Seems okay, so return the OID of the pre-existing view.
@@ -149,6 +146,46 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
149146
}
150147
}
151148

149+
/*
150+
* Verify that tupledesc associated with proposed new view definition
151+
* matches tupledesc of old view. This is basically a cut-down version
152+
* of equalTupleDescs(), with code added to generate specific complaints.
153+
*/
154+
staticvoid
155+
checkViewTupleDesc(TupleDescnewdesc,TupleDescolddesc)
156+
{
157+
inti;
158+
159+
if (newdesc->natts!=olddesc->natts)
160+
elog(ERROR,"Cannot change number of columns in view");
161+
/* we can ignore tdhasoid */
162+
163+
for (i=0;i<newdesc->natts;i++)
164+
{
165+
Form_pg_attributenewattr=newdesc->attrs[i];
166+
Form_pg_attributeoldattr=olddesc->attrs[i];
167+
168+
/* XXX not right, but we don't support DROP COL on view anyway */
169+
if (newattr->attisdropped!=oldattr->attisdropped)
170+
elog(ERROR,"Cannot change number of columns in view");
171+
172+
if (strcmp(NameStr(newattr->attname),NameStr(oldattr->attname))!=0)
173+
elog(ERROR,"Cannot change name of view column \"%s\"",
174+
NameStr(oldattr->attname));
175+
/* XXX would it be safe to allow atttypmod to change? Not sure */
176+
if (newattr->atttypid!=oldattr->atttypid||
177+
newattr->atttypmod!=oldattr->atttypmod)
178+
elog(ERROR,"Cannot change datatype of view column \"%s\"",
179+
NameStr(oldattr->attname));
180+
/* We can ignore the remaining attributes of an attribute... */
181+
}
182+
/*
183+
* We ignore the constraint fields. The new view desc can't have any
184+
* constraints, and the only ones that could be on the old view are
185+
* defaults, which we are happy to leave in place.
186+
*/
187+
}
188+
152189
staticRuleStmt*
153190
FormViewRetrieveRule(constRangeVar*view,Query*viewParse,boolreplace)
154191
{

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -44,14 +44,14 @@ SELECT * FROM viewtest;
4444
-- should fail
4545
CREATE OR REPLACE VIEW viewtest AS
4646
SELECT a FROM viewtest_tbl WHERE a <> 20;
47-
ERROR: Cannot changecolumn setofexisting view viewtest
47+
ERROR: Cannot changenumberofcolumns in view
4848
-- should fail
4949
CREATE OR REPLACE VIEW viewtest AS
5050
SELECT 1, * FROM viewtest_tbl;
51-
ERROR: Cannot changecolumn setofexisting view viewtest
51+
ERROR: Cannot changenumberofcolumns in view
5252
-- should fail
5353
CREATE OR REPLACE VIEW viewtest AS
5454
SELECT a, b::numeric FROM viewtest_tbl;
55-
ERROR: Cannot changecolumn setofexistingviewviewtest
55+
ERROR: Cannot changedatatypeof viewcolumn "b"
5656
DROP VIEW viewtest;
5757
DROP TABLE viewtest_tbl;

0 commit comments

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