Working with View Dependencies

    As the previous example shows, altering a table can be quite a challenge if there is a deep hierarchy of views, because you have to create the views in the correct order. You cannot create a view unless all the objects it requires are present.

    You can use view dependency information when you want to alter a table that is referenced by a view. For example, you might want to change a table’s column data type from integer to bigint because you realize you need to store larger numbers. However, you cannot do that if there are views that use the column. You first have to drop those views, then change the column and then run all the CREATE VIEW statements to create the views again.

    The following example queries list view information on dependencies on tables and columns.

    The example output is based on the Example Data at the end of this topic.

    Also, you can use the first example query to find dependencies on user-defined functions (or procedures). The query uses the catalog table pg_class that contains information about tables and views. For functions, you can use the catalog table pg_proc to get information about functions.

    To find out which views directly depend on table t1, create a query that performs a join among the catalog tables that contain the dependency information, and qualify the query to return only view dependencies.

    1. SELECT v.oid::regclass AS view,
    2.   d.refobjid::regclass AS ref_object    -- name of table
    3.   -- d.refobjid::regproc AS ref_object  -- name of function
    4. FROM pg_depend AS d      -- objects that depend on a table
    5.   JOIN pg_rewrite AS r  -- rules depending on a table
    6.      ON r.oid = d.objid
    7.   JOIN pg_class AS v    -- views for the rules
    8.      ON v.oid = r.ev_class
    9. WHERE v.relkind = 'v'         -- filter views only
    10.   -- dependency must be a rule depending on a relation
    11.   AND d.classid = 'pg_rewrite'::regclass 
    12.   AND d.deptype = 'n'         -- normal dependency
    13.   -- qualify object
    14.   AND d.refclassid = 'pg_class'::regclass   -- dependent table
    15.   AND d.refobjid = 't1'::regclass
    16.   -- AND d.refclassid = 'pg_proc'::regclass -- dependent function
    17.   -- AND d.refobjid = 'f'::regproc
    18. ;
    19.     view    | ref_object
    20. ------------+------------
    21.  v1         | t1
    22.  v2         | t1
    23.  v2         | t1
    24.  mytest.vt1 | t1
    25.  mytest.v2a | t1
    26.  mytest.v2a | t1
    27. (7 rows)

    The query performs casts to the regclass object identifier type. For information about object identifier types, see the PostgeSQL documentation on Object Identifier Types.

    In some cases, the views are listed multiple times because the view references multiple table columns. You can remove those duplicates using DISTINCT.

    You can alter the query to find views with direct dependencies on the function f.

    • In the SELECT clause replace the name of the table d.refobjid::regclass as ref_object with the name of the function d.refobjid::regproc as ref_object
    • In the WHERE clause replace the catalog of the referenced object from for tables, to d.refclassid = 'pg_proc'::regclass for procedures (functions). Also change the object name from d.refobjid = 't1'::regclass to d.refobjid = 'f'::regproc
    • In the WHERE clause, replace the name of the table refobjid = 't1'::regclass with the name of the function refobjid = 'f'::regproc.

    In the example query, the changes have been commented out (prefixed with --). You can comment out the lines for the table and enable the lines for the function.

    Finding Direct Dependencies on a Table Column

    This query finds the views that depend on the column id of table t1:

    If you have created views in multiple schemas, you can also list views, each view’s schema, and the table referenced by the view. The query retrieves the schema from the catalog table pg_namespace and excludes the system schemas pg_catalog, information_schema, and gp_toolkit. Also, the query does not list a view if the view refers to itself.

    1. SELECT v.oid::regclass AS view,
    2.   ns.nspname AS schema,       -- view schema,
    3.   d.refobjid::regclass AS ref_object -- name of table
    4. FROM pg_depend AS d            -- objects that depend on a table
    5.   JOIN pg_rewrite AS r        -- rules depending on a table
    6.     ON r.oid = d.objid
    7.   JOIN pg_class AS v          -- views for the rules
    8.     ON v.oid = r.ev_class
    9.   JOIN pg_namespace AS ns     -- schema information
    10.     ON ns.oid = v.relnamespace
    11. WHERE v.relkind = 'v'          -- filter views only
    12.   -- dependency must be a rule depending on a relation
    13.   AND d.classid = 'pg_rewrite'::regclass 
    14.   AND d.refclassid = 'pg_class'::regclass  -- referenced objects in pg_class (tables and views)
    15.   AND d.deptype = 'n'         -- normal dependency
    16.   -- qualify object
    17.   AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit') -- system schemas
    18.   AND NOT (v.oid = d.refobjid) -- not self-referencing dependency
    19. ;
    20.     view    | schema | ref_object
    21. ------------+--------+------------
    22.  v1         | public | t1
    23.  v2         | public | t1
    24.  v2         | public | t1
    25.  v2         | public | v1
    26.  v3         | public | t1
    27.  vm1        | public | mytest.tm1
    29.  mytest.v2a | mytest | t1
    30.  mytest.v2a | mytest | t1
    31.  mytest.v2a | mytest | v1
    32. (11 rows)

    Listing View Definitions

    This query lists the views that depend on t1, the column referenced, and the view definition. The CREATE VIEW command is created by adding the appropriate text to the view definition.

    This CTE query lists information about views that reference another view.

    The WITH clause in this CTE query selects all the views in the user schemas. The main SELECT statement finds all views that reference another view.

    1. WITH views AS ( SELECT v.relname AS view,
    2.   d.refobjid AS ref_object,
    3.   v.oid AS view_oid,
    4.   ns.nspname AS namespace
    5. FROM pg_depend AS d
    6.   JOIN pg_rewrite AS r
    7.     ON r.oid = d.objid
    8.   JOIN pg_class AS v
    9.     ON v.oid = r.ev_class
    10.   JOIN pg_namespace AS ns
    11.     ON ns.oid = v.relnamespace
    12. WHERE v.relkind = 'v'
    13.   AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit') -- exclude system schemas
    14.   AND d.deptype = 'n'    -- normal dependency
    15.   AND NOT (v.oid = d.refobjid) -- not a self-referencing dependency
    16.  )
    17. SELECT views.view, views.namespace AS schema,
    18.   views.ref_object::regclass AS ref_view,
    19.   ref_nspace.nspname AS ref_schema
    20. FROM views 
    21.   JOIN pg_depend as dep
    22.     ON dep.refobjid = views.view_oid 
    23.   JOIN pg_class AS class
    24.     ON views.ref_object = class.oid
    25.   JOIN  pg_namespace AS ref_nspace
    26.       ON class.relnamespace = ref_nspace.oid
    27.   WHERE class.relkind = 'v'
    28.     AND dep.deptype = 'n'    
    29. ; 
    30.  view | schema | ref_view | ref_schema
    31. ------+--------+----------+------------
    32.  v2a  | mytest | v1       | public

    Example Data