如何更改 PostgreSQL 中的视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3593568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to ALTER a view in PostgreSQL
提问by mevdiven
PostgreSQL does not allow altering a view (i.e. adding column, changing column orders, adding criterie etc.) if it has dependent objects. This is really getting annoying since you have to write a script to:
如果视图具有依赖对象，PostgreSQL 不允许更改视图（即添加列、更改列顺序、添加条件等）。这真的很烦人，因为您必须编写一个脚本来：
- Drop all the dependent objects,
- Alter the view,
- Recreate all the dependent objects back again.
- 删除所有依赖对象，
- 换个角度，
- 再次重新创建所有依赖对象。
I understand that postgreSQL developers have very reasonable concerns to prevent altering views. But do you guys have any scripts/shot-cuts to do all those manual stuff in a single run?
我知道 postgreSQL 开发人员有非常合理的担忧来防止更改视图。但是你们有任何脚本/镜头可以在一次运行中完成所有这些手动操作吗？
采纳答案by Frank Heikens
Adding new columns isn't a problem, changing datatypes or changing the order of the columns, that's where you get problems.
添加新列不是问题，更改数据类型或更改列的顺序，这就是您遇到问题的地方。
Don't change the order, it's not that important anyway, just change your query:
SELECT a, b FROM view_name;
SELECT b, a FROM view_name;
When you have to change a datatype of a column, you have to check the depend objects as well. These might have problems with this new datatype. Just get the definition of this object and recreate after the changes. The information_schema and pg_catalog help you out.
- Make all changes within a single transaction.
不要更改顺序，反正这并不重要，只需更改您的查询：
SELECT a, b FROM view_name;
SELECT b, a FROM view_name;
当您必须更改列的数据类型时，您还必须检查相关对象。这些新数据类型可能存在问题。只需获取此对象的定义并在更改后重新创建。information_schema 和 pg_catalog 可以帮助您。
- 在单个事务中进行所有更改。
回答by Hartmut P.
If I place a addtional "drop view xyz; commit;" before the "create or replace view xyz as ..." statement, at least in many cases I resolve the blocking problem described above.
如果我放置一个附加的“drop view xyz; commit;” 在“创建或替换视图 xyz 为...”语句之前，至少在许多情况下我解决了上述阻塞问题。
