如何從函式返回記錄，由 INSERT/UPDATE 規則（觸發器）執行？

當我嘗試在一個視圖中加入兩個表時遇到了一些問題（對於遷移程式碼，我們希望將一個大記錄拆分為兩個記錄）。我正在使用 Postgres 9.4！

這是一個SQL 小提琴。

小提琴由於某種原因不起作用（不確定是否支持高級東西）。所以基本上我有一個自定義函式，首先創建gi記錄，將記錄中的主 IDgi轉換為變數，然後我使用它來查詢一些外部表並使用結果最終創建joe記錄。

現在我需要支持以下語法：

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                 ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

這是在我們的應用程序層中完成的典型插入（它插入一條記錄，然後使用返回的 id）。我無法執行此查詢，因為我收到以下錯誤：

ERROR: cannot perform INSERT RETURNING on relation "gi_joe"
SQL state: 0A000
Hint: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

如何使用RETURNING自定義函式的集合支持查詢。或者有沒有一種方法可以在一個 INSERT 中完成所有操作（帶有可選RETURNING欄位）。請注意，我的範例已簡化。

表設置：

CREATE TABLE gi
(
 id serial NOT NULL,
 weapon_type_id integer,
 fire_power integer,
 created_at timestamp without time zone NOT NULL,
 updated_at timestamp without time zone NOT NULL,
 CONSTRAINT gi_pkey PRIMARY KEY (id)
);

CREATE TABLE joe
(
 id serial NOT NULL,
 first_name character varying,
 last_name character varying,
 city character varying,
 country character varying,
 gi_id integer,
 external_id integer,
 created_at timestamp without time zone NOT NULL,
 updated_at timestamp without time zone NOT NULL,
 CONSTRAINT joe_pkey PRIMARY KEY (id)
);

CREATE TABLE external
(
 id serial NOT NULL,
 joe_id integer,
 CONSTRAINT external_pkey PRIMARY KEY (id)
);

看法：

CREATE OR REPLACE VIEW gi_joe AS (
 SELECT  gi.id AS id, 
   gi.weapon_type_id as weapon_type, 
   gi.fire_power as weapon_fire_power,
   joe.first_name as first_name,
   joe.last_name as last_name,
   joe.city as city,
   joe.country as country,
   joe.external_id as external_id,
   gi.created_at as created_at,
   gi.updated_at as updated_at
 FROM gi LEFT OUTER JOIN joe ON gi.id = joe.gi_id
);

規則中使用的函式：

CREATE OR REPLACE FUNCTION gi_joe_insert(new_gi_joe gi_joe) RETURNS integer AS $$
 DECLARE temp_gi_id integer;
 DECLARE temp_joe_id integer;
 BEGIN
   INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
     VALUES (new_gi_joe.weapon_type, new_gi_joe.weapon_fire_power, new_gi_joe.created_at, new_gi_joe.updated_at)
     RETURNING id INTO temp_gi_id;

   SELECT joe_id INTO temp_joe_id FROM external WHERE id = new_gi_joe.external_id LIMIT 1;

   INSERT INTO joe(first_name, last_name, city, country, gi_id, external_id,
                   created_at,updated_at)
     VALUES (NEW.first_name, new_gi_joe.last_name, new_gi_joe.city, new_gi_joe.country, temp_gi_id,
           temp_joe_id, new_gi_joe.created_at, new_gi_joe.updated_at);
 RETURN temp_gi_id;
 END;
$$ LANGUAGE plpgsql;

規則，使用返回的 id 插入：

CREATE OR REPLACE RULE custom_insert_gi_joe AS ON INSERT TO gi_joe DO INSTEAD (
 SELECT gi_joe_insert(NEW) AS id;
);

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                 ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

錯誤消息的提示是正確的目標：

您需要一個帶有 RETURNING 子句的無條件 ON INSERT DO INSTEAD 規則。

大膽強調我的。根據文件：

RETURNING``RETURNING如果任何可用規則中沒有子句，則視圖上的查詢將被拒絕 。

不要將此與RETURNS您的函式的子句混淆。不是一回事。

Postgres 查找, or語句的實際RETURNING子句。它必須是一個普通的 SQL 語句。即使規則中有多個語句，也只能有一個子句：INSERT``UPDATE``DELETE RETURNING

RETURNING此外，同一事件的所有規則中最多只能有一個子句。

RETURNING僅當外部呼叫也有子句時才會執行。

由於您要插入兩個表，因此它會變得有些複雜。規則中的兩個INSERT語句中的任何一個都只能使用一個目標表的列。您需要從RETURNING子句中的另一個表（或視圖本身）中進行選擇以提供完整的行。

此外，serial列的返回值不能儲存在變數中，因為普通 SQL 中沒有變數。我解決了它lastval()。

並且NEW在 CTE 中不可見，因此您也不能連結數據修改 CTE。

但無論如何都可以做到：

CREATE OR REPLACE RULE custom_insert_gi_joe AS
ON INSERT TO gi_joe DO INSTEAD (

INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
VALUES (NEW.weapon_type, NEW.weapon_fire_power, NEW.created_at, NEW.updated_at);

INSERT INTO joe(first_name, last_name, city, country
              , gi_id, external_id, created_at, updated_at)
VALUES(NEW.first_name, NEW.last_name, NEW.city, NEW.country, **lastval()**
     ,(SELECT joe_id FROM external WHERE id = NEW.external_id LIMIT 1)
     , NEW.created_at, NEW.updated_at)
RETURNING
  joe.gi_id,
  NULL::int,
  NULL::int,
  joe.first_name,
  joe.last_name,
  joe.city,
  joe.country,
  joe.external_id,
  NULL::timestamp,
  NULL::timestamp
);

SQL小提琴。

我沒有費心提供完整的行，只是為缺少的列填充 NULL 值gi，轉換為相應的類型。這也可以解決，但是由於您只對id列感興趣，因此將無濟於事。

現在，這可以按預期工作：

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                 ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())
RETURNING id;

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