Get Origin of Column of View in Postgresql

447 views Asked by At

Suppose I have two tables

create table user_information1 (
  user_id int not null,
  age int not null,
  primary key (user_id),
);
create table user_information2 (
  user_id int not null,
  height decimal(1,2) not null,
  primary key (user_id),
);

and a view

create view collected_information as
  select a.customer_id AS id,
    a.age,
    b.age
   from user_information1 a,
   user_information2 b
where 
a.user_id = b.user_id;

With pg_depend it is possible to see which tables the view depends on. Can I also find in the pg_catalog the information which column comes from which table? This means for example the age column comes from user_information1 without assuming that the columns have the same name and I look for columns with the same name. The select I use to find the depending views is the following:

SELECT
    e.nspname,
    c.relname,
    f.nspname,
    d.relname
FROM
    pg_depend    a,
    pg_rewrite   b,
    pg_class     c,
    pg_class     d,
    pg_namespace e,
    pg_namespace f
WHERE
    c.oid = b.ev_class
AND b.oid = a.objid
AND c.relkind = 'v'
AND a.classid = 'pg_rewrite'::regclass
AND a.refclassid = 'pg_class'::regclass
AND a.deptype = 'n'
AND e.oid = c.relnamespace
AND f.oid = d.relnamespace
AND a.refobjid = d.oid
AND c.oid <> d.oid;
0

There are 0 answers