我創建了一些模式（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 時，最新的變數無法識別。預先感謝您的幫助。
uj5u.com熱心網友回復：
您需要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;
$$
;
uj5u.com熱心網友回復：
PostgreSQL 將函式存盤為字串，該字串在函式執行時被解釋。search_path適用的是呼叫函式時生效的那個，而不是創建它時生效的那個。（使用從 v14 開始的新語法創建的 SQL 函式是安全的，因為它們在創建函式時被決議。）
為避免這些問題，您應該修復search_pathfor all 函式：
ALTER FUNCTION recap_transf() SET search_path = dsfv;
請注意，添加不受信任的用戶可以創建物件的模式是不安全的，因此只有public在您撤銷了v15 之前版本中的CREATE權限時才添加。PUBLIC
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/519439.html