postgresql 无法更新视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13151566/
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
Cannot update view?
提问by srinu
My site was developed using Drupal 6 running on a Postgresql 8.3 server on Ubuntu 11.10. Also webmin version 1.590.
我的网站是使用在 Ubuntu 11.10 上的 Postgresql 8.3 服务器上运行的 Drupal 6 开发的。还有 webmin 版本 1.590。
Now I want to update records in a table, but when I run:
现在我想更新表中的记录，但是当我运行时：
UPDATE uac_institution_view SET status = '2' WHERE nid = '9950'
it gives me an error like:
它给了我一个错误，如：
Failed to execute SQL : SQL UPDATE uac_institution_view SET status = '2' WHERE nid = '9950' failed : ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule.
执行 SQL 失败：SQL UPDATE uac_institution_view SET status = '2' WHERE nid = '9950' failed：错误：无法更新视图提示：您需要无条件的 ON UPDATE DO INSTEAD 规则。
The problem is that only SELECTqueries work. UPDATE, INSERTand DELETEcommands are not working; they fail with the above error.
问题是只有SELECT查询有效。UPDATE，INSERT并且DELETE命令不起作用；他们因上述错误而失败。
Is this a permisssion problem? A syntax error? Something else?
这是权限问题吗？语法错误？还有什么？
回答by Craig Ringer
PostgreSQL views are not updateable by default. You must tell PostgreSQL how you want the view to be updated.
默认情况下，PostgreSQL 视图不可更新。您必须告诉 PostgreSQL 您希望如何更新视图。
Do this using "an unconditional ON UPDATE DO INSTEADrule" (as the error message you pasted said) or preferably on PostgreSQL 9.1 and above using a view trigger. I provided links to all that in my answer to your previous post, but here's some more info:
使用“无条件ON UPDATE DO INSTEAD规则”（如您粘贴的错误消息所述）或最好在 PostgreSQL 9.1 及更高版本上使用视图触发器执行此操作。我在对您上一篇文章的回答中提供了所有这些内容的链接，但这里有更多信息：
- updateable views in PostgreSQL 9.1 using
INSTEAD OFtrigger - updateable views (for Pg 9.0 and below using rules)
CREATE TRIGGERCREATE VIEW- rules vs triggers
- rules
- triggers in PL/pgSQL
- PostgreSQL 9.1 中使用
INSTEAD OF触发器的可更新视图 - 可更新视图（适用于 Pg 9.0 及以下使用规则）
CREATE TRIGGERCREATE VIEW- 规则与触发器
- 规则
- PL/pgSQL 中的触发器
In many cases it's better to leave the view read-only and just update the underlying table. Since you have not provided a definition of the view it's hard to say what that would actually involve. Update your question with the output of running \d uac_institution_viewin psqland comment to say you've done so; maybe I can point out a way to run the update directly on the underlying table(s).
在许多情况下，最好让视图保持只读状态并只更新基础表。由于您尚未提供视图的定义，因此很难说这实际上涉及什么。使用 run \d uac_institution_viewin的输出更新您的问题，psql并评论说您已经这样做了；也许我可以指出一种直接在基础表上运行更新的方法。
You are using a very obsolete version of PostgreSQL (8.3) so you cannot use the preferred INSTEAD OFtrigger approach, you must either use rules or update the underlying table directly.
您使用的是非常过时的 PostgreSQL (8.3) 版本，因此您不能使用首选INSTEAD OF触发器方法，您必须使用规则或直接更新基础表。
回答by Jeff French
FYI, after the answer involving rules/triggers was posted, PostgreSQL 9.3 came out with auto-updatable views. Version 9.3 is in beta 2 as of June 27, 2013, so it's not yet GA.
仅供参考，在发布涉及规则/触发器的答案后，PostgreSQL 9.3 推出了可自动更新的视图。截至 2013 年 6 月 27 日，版本 9.3 处于测试版 2 中，因此它尚未正式发布。
Here is an example: https://web.archive.org/web/20160322164044/http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-auto-updatable-views/
这是一个例子：https: //web.archive.org/web/20160322164044/http: //michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-auto-updatable-views/
回答by happybuddha
I am on postgres 9.5 and views are updatable by default. Example :
我在 postgres 9.5 上，默认情况下视图是可更新的。例子 ：
CREATE TABLE UP_DATE (id number, name varchar2(29));
insert into up_date values(1, 'Foo');
select * from up_date;
CREATE OR REPLACE VIEW UPDATE
AS
Select
name from up_date;
select * from update;
insert into update values('Bar');
select * from update;
Will out put Foo and Bar
将输出 Foo 和 Bar
