Postgresql – Postgres view column references

information-schemapostgresql

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?

Best Answer

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.

Related Question