Constraints of view attributes

On 05/11/2017 12:24 PM, 51183341@gmx.at wrote:

Hi,

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 can easily find this for t1 in the system catalog, but not for v.
I learned on IRC today that this should be somewhere in pg_rewrite but
where exactly and how do I get this information? Parse rewrite
query_tree? How?

SELECT
table_name, column_name, is_nullable
FROM
information_schema.columns
WHERE
table_name IN (
SELECT
table_name
FROM
information_schema.view_column_usage
WHERE
view_name = 'v'
AND column_name = 'f1')
AND column_name = 'f1';

table_name | column_name | is_nullable
------------+-------------+-------------
t1 | f1 | NO

--
Greg

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

<51183341@gmx.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general