1
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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
rock_techie
  • 41
  • 5
  • To select the columns from the table, you need to specify the tables in `FROM` clause. Can you please explain a bit more what you are trying to achieve? Also, I don't think there is a `dependent_view` column in `pg_class`. – Jyoti Dhiman Aug 31 '18 at 04:53
  • I need a query which would return dependant views on a table and it's column..(when tablename and columnname is passed)...it should give view definition,tablename,viewname,columname etc...i have also edited and added metadata tables in above main query – rock_techie Aug 31 '18 at 04:58
  • See here: http://mwenus.blogspot.de/2014/04/db-view-dependencies-in-postgresql.html but I don't think the information is available on column level –  Aug 31 '18 at 07:52
  • @a_horse_with_no_name...i had checked that link too but didnt helped..unfortunately i could get 2 queries now(one for view dependant definition) i.e first one and second one for rest of the information like (column name,tablename,view name)...how can i merge these 2 queries otherwise? that would also solve my issue – rock_techie Aug 31 '18 at 08:17

1 Answers1

0

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228