无法更新视图？ [英] Cannot update view?

问题描述

我的网站是使用在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状态=
'2'在哪里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:

• PostgreSQL 9.1中使用 INSTEAD OF 触发器


• 可更新的视图（适用于Pg 9.0及以下版本，使用规则）


• 创建触发器


• 创建视图


• 规则与触发器


• 规则


• PL / pgSQL中的触发器

• updateable views in PostgreSQL 9.1 using INSTEAD OF trigger
• updateable views (for Pg 9.0 and below using rules)
• CREATE TRIGGER
• CREATE VIEW
• rules vs triggers
• rules
• triggers in PL/pgSQL

在许多情况下，最好离开该视图为只读，仅更新基础表。由于您尚未提供视图的定义，因此很难说出实际涉及的内容。使用在 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.

这篇关于无法更新视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！