Find views that can accept data modification statements

Copy SQL

SELECT table_schema AS schema, table_name AS view, is_insertable_into, is_updatable, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into,
CASE WHEN (view_definition!~'(JOIN|GROUP BY|HAVING|LIMIT|OFFSET|FETCH FIRST|DISTINCT|WITH|UNION|INTERSECT|EXCEPT)'
AND view_definition!~*'(((array|json|jsonb|json_object|jsonb_object|range|range_intersect|string)_|(json_array|xml))agg|(sum|count|avg|min|max|over)[[:space:]]{0,1}[(])') THEN 'YES' ELSE 'NO' END AS is_the_view_by_default_updatable, check_option,
view_definition~*'WHERE[[:space:]]' AS has_where_clause
FROM Information_schema.views
WHERE (is_insertable_into='YES' 
OR is_updatable='YES'
OR is_trigger_updatable='YES' 
OR is_trigger_deletable='YES'
OR is_trigger_insertable_into='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)
ORDER BY table_schema, table_name;