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;