我在 PostgreSQL 中实现规则或触发器来处理对列类型 ( a
) 之一已更改的表的插入和旧查询时遇到问题。我尝试了几种方法,但未能实现所需的行为。
这是我的场景的简化版本:
我有一个t1
包含列a
和的表b
:
CREATE TABLE t1 (
a int,
b int
);
我希望允许查询使用其他类型的列a
(例如字符串),并将其替换为另一个值(例如 0)。但是,如果插入整数值,则应保留该值而不进行修改。
我尝试过使用规则和触发器函数,但每种方法都遇到了问题:
- 规则:我尝试创建一个规则来拦截插入并修改列的值
a
(如果它不是整数)。但是,我遇到了无限递归和无效输入不会触发规则的问题:
CREATE RULE test_rule AS
ON INSERT TO t1
WHERE pg_typeof(NEW.a) <> 'integer'::regtype
DO INSTEAD
INSERT INTO t1 (a, b)
VALUES (0, NEW.b)
postgres=# insert into t1 (select 'x',1);
ERROR: invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
^
postgres=# insert into t1 (select 1,1);
ERROR: infinite recursion detected in rules for relation "t1"
- 触发函数:我尝试使用触发函数来检查插入值的数据类型并相应地修改它。虽然此方法适用于有效输入,但它没有按预期处理无效输入。
CREATE OR REPLACE FUNCTION insert_default_a_if_non_integer()
RETURNS TRIGGER AS
$$
BEGIN
IF pg_typeof(NEW.a) <> 'integer'::regtype THEN
NEW.a := 0; -- or any default value you prefer
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER t1_insert_trigger
BEFORE INSERT ON t1
FOR EACH ROW
EXECUTE FUNCTION insert_default_a_if_non_integer();
postgres=# insert into t1 (select 1,1);
INSERT 0 1
postgres=# insert into t1 (select 'x',1);
ERROR: invalid input syntax for type integer: "x"
LINE 1: insert into t1 (select 'x',1);
^
有人可以提供有关实现我的要求的最佳方法的指导吗?任何见解或替代解决方案将不胜感激。
谢谢你!
您的尝试在语句编译阶段失败,然后它们开始执行并导致重写规则或触发器激活。
解决困境的一种方法是创建一个视图,其中的列与您现在不正确的插入语句兼容,并
instead of
在该视图上定义触发器。