postgresql - plpgsql动态数学/比较运算符计算？

Question

我试图弄清楚如何在函数中进行任意计算并相应地更新 true/false 变量:

DROP TABLE IF EXISTS conditions;

CREATE TABLE conditions(
    id SERIAL PRIMARY KEY,
    val_1 INT NOT NULL,
    operator_1 TEXT,
    val_2 INT,
    comparison_operator TEXT NOT NULL,
    val_3 INT NOT NULL,
    operator_2 TEXT,
    val_4 INT,
    current_state BOOL,
    CONSTRAINT c1 CHECK ((val_2 IS NULL AND operator_1 IS NULL) OR (val_2 IS NOT NULL AND operator_1 IS NOT NULL)),
    CONSTRAINT c2 CHECK ((val_4 IS NULL AND operator_2 IS NULL) OR (val_4 IS NOT NULL AND operator_2 IS NOT NULL))
);

CREATE OR REPLACE FUNCTION do_calculation()
RETURNS TRIGGER AS $$
BEGIN   
/*
    UPDATE conditions
    SET current_state = ...???
*/
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate
    AFTER INSERT OR UPDATE ON conditions
    FOR EACH ROW
    EXECUTE PROCEDURE do_calculation();

INSERT INTO conditions (val_1, comparison_operator, val_3) VALUES (1, '>', 2);
INSERT INTO conditions (val_1, operator_1, val_2, comparison_operator, val_3) VALUES (1, '+', 3, '>', 2);
INSERT INTO conditions (val_1, operator_1, val_2, comparison_operator, val_3, operator_2, val_4) VALUES (1, '+', 3, '=', 2, '*', 2);
UPDATE conditions SET val_1 = 3 WHERE id = 1;


SELECT * FROM conditions;

但是如何在 do_calculation() 函数中进行实际计算呢？

Answer 1

不要尝试在触发器函数中更新表。设置NEW 记录的列并将触发器声明为BEFORE INSERT OR UPDATE。使用 EXECUTE .

CREATE OR REPLACE FUNCTION do_calculation()
RETURNS TRIGGER AS $$
BEGIN   
    EXECUTE concat(
        'SELECT ', 
        NEW.val_1, NEW.operator_1, NEW.val_2, 
        NEW.comparison_operator, 
        NEW.val_3 , NEW.operator_2 , NEW.val_4)
    INTO NEW.current_state;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate
    BEFORE INSERT OR UPDATE ON conditions
    FOR EACH ROW
    EXECUTE PROCEDURE do_calculation();

请注意，此方法可能容易受到 SQL 注入(inject)攻击。您可以在文本列上添加一些检查，例如 CHECK(operator_1 in ('+', '-', '*', '/')) 等以保护表格。