How do you automatically refresh a view in postgres? I want to add a script which automatically refreshes all views after a database migration. How could I achieve this? I know REFRESH MATERIALIZED VIEW exists, but what if you just want to refresh the definition of a normal view?
As for why I want this, well I have a view like
CREATE OR REPLACE VIEW MyView
SELECT *
FROM MyTable
WHERE...
Now I add another column to MyTable in a new migration, but the view will not contain the new column since it was defined before the new migration. So I wanted to add a deployment step where all the views definitions are refreshed on any new migration.
What can you recommend?
The way to deal like that is to drop and re-create all the affected views during the migration. Or even simpler: just drop and re-create all your views during a schema version change. That is simple, since you keep all your view definitions under version control.
An explanation why that is necessary: PostgreSQL doesn't store views as an SQL statement string, but it parses the query and stores the binary parse tree. Parsing resolves the * to all the column names at the time of the view creation, so if you add another column later, it won't have an effect on the view creation.
Since you complain about the bad developer experience: the benefit of that procedure is that PostgreSQL tracks dependencies between the view and its dependent objects and keeps you from dropping them or modifying them in a way that affects the view. This way, a view can never break and suddenly cause an error, just because one of its prerequisites is missing.