Postgres Pro
Downloads
Home   >   mailing lists

Thread: Determining a table column by the view column.

Determining a table column by the view column.

From
Dmitriy Igrishin
Date:
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).

Thanks.

--
// Dmitriy.


Re: Determining a table column by the view column.

From
raghu ram
Date:


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/



Re: Determining a table column by the view column.

From
John R Pierce
Date:
On 08/14/12 7:18 AM, Dmitriy Igrishin 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).


note that a view column can be the result of a function or operation,
perhaps on fields of several columns from several tables being joined in
the view.   how would your function cope with something like this?




--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast