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

Commitcab5dc5

Browse files
committed
Allow only some columns of a view to be auto-updateable.
Previously, unless all columns were auto-updateable, we wouldn'tinserts, updates, or deletes, or at least not without a rule or trigger;now, we'll allow inserts and updates that target only the auto-updateablecolumns, and deletes even if there are no auto-updateable columns atall provided the view definition is otherwise suitable.Dean Rasheed, reviewed by Marko Tiikkaja
1 parent523beaa commitcab5dc5

File tree

8 files changed

+701
-178
lines changed

8 files changed

+701
-178
lines changed

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

Lines changed: 29 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -319,16 +319,8 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
319319

320320
<listitem>
321321
<para>
322-
All columns in the view's select list must be simple references to
323-
columns of the underlying relation. They cannot be expressions,
324-
literals or functions. System columns cannot be referenced, either.
325-
</para>
326-
</listitem>
327-
328-
<listitem>
329-
<para>
330-
No column of the underlying relation can appear more than once in
331-
the view's select list.
322+
The view's select list must not contain any aggregates, window functions
323+
or set-returning functions.
332324
</para>
333325
</listitem>
334326

@@ -340,6 +332,14 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
340332
</itemizedlist>
341333
</para>
342334

335+
<para>
336+
An automatically updatable view may contain a mix of updatable and
337+
non-updatable columns. A column is updatable if it is a simple reference
338+
to an updatable column of the underlying base relation; otherwise the
339+
column is read-only, and an error will be raised if an <command>INSERT</>
340+
or <command>UPDATE</> statement attempts to assign a value to it.
341+
</para>
342+
343343
<para>
344344
If the view is automatically updatable the system will convert any
345345
<command>INSERT</>, <command>UPDATE</> or <command>DELETE</> statement
@@ -434,6 +434,25 @@ CREATE VIEW pg_comedies AS
434434
<literal>classification</> of new rows.
435435
</para>
436436

437+
<para>
438+
Create a view with a mix of updatable and non-updatable columns:
439+
440+
<programlisting>
441+
CREATE VIEW comedies AS
442+
SELECT f.*,
443+
country_code_to_name(f.country_code) AS country,
444+
(SELECT avg(r.rating)
445+
FROM user_ratings r
446+
WHERE r.film_id = f.id) AS avg_rating
447+
FROM films f
448+
WHERE f.kind = 'Comedy';
449+
</programlisting>
450+
This view will support <command>INSERT</>, <command>UPDATE</> and
451+
<command>DELETE</>. All the columns from the <literal>films</> table will
452+
be updatable, whereas the computed columns <literal>country</> and
453+
<literal>avg_rating</> will be read-only.
454+
</para>
455+
437456
<para>
438457
Create a recursive view consisting of the numbers from 1 to 100:
439458
<programlisting>

‎src/backend/commands/tablecmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8806,7 +8806,8 @@ ATExecSetRelOptions(Relation rel, List *defList, AlterTableType operation,
88068806
if (check_option)
88078807
{
88088808
constchar*view_updatable_error=
8809-
view_query_is_auto_updatable(view_query,security_barrier);
8809+
view_query_is_auto_updatable(view_query,
8810+
security_barrier, true);
88108811

88118812
if (view_updatable_error)
88128813
ereport(ERROR,

‎src/backend/commands/view.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -469,7 +469,7 @@ DefineView(ViewStmt *stmt, const char *queryString)
469469
if (check_option)
470470
{
471471
constchar*view_updatable_error=
472-
view_query_is_auto_updatable(viewParse,security_barrier);
472+
view_query_is_auto_updatable(viewParse,security_barrier, true);
473473

474474
if (view_updatable_error)
475475
ereport(ERROR,

0 commit comments

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