Any thoughts on this would be welcome. This is something which I would personally find exceptionally valuable; if there are problems with the idea, I'd like to know. As well, if my description isn't clear enough I'd be happy to explain.
I think the problem with this is that you cannot be certain at any point in time that a query such as:
SELECT u.name, p.number FROM phone_numbers p JOIN users u ON p.user_id = u.id;
will never eliminate tuples that don't match the join condition, *even if there is a foreign key defined on the join condition*.
The reason for this is that referenced tables are not updated immediately, they're only updated by triggers at the end of the statement, or transaction, depending if the foreign key is DEFERRED or not.
The problem is that an UPDATE/DELETE could take place which causes the foreign key to be violated and you may try and perform an UPDATE to the view before the foreign key is cascaded by the trigger at end of statement/transaction. Remember that a statement could execute inside of a volatile function being called by some outer query.
If foreign keys were updated immediately, like indexes normally are, then this wouldn't be an issue.
I've attached a file with 2 examples of when this can happen.
