PostgreSQL已创建存储过程却提示不存在的问题求助

核心问题分析 #

• 触发器只能调用触发器函数，不能调用存储过程：PostgreSQL中，EXECUTE PROCEDURE是历史遗留语法，实际要求的是返回trigger类型的函数，而非你创建的PROCEDURE对象。
• 触发器触发级别错误：你使用了FOR EACH STATEMENT，但要访问NEW/OLD行级记录，必须改为FOR EACH ROW。
• 触发器调用语法错误：触发器函数的NEW/OLD参数由PostgreSQL自动传入，无需显式传递。

修复后的完整代码 #

将原存储过程改为触发器函数，同时修正触发器定义：

DROP TABLE IF EXISTS clients, accounts, transactions, failed_transactions CASCADE;

CREATE TABLE IF NOT EXISTS clients
(
    id int PRIMARY KEY,
    name varchar
);

CREATE TABLE IF NOT EXISTS accounts
(
    id int PRIMARY KEY,
    balance float,
    client int,
    FOREIGN KEY (client) REFERENCES clients(id),
    CHECK (balance >= -500)
);

CREATE TABLE IF NOT EXISTS transactions
(
    id serial PRIMARY KEY,
    amount float
);

CREATE TABLE failed_transactions
(
    id serial PRIMARY KEY, -- 补充主键约束，原代码遗漏
    account_id integer,
    attempted_amount float,
    timestamp timestamp DEFAULT NOW()
);

-- 替换存储过程为触发器函数
CREATE OR REPLACE FUNCTION log_failed_transaction()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.balance < -500 THEN
        INSERT INTO failed_transactions(account_id, attempted_amount)
                    VALUES (NEW.id, NEW.balance - OLD.balance);
    END IF;
    RETURN NEW; -- BEFORE触发器必须返回NEW/OLD/NULL，此处返回NEW允许更新执行
END $$;

-- 修正触发器定义：改为行级触发，无需传递参数
CREATE OR REPLACE TRIGGER accounts_update BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_failed_transaction();

INSERT INTO clients VALUES (10, 'Client 1');
INSERT INTO clients VALUES (14, 'Client 2');
INSERT INTO clients VALUES (25, 'Client 3');

INSERT INTO accounts VALUES (8, 2000, 14);
INSERT INTO accounts VALUES (12, 650, 10);
INSERT INTO accounts VALUES (2, 300, 25);

关键修复点说明 #

1. 存储过程转触发器函数：触发器函数必须返回trigger类型，PostgreSQL会自动注入当前行的NEW/OLD记录，无需手动声明参数。
2. 触发级别调整：FOR EACH ROW才能获取单条数据的变更前后状态，FOR EACH STATEMENT仅针对整个更新语句，无法访问行级数据。
3. 补充返回值：BEFORE类型触发器必须返回NEW/OLD/NULL，否则会触发执行错误。
4. 表结构补全：为failed_transactions的id字段添加主键约束，保证表结构完整性。

测试验证 #

执行以下更新语句触发失败交易日志：

UPDATE accounts SET balance = -600 WHERE id = 8;

查询failed_transactions表即可看到记录：

SELECT * FROM failed_transactions;

内容的提问来源于stack exchange，提问作者Lapin