如何使這個執行連接的 Postgres 視圖可更新？

我有兩張桌子，products並且subscriptions：

CREATE TABLE products (
   id bigint NOT NULL,
   title character varying(75),
   description text,
   manufacturer_id bigint,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   mpn text,
   visible boolean DEFAULT false NOT NULL
);

CREATE TABLE subscriptions (
   id bigint NOT NULL,
   product_id bigint NOT NULL,
   user_id bigint NOT NULL,
   created_at timestamp without time zone,
   updated_at timestamp without time zone
);

在我的應用程序中，我通常需要知道一個產品的訂閱者數量，並且在應用程序中的所有正確位置都使用這種邏輯是很棘手的。所以我想products用已經包含該資訊的視圖替換錶。所以我這樣做了：

ALTER TABLE ONLY products
 RENAME TO products_raw;

CREATE VIEW products AS
 SELECT products_raw.*, COALESCE(a.subscriptions_count, 0) AS subscriptions_count
 FROM products_raw
 LEFT OUTER JOIN (
   SELECT b.product_id, COUNT(*) subscriptions_count
   FROM   subscriptions b
   GROUP BY b.product_id
 ) a ON a.product_id = products_raw.id;

但是，products視圖不能自動更新，因為JOIN- 我希望在表上執行任何 INSERT/UPDATE/DELETE 操作，在適用時products_raw忽略虛擬。subscriptions_count

這是我第一次創建視圖或規則，但我嘗試了這條規則：

CREATE RULE products_insert_rule AS ON INSERT TO products DO INSTEAD
 INSERT INTO products_raw VALUES(NEW.*);

但是 Postgres 不喜歡這樣：

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 13:         INSERT INTO products_raw VALUES(NEW.*);
                                                ^

我也試過寫一個INSTEAD OF INSERT觸發器：

CREATE FUNCTION products_insert() RETURNS trigger AS $$
BEGIN
 INSERT INTO products_raw VALUES (NEW.*);
 RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
 FOR EACH ROW EXECUTE PROCEDURE products_insert();

但得到了基本相同的錯誤資訊：

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO products_raw VALUES (NEW.*)
                                        ^
QUERY:  INSERT INTO products_raw VALUES (NEW.*)
CONTEXT:  PL/pgSQL function products_insert() line 3 at SQL statement
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

我正在使用 Postgres 9.4。任何提示將不勝感激。

編輯：我對這個觸發器更近了一點，但由於我不確定如何將products_rawrowtype 轉換為products. 我也不喜歡必須重複預設值（包括id序列）的方向……

CREATE FUNCTION products_insert() RETURNS trigger AS $$
DECLARE
   p products_raw%ROWTYPE;
BEGIN
 IF p.id IS NULL THEN
   p.id = NEXTVAL('products_id_seq');
 END IF;
 IF p.visible IS NULL THEN
   p.visible = false;
 END IF;
 INSERT INTO products_raw VALUES(p.*);

 RETURN p;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
 FOR EACH ROW EXECUTE PROCEDURE products_insert();

我使用上述觸發器得到的錯誤是

PG::DatatypeMismatch: ERROR:  returned row structure does not match the structure of the triggering table
DETAIL:  Number of returned columns (8) does not match expected column count (9).
CONTEXT:  PL/pgSQL function products_insert() during function exit
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

您不能使用 NEW.*，因為視圖具有基礎表中不存在的列。您必須明確列出要添加的列。正如人們上面解釋的那樣，這不起作用的原因是您沒有提供預設值。為此，您可能需要考慮使用 COALESCE()。

引用自：https://dba.stackexchange.com/questions/105957