Re: 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,
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.

Reagards,
Bartek