Re: Functional dependencies and GROUP BY - for subqueries

> Can you please elaborate, why would it be a disaster?
>
> Consider that we've done
>
> create table t1 (id int primary key, ... other stuff ...);
> create view v1 as select * from t1;
> create view v2 as select * from v1 group by id;
>
> Currently, v2 would be rejected but you would like to make it legal.
> Now consider
>
> alter table t1 drop primary key;
>
> This ALTER would have to be rejected, or else (with CASCADE) lead to
> dropping v2 but not v1. That's pretty ugly action-at-a-distance
> if you ask me. But worse, consider
>
> create or replace view v1 as select * from t2;
>
> where t2 exposes the same columns as t1 but lacks a primary-key
> constraint on id. This likewise would need to invalidate v2. We lack
> any dependency mechanism that could enforce that, and it seems seriously
> ugly that such a view redefinition could fail at all. (Note for
> instance that there's no place to put a CASCADE/RESTRICT option in
> CREATE OR REPLACE VIEW.)
>
> So quite aside from the implementation difficulties of looking into
> views for such constraints, I don't think the behavior would be pleasant
> if we did do it. Views are not supposed to expose properties of the
> underlying tables.
>
>
Thanks for the explanation.

Is there any reason why do we want to check the functional dependencies at
the time of parsing and not after rewrite? Obviously, by doing so, we will
allow creation of certain views which will start throwing errors after the
underlying table changes the primary key. Is it mandatory that we throw
"functional dependency" related errors at the time of creation of views?

> regards, tom lane
>

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company