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

问题描述：

我试图找出如何做相应的任意计算的功能和更新的true/false变量：plpgsql动态数学/比较运算符计算？

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（）函数？

答

不要尝试更新触发器功能中的表格。设置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注入攻击。您可以在文本列（如CHECK(operator_1 in ('+', '-', '*', '/'))等）上添加一些检查来保护表格。

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

相关推荐