From 4cbe3ac3e86790d05c569de4585e5075a62a9b41 Mon Sep 17 00:00:00 2001 From: Stephen Frost Date: Thu, 18 Jul 2013 17:10:16 -0400 Subject: WITH CHECK OPTION support for auto-updatable VIEWs For simple views which are automatically updatable, this patch allows the user to specify what level of checking should be done on records being inserted or updated. For 'LOCAL CHECK', new tuples are validated against the conditionals of the view they are being inserted into, while for 'CASCADED CHECK' the new tuples are validated against the conditionals for all views involved (from the top down). This option is part of the SQL specification. Dean Rasheed, reviewed by Pavel Stehule --- doc/src/sgml/ref/alter_view.sgml | 5 + doc/src/sgml/ref/create_view.sgml | 199 +++++++++++++++++++++++++++----------- 2 files changed, 145 insertions(+), 59 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml index db5a656808d..cbec3ab54eb 100644 --- a/doc/src/sgml/ref/alter_view.sgml +++ b/doc/src/sgml/ref/alter_view.sgml @@ -28,6 +28,11 @@ ALTER VIEW [ IF EXISTS ] name RENAM ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] ) ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] ) + +where view_option_name can be one of: + + security_barrier [ boolean ] + check_option [ text (local or cascaded) ] diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 2af6f6e028e..8102ec2fd1b 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -24,6 +24,12 @@ PostgreSQL documentation CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ] [ WITH ( view_option_name [= view_option_value] [, ... ] ) ] AS query + [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] + +where view_option_name can be one of: + + security_barrier [ boolean ] + check_option [ text (local or cascaded) ] @@ -120,10 +126,33 @@ CREATE VIEW name AS WITH RECURSIVE name (WITH ( view_option_name [= view_option_value] [, ... ] ) - This clause specifies optional parameters for a view; currently, the - only supported parameter name is security_barrier, - which should be enabled when a view is intended to provide row-level - security. See for full details. + This clause specifies optional parameters for a view; the following + parameters are supported: + + + + security_barrier(boolean) + + + This should be used if the view is intended to provide row-level + security. See for full details. + + + + + + check_option(text) + + + This parameter may be either local or + cascaded, and is equivalent to specifying + WITH [ CASCADED | LOCAL ] CHECK OPTION (see below). + This option can be changed on existing views using . + + + + @@ -138,6 +167,77 @@ CREATE VIEW name AS WITH RECURSIVE name ( + + + WITH [ CASCADED | LOCAL ] CHECK OPTION + + + + CHECK OPTION + + + WITH CHECK OPTION + + This option controls the behavior of automatically updatable views. When + this option is specified, INSERT and UPDATE + commands on the view will be checked to ensure that new rows satisfy the + view-defining condition (that is, the new rows are checked to ensure that + they are visible through the view). If they are not, the update will be + rejected. If the CHECK OPTION is not specified, + INSERT and UPDATE commands on the view are + allowed to create rows that are not visible through the view. The + following check options are supported: + + + + LOCAL + + + New rows are only checked against the conditions defined directly in + the view itself. Any conditions defined on underlying base views are + not checked (unless they also specify the CHECK OPTION). + + + + + + CASCADED + + + New rows are checked against the conditions of the view and all + underlying base views. If the CHECK OPTION is specified, + and neither LOCAL nor CASCADED is specified, + then CASCADED is assumed. + + + + + + + + The CHECK OPTION may not be used with RECURSIVE + views. + + + + Note that the CHECK OPTION is only supported on views that + are automatically updatable, and do not have INSTEAD OF + triggers or INSTEAD rules. If an automatically updatable + view is defined on top of a base view that has INSTEAD OF + triggers, then the LOCAL CHECK OPTION may be used to check + the conditions on the automatically updatable view, but the conditions + on the base view with INSTEAD OF triggers will not be + checked (a cascaded check option will not cascade down to a + trigger-updatable view, and any check options defined directly on a + trigger-updatable view will be ignored). If the view or any of its base + relations has an INSTEAD rule that causes the + INSERT or UPDATE command to be rewritten, then + all check options will be ignored in the rewritten query, including any + checks from automatically updatable views defined on top of the relation + with the INSTEAD rule. + + + @@ -256,7 +356,9 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; condition, and thus is no longer visible through the view. Similarly, an INSERT command can potentially insert base-relation rows that do not satisfy the WHERE condition and thus are not - visible through the view. + visible through the view. The CHECK OPTION may be used to + prevent INSERT and UPDATE commands from creating + such rows that are not visible through the view. @@ -300,6 +402,38 @@ CREATE VIEW comedies AS the table will not be part of the view. + + Create a view with LOCAL CHECK OPTION: + + +CREATE VIEW universal_comedies AS + SELECT * + FROM comedies + WHERE classification = 'U' + WITH LOCAL CHECK OPTION; + + This will create a view based on the comedies view, showing + only films with kind = 'Comedy' and + classification = 'U'. Any attempt to INSERT or + UPDATE a row in the view will be rejected if the new row + doesn't have classification = 'U', but the film + kind will not be checked. + + + + Create a view with CASCADED CHECK OPTION: + + +CREATE VIEW pg_comedies AS + SELECT * + FROM comedies + WHERE classification = 'PG' + WITH CASCADED CHECK OPTION; + + This will create a view that checks both the kind and + classification of new rows. + + Create a recursive view consisting of the numbers from 1 to 100: @@ -313,64 +447,11 @@ UNION ALL Compatibility - - The SQL standard specifies some additional capabilities for the - CREATE VIEW statement: - -CREATE VIEW name [ ( column_name [, ...] ) ] - AS query - [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] - - - - - The optional clauses for the full SQL command are: - - - - CHECK OPTION - - - This option controls the behavior of automatically updatable views. - When given, INSERT and UPDATE commands on - the view will be checked to ensure new rows satisfy the - view-defining condition (that is, the new rows would be visible - through the view). If they do not, the update will be rejected. - Without CHECK OPTION, INSERT and - UPDATE commands on the view are allowed to create rows - that are not visible through the view. (The latter behavior is the - only one currently provided by PostgreSQL.) - - - - - - LOCAL - - - Check for integrity on this view. - - - - - - CASCADED - - - Check for integrity on this view and on any dependent - view. CASCADED is assumed if neither - CASCADED nor LOCAL is specified. - - - - - - CREATE OR REPLACE VIEW is a PostgreSQL language extension. So is the concept of a temporary view. - The WITH clause is an extension as well. + The WITH ( ... ) clause is an extension as well. -- cgit v1.2.3