无法在 PostgreSQL 中更新视图? [英] Cannot update view in PostgreSQL?
问题描述
我的网站是使用在 Ubuntu 11.10 上的 Postgresql 8.3 服务器上运行的 Drupal 6 开发的.还有 webmin 版本 1.590.
My site was developed using Drupal 6 running on a Postgresql 8.3 server on Ubuntu 11.10. Also webmin version 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:
执行 SQL 失败:SQL UPDATE uac_institution_view SET status ='2' WHERE nid = '9950' 失败:错误:无法更新视图提示:您需要一个无条件的 ON UPDATE DO INSTEAD 规则.
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.
问题是只有 SELECT 查询有效.UPDATE、INSERT 和 DELETE 命令不起作用；他们因上述错误而失败.
The problem is that only SELECT queries work. UPDATE, INSERT and DELETE commands are not working; they fail with the above error.
这是一个权限问题吗?语法错误?还有什么?
Is this a permisssion problem? A syntax error? Something else?
推荐答案
PostgreSQL 视图默认不可更新.您必须告诉 PostgreSQL 您希望如何更新视图.
PostgreSQL views are not updateable by default. You must tell PostgreSQL how you want the view to be updated.
使用无条件的 ON UPDATE DO INSTEAD 规则"(如您粘贴的错误消息所述)或最好使用视图触发器在 PostgreSQL 9.1 及更高版本上执行此操作.我在我对您之前帖子的回答中提供了所有这些内容的链接，但这里有更多信息:
Do this using "an unconditional ON UPDATE DO INSTEAD rule" (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:
在许多情况下，最好让视图保持只读状态并只更新基础表.由于您没有提供视图的定义，因此很难说这实际上会涉及什么.使用在 psql 中运行 d uac_institution_view 的输出更新您的问题，并评论说您已经这样做了；也许我可以指出一种直接在基础表上运行更新的方法.
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_view in psql and comment to say you've done so; maybe I can point out a way to run the update directly on the underlying table(s).
您使用的是非常过时的 PostgreSQL (8.3) 版本，因此您不能使用首选的 INSTEAD OF 触发器方法，您必须使用规则或直接更新基础表.
You are using a very obsolete version of PostgreSQL (8.3) so you cannot use the preferred INSTEAD OF trigger approach, you must either use rules or update the underlying table directly.
这篇关于无法在 PostgreSQL 中更新视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
