我已经创建了一些模式(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时,最后一个变量无法识别。提前感谢您的帮助。
2条答案
按热度按时间rxztt3cl1#
您需要在函数体的开头重复
SET search_path TO dsfv, public;
,以便将其应用于其内部上下文。TG_TABLE_NAME
是一个text
变量,与您正在使用的TG_OP
没有什么不同,因此您不能直接将其作为表名插入查询中。您必须将查询构造为文本,并使用动态SQL EXECUTE来运行它们。xytpbqjk2#
PostgreSQL将函数存储为字符串,在执行函数时对其进行解释。应用的
search_path
是在调用函数时生效的search_path
,而不是在创建函数时生效的search_path
。(使用v14中的新语法创建的SQL函数不会受到这种影响,因为在创建函数时会解析这些函数。)若要避免这些问题,您应该修正所有函式的
search_path
:请注意,在不受信任的用户可以创建对象的情况下添加模式是不安全的,因此,只有在撤消了
PUBLIC
的CREATE
权限(该权限在v15之前的版本中具有)的情况下,才添加public
。