更改视图中使用的 PostgreSQL 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8524873/
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
Change PostgreSQL columns used in views
提问by Larsenal
I would like PostegreSQL to relax a bit. Every time I want to change a column used in a view, it seems I have to drop the view, change the field and then recreate the view. Can I waive the extra protection and just tell PostgreSQL to let me change the field and then figure out the adjustment to the view?
我想让 PostegreSQL 放松一点。每次我想更改视图中使用的列时，似乎我必须删除视图，更改字段，然后重新创建视图。我可以放弃额外的保护，只告诉 PostgreSQL 让我改变字段，然后找出对视图的调整吗？
Clarification:I understand what a view is. In fact, it's because the view is like a subquery that I wish I could just change the underlying tables and have the view pick up the change.
澄清：我理解什么是视图。事实上，这是因为视图就像一个子查询，我希望我可以只更改基础表并让视图接收更改。
Let's say I have the following:
假设我有以下内容：
CREATE TABLE monkey
(
"name" character varying(50) NOT NULL,
)
CREATE OR REPLACE VIEW monkey_names AS
SELECT name
FROM monkey
I really just want to do the following in a migration script withouthaving to drop and recreate the view.
我真的只想在迁移脚本中执行以下操作，而不必删除并重新创建视图。
ALTER TABLE monkey ALTER COLUMN "name" character varying(100) NOT NULL
回答by Erwin Brandstetter
Permanent solution for this case
此案例的永久解决方案
To avoid the problem altogether use the data type textor varchar/ character varyingwithout a length specifier instead of character varying(n). Read about these data types in the manual.
为了完全避免这个问题，请使用数据类型text或varchar/character varying不带长度说明符而不是character varying(n). 在手册中阅读这些数据类型。
CREATE TABLE monkey(name text NOT NULL)
If you really want to enforce a maximum length, create a CHECKconstraint:
如果您真的想强制执行最大长度，请创建一个CHECK约束：
ALTER TABLE monkey
ADD CONSTRAINT monkey_name_len CHECK (length(name) < 101);
You can change or drop that constraint any time without touching depending objects like views and without forcing Postgres to write new rows in the table due to the change of type (which isn't always necessary any more in modern version of Postgres).
您可以随时更改或删除该约束，而无需接触依赖对象（如视图），并且不会由于类型的更改而强制 Postgres 在表中写入新行（这在现代版本的 Postgres 中不再总是必要的）。
Detailed explanation
详细说明
As proposed by @Michael, I add some more general information:
正如@Michael 所提议的，我添加了一些更一般的信息：
A view in PostgreSQL is not just an "alias to subquery". Views are implemented as special tables with a rule ON SELECT TO my_view DO INSTEAD. (That's why you can alter views with an ALTER TABLEcommand.) You can GRANTprivileges to it, add comments or even define column defaults (useful for a rule ON INSERT TO my_view DO INSTEAD...). Read more in the manual hereor here.
PostgreSQL 中的视图不仅仅是“子查询的别名”。视图被实现为带有规则的特殊表ON SELECT TO my_view DO INSTEAD。（这就是您可以使用ALTER TABLE命令更改视图的原因。）您可以GRANT为其授予权限、添加注释甚至定义列默认值（对规则有用ON INSERT TO my_view DO INSTEAD...）。在此处或此处阅读手册中的更多信息。
If you change underlying objects, you need to change the defining query of any depending view, too. The ALTER VIEWstatement can only change auxiliary attributes of a view. Use CREATE OR REPLACE VIEWto change the query - it will preserve any additional attributes.
如果您更改底层对象，您也需要更改任何依赖视图的定义查询。该ALTER VIEW语句只能更改视图的辅助属性。使用CREATE OR REPLACE VIEW更改查询-它将保留任何附加属性。
However, if you want to change data types of resulting columns (like in the case at hand), CREATE OR REPLACE VIEWis not possible. You have to DROPthe old and CREATEa new view. This will never delete any data of the underlying tables. It willdrop any additional attributes of the view, though, which have to be recreated, too.
但是，如果您想更改结果列的数据类型（如手头的情况），CREATE OR REPLACE VIEW则是不可能的。你必须对DROP旧的和CREATE新的看法。这永远不会删除基础表的任何数据。但是，它会删除视图的任何其他属性，这些属性也必须重新创建。
