Thread: pgsql: WITH CHECK OPTION support for auto-updatable VIEWs
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 Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/4cbe3ac3e86790d05c569de4585e5075a62a9b41 Modified Files -------------- doc/src/sgml/ref/alter_view.sgml | 5 + doc/src/sgml/ref/create_view.sgml | 199 ++++++++++---- src/backend/access/common/reloptions.c | 14 + src/backend/catalog/information_schema.sql | 8 +- src/backend/catalog/sql_features.txt | 4 +- src/backend/commands/tablecmds.c | 36 +++ src/backend/commands/view.c | 68 +++++ src/backend/executor/execMain.c | 43 +++ src/backend/executor/nodeModifyTable.c | 33 +++ src/backend/nodes/copyfuncs.c | 18 ++ src/backend/nodes/equalfuncs.c | 15 + src/backend/nodes/nodeFuncs.c | 14 + src/backend/nodes/outfuncs.c | 15 + src/backend/nodes/readfuncs.c | 18 ++ src/backend/optimizer/plan/createplan.c | 15 +- src/backend/optimizer/plan/planner.c | 30 +- src/backend/parser/gram.y | 43 ++- src/backend/rewrite/rewriteHandler.c | 117 +++++++- src/bin/pg_dump/pg_dump.c | 16 +- src/bin/pg_dump/pg_dump.h | 1 + src/include/catalog/catversion.h | 2 +- src/include/commands/view.h | 2 + src/include/executor/executor.h | 2 + src/include/nodes/execnodes.h | 4 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 23 ++ src/include/nodes/plannodes.h | 1 + src/include/optimizer/planmain.h | 3 +- src/include/rewrite/rewriteHandler.h | 4 + src/include/utils/rel.h | 34 +++ src/test/regress/expected/create_view.out | 2 +- src/test/regress/expected/updatable_views.out | 363 +++++++++++++++++++++++++ src/test/regress/sql/updatable_views.sql | 199 ++++++++++++++ 33 files changed, 1245 insertions(+), 107 deletions(-)
On Thu, Jul 18, 2013 at 09:24:02PM +0000, Stephen Frost wrote: > 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 > > Branch > ------ > master What am I missing here: create table some_data (id int4 primary key, payload text); create view first as select * from some_data where 0 = id % 2 with local check option; create view second as select * from first where 0 = id with local check option; insert into second (id, payload) values (15, '15 is divisible by 3, but not by 2'); ERROR: new row violates WITH CHECK OPTION for view "first" DETAIL: Failing row contains (15, 15 is divisible by 3, but not by 2). If I read it correctly, insert to "second" with id = 15 should work, because the where on "second" matches, and local check shouldn't be checking on "first"? Best regards, depesz