Re: Determining a table column by the view column.

[raghu ram <raghuchennuru@xxxxxxxxx>]

On Tue, Aug 14, 2012 at 7:48 PM, Dmitriy Igrishin <dmitigr@xxxxxxxxx> 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 = "">

                AND d1.refclassid ='pg_class'::regclass

                AND d2.classid = "">

                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/