The list of all the queries

Updatable views that have not been turned to read only

Query goal: Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Make data modifications by using database routines and prevent data modifications through views. Thus, use DO INSTEAD NOTHING rules or INSTEAD OF triggers to prevent data modifications through a view that is theoretically updatable. Essentially it means implementing WITH READ ONLY constraint.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
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))
SELECT schema, view, 'INSERT is permitted' AS possible_problem
FROM updatable_views
WHERE is_insertable_into='YES'
AND is_trigger_insertable_into='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]INSERT.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
UNION SELECT schema, view, 'UPDATE is permitted' AS possible_problem
FROM updatable_views
WHERE is_updatable='YES'
AND is_trigger_updatable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]UPDATE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
UNION SELECT schema, view, 'DELETE is permitted' AS possible_problem
FROM updatable_views
WHERE (is_insertable_into='YES' OR is_updatable='YES')
AND is_trigger_deletable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]DELETE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view, possible_problem;

SQL statements for generating SQL statements that help us to fix the problem

SQL queryDescription
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
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))
SELECT format('CREATE OR REPLACE RULE %2$s_insert AS ON INSERT TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE is_insertable_into='YES'
AND is_trigger_insertable_into='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]INSERT.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent INSERT operations
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
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))
SELECT format('CREATE OR REPLACE RULE %2$s_update AS ON UPDATE TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE is_updatable='YES'
AND is_trigger_updatable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]UPDATE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent UPDATE operations
WITH updatable_views AS (SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into
FROM Information_schema.views
WHERE (is_insertable_into='YES' OR is_updatable='YES') 
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))
SELECT format('CREATE OR REPLACE RULE %2$s_delete AS ON DELETE TO %1$I.%2$I DO INSTEAD NOTHING;', schema, view) AS statements
FROM updatable_views
WHERE (is_insertable_into='YES' OR is_updatable='YES')
AND is_trigger_deletable='NO'
AND (schema, view) NOT IN (SELECT schemaname, tablename    
FROM pg_rules
WHERE definition~'ON[[:space:]]DELETE.*DO[[:space:]]INSTEAD[[:space:]]NOTHING')
ORDER BY schema, view;
Prevent DELETE operations

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, 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 .

Categories where the query belongs to

Category nameCategory description
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.

Reference materials for further reading

Reference
https://www.postgresql.org/docs/current/sql-createrule.html
https://www.postgresqltutorial.com/postgresql-views/postgresql-updatable-views/

The list of all the queries