Search code examples
sqlpostgresqlinformation-schema

Get view projections from information_schema

I have a simple view as following:

CREATE VIEW AS
(
    SELECT identifier AS id FROM persons
);

Does the information_schema views provide functionality to find out that column identifier from table persons is projected as column id in the view?

The view information_schema.view_column_usage only shows that column identifier from persons is used, but not that it is projected into id in the view.

Is there something else I could use from information_schema?

Solution

  • It is not stored anywhere in information_schema.

    Of course it has to be stored somewhere, but that is in the ev_action column of the pg_rewrite system catalog, in the row for the _RETURN rule of the view.

    That column has the internal data type pg_node_tree that stores the parsed form of a query, so there is no way to analyze it from SQL.