Have good support for Views with non-nullable foreign key relationships

[brandonchinn178]

Exposing non-nullable table columns in views has been asked before (https://github.com/orgs/supabase/discussions/13279, https://github.com/orgs/supabase/discussions/14151), and the current guidance is "It's a Postgres limitation, we have to workaround it". However, even if Postgres doesn't support it, it would be nice if supabase's type generation had a special carve-out for it.

Our current workaround is to cast the columns into non-nullable types like

CREATE DOMAIN uuid_not_null AS UUID NOT NULL;

CREATE TABLE all_posts (
  id UUID PRIMARY KEY,
  author UUID NOT NULL REFERENCES users (id)
);

CREATE VIEW active_posts AS
SELECT
  id::uuid_not_null AS id,
  author::uuid_not_null AS author
FROM all_posts
WHERE deleted_at IS NULL;

This works, but now the foreign key relationship is destroyed and PostgREST is unable to find the relationship between active_posts and users.