PostgreSQL视图的INSERT或UPDATE操作

Question

我将从PostgreSQL视图开始，因为它们对我的用例很有用，并且比函数提供更好的性能。

（这不相关，但我正在Heroku Postgres上使用Django 1.7 - 以防万一）。

我已经创建了一个视图并可以正常查询。我想编写一个Django包装器，使其像表格一样处理，并相应地查询和写入。我一直在审查Postgres关于视图的INSERT和UPDATE文档，但老实说，我发现他们的文档非常难以阅读，我几乎无法理解他们在说什么。

假设我有以下视图：

CREATE OR REPLACE VIEW links AS
  SELECT
    listing.id                                                     AS listing_id,
    CONCAT('/i-', industry.slug, '-j-', listing.slug, '/') AS link,
    'https://www.example.com' || CONCAT(industry.slug, '-SEP-', listing.slug, '/') AS full_link,
    listing.slug AS listing_slug,
    industry.slug AS industry_slug
  FROM listing
    INNER JOIN company ON company.id = listing.company_id
    INNER JOIN industry ON industry.id = company.industry_id

在这里，我使用industry.slug和listing.slug来构建链接。我希望能够从这个视图中更新这两个字段，如下所示：

UPDATE links
SET listing_slug = 'my-new-slug'
WHERE listing_id = 5;

如何正确地创建规则？

这与IT技术有关。请注意，不要删除HTML标签。

- jdotjdot

---

由于它不是一个简单的可更新视图（它有连接），所以您需要编写一个“DO INSTEAD”规则或触发器。规则很复杂，很难正确使用，因此我建议使用触发器。 - Craig Ringer

Answer 1

由于存在双重连接，建议您使用触发器过程。要更新行业表，您需要首先使用外键listing-company-industry查找industry.id。 过程可能如下：

CREATE OR REPLACE FUNCTION update_listing_and_industry() RETURNS TRIGGER AS
  $$
  DECLARE _company_id int; _industry_id int;
  BEGIN
    _company_id = (SELECT company_id FROM listing WHERE id = OLD.listing_id);
    _industry_id = (SELECT industry_id FROM company WHERE id = _company_id);

    UPDATE listing SET slug = NEW.listing_slug WHERE id = OLD.listing_id;
    UPDATE industry SET slug = NEW.industry_slug WHERE id = _industry_id;
    RETURN NEW;
  END;
  $$
LANGUAGE plpgsql;

注意：触发器过程是一个普通的过程，它返回一个触发器。根据触发器的操作，该过程必须返回NEW或OLD（在此情况下返回NEW）。

而带有INSTEAD OF UPDATE子句的触发器：

CREATE trigger update_view INSTEAD OF UPDATE ON links 
FOR EACH ROW EXECUTE PROCEDURE update_listing_and_industry();

- Tom-db

Answer 2

你的视图中唯一可以更新的列是listing_slug。 更新其他列是不可能或没有意义的（例如，更新industry_slug是没有意义的，因为在视图中没有industry的主键）。 在这种情况下，您应该使用条件规则来防止更新其他列。

正如文档中所述，每个您希望在视图上执行的操作都必须有一个无条件的INSTEAD规则。因此，您应该创建一个虚拟的INSTEAD规则和一个条件性的ALSO规则：

CREATE RULE update_links_default
AS ON UPDATE TO links DO INSTEAD NOTHING;

CREATE RULE update_links_listing
AS ON UPDATE TO links 
WHERE NEW.listing_slug <> OLD.listing_slug
DO ALSO
    UPDATE listing 
    SET slug = NEW.listing_slug
    WHERE id = OLD.listing_id;

如果您将industry.id as industry_id列添加到视图中，您可以为industry表定义适当的规则：

CREATE RULE update_links_industry
AS ON UPDATE TO links 
WHERE NEW.industry_slug <> OLD.industry_slug
DO ALSO
    UPDATE industry 
    SET slug = NEW.industry_slug
    WHERE id = OLD.industry_id;

通常，您需要为表格编写一个 ALSO 规则。

- klin