1

Using the Postgres information_schema or system catalogs, is it possible to determine what table columns are referenced by the columns in a view?

information_schema.view_column_usage provides information on columns that are used in a view, not necessarily selected as a column.

pg_class and pg_attribute provide some info, but there doesn't seem to be a way to map columns from a view where an alias has been used.

Is is it possible to get the column references of the selected columns in a view?

1

PostgreSQL doesn't track which view column depends on which object. It is the view as a whole that is tracked.

But PostgreSQL tracks on which table columns (and other objects) a view depends: Each view is related to its query rewrite rule in pg_rewrite, and the query rewrite rule has a dependency on the table columns (and other objects) used in the view. These dependencies are tracked in pg_depend.

Putting the pieces of the puzzle together is straightforward, but takes a while. I have written a blog about this recently.

| improve this answer | |
  • I read your blog, very informative, I understand a bit more about view dependencies. But the problem remains, all dependencies of pg_rewrite are listed regardless of whether or not they are in the select list of the view. It seems like the only way to map view columns to their source columns is to somehow query ev_action in pg_rewrite – oorst Sep 24 '19 at 23:47
  • A view colum does not necessarily relate to a table column. Consider CREATE VIEW xy AS SELECT count(*) FROM a JOIN b USING (c). Yes, you'd have to analyze ev_action, but that would mean to write a C function that uses a tree walker. – Laurenz Albe Sep 25 '19 at 1:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.