Обсуждение: Determining a table column by the view column.

Поиск
Список
Период
Сортировка

Determining a table column by the view column.

От
Dmitriy Igrishin
Дата:
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.

От
raghu ram
Дата:


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.

От
John R Pierce
Дата:
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