| Query goal: | Find updatable views that restrict rows, i.e., have WHERE clause, but do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT table_schema AS view_schema, table_name AS view_name, is_updatable, is_insertable_into FROM Information_schema.views WHERE (is_insertable_into='YES' OR is_updatable='YES') AND check_option='NONE' AND view_definition~*'WHERE[[:space:]]' AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY table_schema, table_name; |
| Collection name | Collection description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
| Category name | Category description |
|---|---|
| Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
| Security | Queries of this category provide information about the security measures. |