postgresql中计数的函数触发器

7xzttuei 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(245)

我已经创建了一些模式（dsfv、dsfn等），并在每个模式中插入了一些表，包括以下表：

poste_hta_bt（具有属性“code_pt”作为唯一关键字的父表）;
transfo_hta_bt（也具有“code_pt”属性作为引用poste_hta_bt的外键）。

我还创建了一个函数触发器，它计算“transfo_hta_bt”中实体的总数，并在“poste_hta_bt”的“nb_transf”属性中报告它。
我的代码如下：

SET SESSION AUTHORIZATION dsfv;
SET search_path TO dsfv, public;

CREATE TABLE IF NOT EXISTS poste_hta_bt
(
    id_pt serial NOT NULL,
    code_pt varchar(30) NULL UNIQUE,
    etc.
);
CREATE TABLE IF NOT EXISTS transfo_hta_bt
(
    id_tra serial NOT NULL,
    code_tra varchar(30) NULL, 
    code_pt varchar(30) NULL,
    etc.
);

ALTER TABLE transfo_hta_bt ADD CONSTRAINT "FK_transfo_hta_bt_poste_hta_bt"
FOREIGN KEY (code_pt) REFERENCES poste_hta_bt (code_pt) ON DELETE No Action ON UPDATE No Action;

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    som_transf smallint;
    som_transf1 smallint;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = NEW.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = NEW.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'DELETE') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = OLD.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = OLD.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'UPDATE') THEN
        SELECT COUNT(*) INTO som_transf FROM dsfv.transfo_hta_bt WHERE code_pt = NEW.code_pt;
        SELECT COUNT(*) INTO som_transf1 FROM dsfv.transfo_hta_bt WHERE code_pt = OLD.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf WHERE dsfv.poste_hta_bt.code_pt = NEW.code_pt;
        UPDATE dsfv.poste_hta_bt SET nb_transf = som_transf1 WHERE dsfv.poste_hta_bt.code_pt = OLD.code_pt;
        RETURN NULL;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;

DROP TRIGGER IF EXISTS recap_tr ON dsfv.transfo_hta_bt;
CREATE TRIGGER recap_tr AFTER INSERT OR UPDATE OR DELETE ON dsfv.transfo_hta_bt FOR EACH ROW EXECUTE PROCEDURE recap_transf();

这段代码运行正确，但我不明白为什么如下：在函数触发器中，我注意到我必须指定每个表的模式，尽管我从一开始就将search_path调整为dsfv。另外，当我在函数触发器中将dsfv.transfo_hta_bt替换为TG_TABLE_NAME时，最后一个变量无法识别。提前感谢您的帮助。

postgresql

来源：https://stackoverflow.com/questions/74189111/function-trigger-with-count-in-postgresql

2条答案

按热度按时间

rxztt3cl1#

您需要在函数体的开头重复SET search_path TO dsfv, public;，以便将其应用于其内部上下文。
TG_TABLE_NAME是一个text变量，与您正在使用的TG_OP没有什么不同，因此您不能直接将其作为表名插入查询中。您必须将查询构造为文本，并使用动态SQL EXECUTE来运行它们。

CREATE OR REPLACE FUNCTION recap_transf() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    som_transf smallint;
    som_transf1 smallint;
BEGIN
--search path update is rendered somewhat useless by dynamic SQL used later
    execute 'SET search_path TO '||TG_TABLE_SCHEMA||', public;';
    IF (TG_OP = 'INSERT') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using NEW.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      NEW.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'DELETE') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using OLD.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      OLD.code_pt;
        RETURN NULL;
    ELSIF (TG_OP = 'UPDATE') THEN
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf 
                using NEW.code_pt;
        execute format('SELECT COUNT(*) FROM %1$I.%2$I WHERE code_pt = $1',
                        TG_TABLE_SCHEMA,
                        TG_TABLE_NAME) 
                into som_transf1 
                using OLD.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf,
                      NEW.code_pt;
        execute format('UPDATE %1$I.poste_hta_bt SET nb_transf = $1 WHERE %1$I.poste_hta_bt.code_pt = $2',
                        TG_TABLE_SCHEMA) 
                using som_transf1,
                      OLD.code_pt;
        RETURN NULL;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;

赞(0）回复(0）举报 2022-11-04

xytpbqjk2#

PostgreSQL将函数存储为字符串，在执行函数时对其进行解释。应用的search_path是在调用函数时生效的search_path，而不是在创建函数时生效的search_path。（使用v14中的新语法创建的SQL函数不会受到这种影响，因为在创建函数时会解析这些函数。）
若要避免这些问题，您应该修正所有函式的search_path：

ALTER FUNCTION recap_transf() SET search_path = dsfv;

请注意，在不受信任的用户可以创建对象的情况下添加模式是不安全的，因此，只有在撤消了PUBLIC的CREATE权限（该权限在v15之前的版本中具有）的情况下，才添加public。

赞(0）回复(0）举报 2022-11-04

我来回答

postgresql中计数的函数触发器

2条答案

相关问题

热门标签

最新问答