CODEWITHSUNDEEP
postgresqlfunctionviewalter-tabledatabase-optimization
Sridas D
Sridas D

Reputation: 43

Is there any way to list all the views related to a table in the existing postgres schema

I got a Postgres database with multiple schemas. I'm trying to optimise my database tables with optimal data types. more often I end with the error

cannot alter the type of a column used by a view

while using the query alter table schema.tbl_name alter column column_name type varchar(5) using column_name::varchar(5);

Is there any way (function) that I could list all the views related to the table?

Upvotes: 4

Views: 3860

Answers (3)

Michal Charemza
Michal Charemza

Reputation: 27012

This is doable, but it's tricky for 3 reasons:

  1. PostgreSQL supports transitive/higher level/nested/stacked views (views on the views, and views on the views on the views etc...)
  2. PostgreSQL also doesn't forbid cycles of views - views that depend on each other - from being created (I was only recently told this by a colleague - I was surprised!)
  3. In a big database (thousands+ of tables) it's easy to get into performance problems

But wonderfully there is a fairly efficient way that just queries one table, pg_depend, by leveraging the slightly magical regclass Object identifier type, and by using a recursive CTE

-- A view is a row in pg_rewrite, and a corresponding set of rows in pg_depend where:
--
-- 1. There is a row per column that links pg_rewrite with all the columns of all the tables (or
--    other views) in pg_class that the view queries
-- 2. There is also one "internal" row linking the pg_rewrite row with its pg_class entry for the
--    view itself. This is can be used to then find the views that query the view, the fully
--    qualified name of the view, as well as its definition.
--
-- So to find all the direct views for a table, we find the rows in pg_depend for 1., and then self
-- join onto pg_depend again for 2. (making sure to not choose the same rows again). To then find
-- all the indirect views, we do the same thing repeatedly using a recursive CTE.
--
-- PostgreSQL does not forbid cycles of views, so we have to make sure we never add a view that has
-- already been added.

-- Non recursive term: direct views on the table
WITH RECURSIVE views_on_table AS (
    SELECT
        view_depend.refobjid, ARRAY[view_depend.refobjid] AS path
    FROM
        pg_depend AS view_depend, pg_depend AS table_depend 
    WHERE
        -- Find the rows in pg_rewrite for 1: rows that link the table with the views that query it
        table_depend.classid = 'pg_rewrite'::regclass
        AND table_depend.refclassid = 'pg_class'::regclass
        AND table_depend.refobjid = 'schema_name.table_name'::regclass

        -- And we can find 2: the pg_class entry for each of the views themselves
        AND view_depend.classid = 'pg_rewrite'::regclass
        AND view_depend.refclassid = 'pg_class'::regclass
        AND view_depend.deptype = 'i'
        AND view_depend.objid = table_depend.objid
        AND view_depend.refobjid != table_depend.refobjid

    UNION

    -- Recursive term: views on the views
    SELECT
        view_depend.refobjid, views_on_table.path || ARRAY[view_depend.refobjid]
    FROM
        views_on_table, pg_depend AS view_depend, pg_depend AS table_depend 
    WHERE
        -- Find the rows in pg_rewrite for 1: rows that link the views with other views that query it
        table_depend.classid = 'pg_rewrite'::regclass
        AND table_depend.refclassid = 'pg_class'::regclass
        AND table_depend.refobjid = views_on_table.refobjid
        
        -- And we can find 2: the pg_class entry for each of the views themselves
        AND view_depend.classid = 'pg_rewrite'::regclass
        AND view_depend.refclassid = 'pg_class'::regclass
        AND view_depend.deptype = 'i' 
        AND view_depend.objid = table_depend.objid
        AND view_depend.refobjid != table_depend.refobjid

        -- Making sure to not get into an infinite cycle
        AND NOT view_depend.refobjid = ANY(views_on_table.path)
)

SELECT views_on_table.refobjid::regclass::text AS fully_qualified_name
FROM views_on_table

This was inspired by the definition of information_schema.view_table_usage, and the discussion at https://github.com/uktrade/pg-bulk-ingest/pull/235

Upvotes: 0

RonJohn
RonJohn

Reputation: 425

Based on Bohemian's answer, here's a view and subsequent query that will generate all views for a certain table.

It won't catch views that depend on views which depend on your table, though.

CREATE OR REPLACE VIEW dba.v_views_on_table AS
select vtu.view_schema
     , vtu.view_name 
     , vtu.table_schema
     , vtu.table_name
     , v.view_definition
from information_schema.view_table_usage vtu
join information_schema.views v on (vtu.view_schema = v.table_schema
  and vtu.view_name = v.table_name)
where vtu.table_schema not in ('information_schema', 'pg_catalog')
order by vtu.view_schema, vtu.view_name
;

\pset format unaligned
\pset tuples_only 
\o sometable_views.sql
select format('CREATE VIEW %s AS %s %s'
             , u.view_schema||'.'||u.view_name
             , E'\n'
             , v.view_definition)
from information_schema.view_table_usage u
join information_schema.views v on u.view_schema = v.table_schema
  and u.view_name = v.table_name
where u.table_schema||'.'||u.table_name = 'someschema.sometable'
order by u.view_schema, u.view_name
;

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

Use this query:

select
  u.view_schema schema_name,
  u.view_name,
  u.table_schema referenced_table_schema,
  u.table_name referenced_table_name,
  v.view_definition
from information_schema.view_table_usage u
join information_schema.views v on u.view_schema = v.table_schema
  and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema, u.view_name

Credit: Dataedo.com's article List tables used by a view in PostgreSQL database

Upvotes: 8

Related Questions