Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables.
Type
Sofware measure (Numeric values (software measures) about the database)
WITH derived_tables AS (SELECT DISTINCT
source_ns.nspname as table_schema,
source_table.relname as table_name,
dependent_ns.nspname as view_schema,
dependent_view.relname as view_name,
CASE WHEN dependent_view.relkind='v' THEN 'VIEW'
WHEN dependent_view.relkind='m' THEN 'MATERIALIZED VIEW' END AS type
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE dependent_view.relkind IN ('m','v')
AND dependent_ns.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(dependent_ns.nspname ||'.'|| dependent_view.relname<>source_ns.nspname||'.'||source_table.relname))
SELECT view_schema, view_name, type, Count(DISTINCT table_schema || '.' || table_name) AS nr_of_tables
FROM derived_tables
GROUP BY view_schema, view_name, type
ORDER BY Count(*) DESC, type, view_schema, view_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.