PostgreSQL无法从视图trips删除行的技术求助

我需要从trips视图中删除那些在sessions视图存在对应记录的行，操作普通表时正常，但操作视图时报错。

数据示例 #

trips视图数据 #

SELECT * FROM trips;
 id      session_ids    distance
535780  {8024,8026}     74695.31
535268  {4567}            455.84
543477  {63331}         18546.94
540797  {43350}           412.65

sessions视图数据 #

SELECT * FROM sessions;
session_id      timestamp
  4567     2016-04-07 15:39:31.578
  8024     2016-04-09 14:31:19.068
  1526     2016-04-04 07:50:24.544
 10311     2016-04-10 16:48:14.883

注意：trips.session_ids列类型为integer array，sessions.session_id列类型为integer。

执行的DELETE语句 #

DELETE FROM trips t
USING sessions s
WHERE s.session_id = ANY(t.session_ids)

报错信息 #

ERROR:  cannot delete from view "trips"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
SQL state: 55000

期望执行后的结果 #

SELECT * FROM trips;
  id    session_ids  distance
543477  {63331}     18546.94
540797  {43350}       412.65

补充说明 #

1. trips视图是从大表raw_table筛选出的子集，创建语句：

CREATE VIEW trips
AS
SELECT * FROM raw_table
WHERE some_condition;

需求是进一步筛选trips视图，排除sessions中存在对应记录的行。

2. 测试环境中该操作可正常执行，但实际数据库触发报错，测试用例代码：

CREATE TABLE raw_table(id int, session_ids integer[], distance double precision);
INSERT INTO raw_table(id, session_ids, distance)
VALUES (535780,'{8024,8026}',4695.31),
(535268,'{4567}',455.84), 
(543477,'{63331}',18546.94),
(544400,'{15304}',25546.24),
(544210,'{12012,17577}',32546.24),
(540797,'{43350}',412.65);

CREATE VIEW trips 
AS
SELECT * FROM raw_table
WHERE distance < 25000;

CREATE TABLE sessions (session_id int, timestamp TIMESTAMP);
INSERT INTO sessions (session_id, timestamp)
VALUES (4567,'2016-04-07 15:39:31.578+01'),
(8024,'2016-04-09 14:31:19.068+01'),
(1526,'2016-04-04 07:50:24.544+01'),
(10311,'2016-04-10 16:48:14.883+01');

DELETE FROM trips t
USING sessions s
WHERE s.session_id = ANY(t.session_ids)

解决方案 #

方法1：直接操作底层表raw_table #

既然trips是raw_table的筛选视图，直接对底层表执行删除操作，同时保留视图的筛选条件：

DELETE FROM raw_table rt
USING sessions s
WHERE rt.id IN (SELECT id FROM trips) -- 保留trips视图的筛选范围
AND s.session_id = ANY(rt.session_ids);

此方法绕过了视图不可更新的限制，同时确保只删除trips视图范围内符合条件的数据。

方法2：给trips视图添加INSTEAD OF DELETE触发器 #

如果必须通过视图执行删除操作，可以创建触发器替代视图的删除逻辑：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION trips_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM raw_table
    WHERE id = OLD.id
    AND EXISTS (
        SELECT 1 FROM sessions s
        WHERE s.session_id = ANY(raw_table.session_ids)
    );
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

2. 给trips视图绑定触发器：

CREATE TRIGGER trips_instead_of_delete
INSTEAD OF DELETE ON trips
FOR EACH ROW EXECUTE FUNCTION trips_delete_trigger();

完成后，再执行最初的DELETE语句即可生效。

内容的提问来源于stack exchange，提问作者arilwan