Complex enhancement/refactoring of https://gist.github.com/mateuszwenus/11187288
See related topics https://wiki.postgresql.org/wiki/Todo#Views_and_Rules
Recursively backup all dependent views, then modify base tables, then recreate all backuped views
Feature | View | Materialized View | Comment |
---|---|---|---|
Create view | Yes | Yes | With reloptions, tablespace, (no) data |
Create index | N/A | Yes | |
Alter owner to | Yes | Yes | |
Create rule | Yes | Yes | |
Comment on view | Yes | Yes | |
Comment on view column | Yes | Yes | |
Grant privilege on view | Yes | Yes | With grant options |
Grant privilege on view column | Yes | Yes | With grant options |
Create policy | N/A | N/A |
select public.deps_save_and_drop_dependencies(
'public',
'my_table',
'{
"dry_run": true,
"verbose": false,
"populate_materialized_view": false,
}'
);
-- alter my_table...
select public.deps_restore_dependencies(
'public',
'my_table',
'{
"dry_run": true,
"verbose": false
}'
);
-
deps_save_and_drop_dependencies
dry_run
Run without actually dropping dependenciesverbose
Show debug logpopulate_materialized_view
Enable or disable materialized view refresh-on-create viaWITH [NO] DATA
flag
-
deps_restore_dependencies
dry_run
Run without actually executing ddl statementsverbose
Show debug log