我正在使用一个postgresql 14数据库,在这个数据库中,我可以通过一个视图模式访问数据,并且使用触发器执行插入和更新。在存储表上有一个唯一的约束,我想知道在这种情况下是否有一种方法可以执行upsert?
这将在一个(小得多)的数据库中重现该问题:
模式:
CREATE SCHEMA storage;
-- unreachable storage schema
CREATE TABLE storage.data (
id SERIAL PRIMARY KEY,
a INTEGER,
b INTEGER,
c INTEGER,
CONSTRAINT pair UNIQUE(a, b)
);
-- reachable access schema
CREATE SCHEMA access;
CREATE VIEW access.data AS
SELECT id, a, b, c FROM storage.data;
-- data insertion trigger for the access view
CREATE FUNCTION data_insert()
RETURNS TRIGGER AS $data_insert$
BEGIN
INSERT INTO storage.data (a, b, c)
VALUES (NEW.a, NEW.b, NEW.c);
RETURN NEW;
END;
$data_insert$ LANGUAGE plpgsql;
CREATE TRIGGER data_insert_trigger
INSTEAD OF INSERT ON access.data
FOR EACH ROW EXECUTE PROCEDURE data_insert();
-- data update trigger for the access view
CREATE FUNCTION data_update()
RETURNS TRIGGER AS $data_update$
BEGIN
UPDATE storage.data SET
a = NEW.a,
b = NEW.b,
c = NEW.c
WHERE id = OLD.id;
RETURN NEW;
END;
$data_update$ LANGUAGE plpgsql;
CREATE TRIGGER data_update_trigger
INSTEAD OF UPDATE ON access.data
FOR EACH ROW EXECUTE PROCEDURE data_update();
我想做的是:
# INSERT INTO access.data(a,b,c) VALUES (1,2,3);
INSERT 0 1
# INSERT INTO access.data(a,b,c) VALUES (1,2,4)
ON CONFLICT ON CONSTRAINT pair
DO UPDATE SET c=EXCLUDED.c;
ERROR: constraint "pair" for table "data" does not exist
在这种情况下,是否有任何方法可以执行upsert查询,或者我应该满足于执行select,然后执行insert或update?
编辑:我不能修改模式或添加功能,我只能对访问模式进行查询。
1条答案
按热度按时间gfttwv5a1#
你试过这样的东西吗?