Re: Constraints of view attributes

<51183341(at)gmx(dot)at> writes:
> create table t1 (f1 text not null);
> create table t2 (f2 text);
> create view v as (select t1.f1, t2.f2 from t1, t2);

> Given v, How can a find out the t1.f1 has a not null constraint.

I assume what you actually mean is you want to know whether the
view v will always produce a non-null in that column. That's not
that easy. It is true in the above example, but consider for instance

create view v as select t1.f1, t2.f2 from t2 left join t1 on ...

With the outer join, that view column could produce nulls despite
the NOT NULL constraint on t1. There are other constructs such
as GROUPING SETS that break the equivalence, too. So you'd need a
fairly careful inspection of the view parsetree not only to find
which table is referenced, but whether there's something in the
view that defeats the deduction you'd like to make. There isn't
anything in Postgres right now that makes that type of inference,
let alone a way to export it to userland.

regards, tom lane