On Tue, Aug 14, 2012 at 7:48 PM, Dmitriy Igrishin
<dmitigr@gmail.com> wrote:
Hey all,
Is there way to determine a table column referenced by
a view column via SQL?
I want to create an universal function to determine
mandatoriness of some column of the view (i.e.
check the not null constraint of underlying table column).
Below query will find out the list of columns those have rules or views ?
SELECT d1.refobjid::regclass AS table,
a.attname AS column,
d2.refobjid::regclass AS "referenced by view"
FROM pg_depend d1
JOIN pg_depend d2 ON (d1.objid = d2.objid
AND d1.classid = 'pg_rewrite'::regclass
AND d1.refclassid ='pg_class'::regclass
AND d2.classid = 'pg_rewrite'::regclass
AND d2.refclassid = 'pg_class'::regclass
AND d2.deptype='i')
JOIN pg_attribute a ON (d1.refobjid = a.attrelid
AND d1.refobjsubid = a.attnum)
WHERE d1.refobjid='base_table'::regclass
ORDER BY 1, 2, 3;
--
Thanks & Regards,
Raghu Ram
EnterpriseDB Corporation
Blog:http://raghurc.blogspot.in/