Thread: Display View Columns and Their Source Tables and Columns

I'm so close but I can't quite figure out how to match view columns to 
their source columns in a query.  Looks like I might need yet another 
table to join that makes that match, but I'm not having any success 
finding such a bridge.  Matching views to their source tables works well 
enough.  What am I missing?  Is there a better approach?

I would welcome any comments or leads that you have.

Thanks for your help!

Sue

Here is what I have so far:

select vcu.view_name     view_name
       ,c.column_name     view_column
       ,vcu.table_schema  source_schema
       ,vcu.table_name    source_table
       ,vcu.column_name   source_column
       ,c.is_updatable    is_updatable
   from information_schema.view_column_usage  vcu
       ,information_schema.columns c
  where vcu.view_schema = 'devops'
    and vcu.table_schema in ('devops','chief','store')
    and vcu.view_schema = c.table_schema
    and vcu.view_name = c.table_name
    and    ************************ Help! *****************
  order by vcu.view_name
          ,vcu.table_name
          ,c.column_name
;


-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

Hi,

I don't know if it is possible ...
the only way I found seems to use pg_depend and pg_rewrite
as described here
https://pgdba.org/post/2018/04/dependency_ladder/

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Susan Hurst wrote:
> I'm so close but I can't quite figure out how to match view columns to 
> their source columns in a query.

There is no simple way to find which view column matches which column
in the original table.  Don't forget that the column could be defined
as an expression that involves several columns of the base table.

You'd have to parse pg_rewrite.ev_action.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com