select 'CREATE VIEW ' || views || E' AS \n'
|| pg_get_viewdef(views, true) as view_definition
from (
select distinct(r.ev_class::regclass) as "views",
pg_class.relname as table_name,
pg_attribute.attname as column_name,
pg_class.dependent_view as rel_obj_name
from pg_depend d join pg_rewrite r on r.oid = d.objid
where refclassid = 'pg_class'::regclass
and refobjid = 'DBNAME.TABLENAME'::regclass
and classid = 'pg_rewrite'::regclass
and pg_get_viewdef(r.ev_class, true) ~ 'COLUMNNAME') as x;
I want to run this query and extract viewdefinition(Dependant) on a table and column and other details.
Currently it gives below error:
ERROR: missing FROM-clause entry for table "pg_class" LINE 3: ...(select distinct(r.ev_class::regclass) as "views",pg_class.r... SQL state: 42P01 Character: 162
Metadata tables used in the above query are:
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+-----------+-----------+----------+---------
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |
relfilenode | oid | | not null |
reltablespace | oid | | not null |
relpages | integer | | not null |
reltuples | real | | not null |
relallvisible | integer | | not null |
reltoastrelid | oid | | not null |
relhasindex | boolean | | not null |
relisshared | boolean | | not null |
relpersistence | "char" | | not null |
relkind | "char" | | not null |
relnatts | smallint | | not null |
relchecks | smallint | | not null |
relhasoids | boolean | | not null |
relhaspkey | boolean | | not null |
relhasrules | boolean | | not null |
relhastriggers | boolean | | not null |
relhassubclass | boolean | | not null |
relrowsecurity | boolean | | not null |
relforcerowsecurity | boolean | | not null |
relispopulated | boolean | | not null |
relreplident | "char" | | not null |
relfrozenxid | xid | | not null |
relminmxid | xid | | not null |
relacl | aclitem[] | | |
reloptions | text[] | | |
Table "pg_catalog.pg_depend"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
classid | oid | | not null |
objid | oid | | not null |
objsubid | integer | | not null |
refclassid | oid | | not null |
refobjid | oid | | not null |
refobjsubid | integer | | not null |
deptype | "char" | | not null |
Table "pg_catalog.pg_rewrite"
Column | Type | Collation | Nullable | Default
------------+--------------+-----------+----------+---------
rulename | name | | not null |
ev_class | oid | | not null |
ev_type | "char" | | not null |
ev_enabled | "char" | | not null |
is_instead | boolean | | not null |
ev_qual | pg_node_tree | | |
ev_action | pg_node_tree | |
Query 1:
SELECT dependent_ns.nspname as dependent_schema
, dependent_view.relname as dependent_view
, source_ns.nspname as source_schema
, source_table.relname as source_table
, pg_attribute.attname as column_name
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_attribute ON pg_depend.refobjid = pg_attribute.attrelid
AND pg_depend.refobjsubid = pg_attribute.attnum
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
source_ns.nspname = 'SCHEMANAME'
AND source_table.relname = 'TABLENAME'
AND pg_attribute.attnum > 0
AND pg_attribute.attname = 'COLUMNNAME'
ORDER BY 1,2;
Query 2:
select 'CREATE VIEW ' || views || E' AS \n'
|| pg_get_viewdef(views, true) as view_definition
from (select distinct(r.ev_class::regclass) as "views"
from pg_depend d join pg_rewrite r on r.oid = d.objid
where refclassid = 'pg_class'::regclass
and refobjid = 'SCHEMANAME.TABLENAME'::regclass
and classid = 'pg_rewrite'::regclass
and pg_get_viewdef(r.ev_class, true) ~ 'columnname') as x;
How to merge these two queries to get names and view definition from a single query?
I think this is what you are looking for:
SELECT v.relnamespace::regnamespace AS dependent_schema
, v.relname AS dependent_table
, t.relnamespace::regnamespace AS source_schema
, t.relname AS source_table
, a.attname AS column_name
, format(E'CREATE VIEW %s AS\n%s'
, v.oid::regclass, pg_get_viewdef(r.ev_class, true)) AS view_definition
FROM pg_class t
JOIN pg_attribute a ON a.attrelid = t.oid
JOIN pg_depend d ON d.refobjid = a.attrelid
AND d.refobjsubid = a.attnum
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class v ON v.oid = r.ev_class
WHERE t.oid = 'SCHEMANAME.TABLENAME'::regclass -- table name HERE
AND a.attname = 'COLUMNAME' -- column name HERE
AND NOT attisdropped -- no dropped cols
AND attnum > 0 -- no system cols
AND d.classid = 'pg_rewrite'::regclass -- it's a view
AND r.ev_type = '1' -- only SELECT
AND r.is_instead; -- INSTEAD rule
Simpler, faster and safer than either of your queries. But this is still building on implementation details that may change in the next major release. So don't rely on it.
The input ('SCHEMANAME.TABLENAME') optionally includes the schema name. Postgres defaults to the search_path for unqualified names.
The view definition uses v.oid::regclass, which schema-qualifies the view name depending on the current search_path. Adapt to your needs.
Related: