Thread: View parsing
Hi all, I would like to be able to determine which tables each field of a view comes from. I have a view definition like SELECT tbl_a.fld_a, tbl_b.fld_b AS fld_e, function(c,d,f) as fld_c, (SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a) as fld_d FROM tbl_a inner join tbl_b on tbl_a.fld_e=tbl_b.fld_f WHERE cond_a; What I'd like to get to is a list of fields in the view indicating how each is calculated - without putting the definition into a horrible bunch of regexps. (Which would no doubt work but seems the wrong thing to do.) Something like this would be ideal: fld_a -> tbl_a.fld_a fld_e -> tbl_b.fld_b fld_c -> function(c,d,f) fld_d -> (SELECT fld_d FROM tbl_d WHERE tbl_d.fld_e=tbl_a.fld_a) My goal is to answer the question "in what other views might I find the data which I see as, say, fld_e?". Once I know where the data comes from I can dig through the pg_depends data and figure out other views which depend on that (table,column) tuple and go from there. Yours, Frank
Hi,
Reagards,
Bartek
how about this one?
SELECT n.nspname, c.relname, a.attname
FROM pg_depend d
INNER JOIN pg_class c ON (c.oid = refobjid)
INNER JOIN pg_attribute a ON (c.oid = a.attrelid AND d.refobjsubid = a.attnum)
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
INNER JOIN pg_rewrite rw ON (d.objid = rw.oid)
WHERE rw.ev_class = '"MySchema"."MyViewName"'::regclass
ORDER BY n.nspname, c.relname, a.attname
This will answer for all involved tables and columns - not only produced by a view, but also involved in joins and sub-queries.
Assumption: rules are deprecated and used only by PG internal engine (for views).
If this assumption is not correct You should narrow pg_rewrite results to find proper rule.
Bartek