Given the folowing view definition

create or replace view projects as
select id, client_id as clientId from data.projects

Is it possible, knowing the view name and the view column to get the name of the original table and column names?

I know about information_schema.view_column_usage but it does not provide the column to column relation. My thinking is that although it might not be possible to find out using a query, the information is available somehow since the column clientId is autoupdatable and PG needs to somehow translate it to an insert on the original table.

Is there a public function similar to pg_column_is_updatable that would return the oid of the referenced column?

EDIT: The best i could come up with is this https://gist.github.com/ruslantalpa/2eab8c930a65e8043d8f

  • I would settle also for the name of the source table (invormation_schema.view_table_usage does not work since it also lists the tables that are used in where clause) Feb 26 '16 at 18:08
  • Just as suggestion: try explain verbose select client_id from projects. I am not big fun of views so I can not test it quickly but I believe that result contains info about what comes from where.
    – Abelisto
    Feb 26 '16 at 21:44
  • There is something there but it does not really help, i would have to resort to text parsing. I am trying to come up with a query like information_schema.view_column_usage but with an additional column that contains the name of the column in the view Feb 27 '16 at 22:35
  • This is related stackoverflow.com/questions/10048056/… but it's for sqlserver. There seems to be a relation between sqlserver sys and pg's pg_class schemas, but it's a bit hard for me to translate the query while not very familiar with sys or pg_class Feb 28 '16 at 8:04
  • If I understand something in DBMS... Views is not something static but just alias for subqueries. For example, create view v as select * from t and select * from v where id = 1 does not mean that DBMS builds the copy of table t and then searches in it for the condition provided. View usage can be investigated only in the context of the query using it. IMO.
    – Abelisto
    Feb 28 '16 at 8:23

Your Answer

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

Browse other questions tagged or ask your own question.