Re: Determining a table column by the view column.

On Tue, Aug 14, 2012 at 7:48 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)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/