PostgreSQL跨银行转账系统报错：bank1_add_amount过程不存在

问题描述 #

开发模拟5家银行间转账的PostgreSQL系统时，执行代码出现错误，提示bank1_add_amount(character varying, double precision)过程不存在。移除EXECUTE FORMAT('CALL bank%s_add_amount(%L, %s)', bank, client_name, amount);语句后错误消失，但该语句是实现转账功能必需的。

错误信息（中文翻译） #

错误: 错误: 过程bank1_add_amount(character varying, double precision)不存在
第1行: CALL bank1_add_amount('Client 1'::varchar, -50::float)
             ^
提示: 没有匹配指定名称和参数类型的过程。您可能需要添加显式的类型转换。
查询: CALL bank1_add_amount('Client 1'::varchar, -50::float)
上下文: PL/pgSQL函数add_amount(integer,character varying,double precision)，第6行EXECUTE处
SQL指令 « CALL add_amount(1, 'Client 1', -50) »
PL/pgSQL函数transfer_amount(integer,integer,character varying,double precision)，第3行EXECUTE处
SQL指令 « CALL transfer_amount(ROW.bank_from, ROW.bank_to, ROW.client_name, ROW.amount) »
PL/pgSQL函数perform_end_of_day()，第6行CALL处

SQL状态: 42883

问题原因 #

你用PREPARE创建的是预准备语句（Prepared Statement），而非存储过程（Procedure）。CALL关键字仅用于调用存储过程，预准备语句需要用EXECUTE执行，这是导致报错的核心原因。此外，预准备语句的作用域仅限当前会话，DO块执行完毕后这些预准备语句可能已失效，且重复创建的方式也不合理。

解决方案 #

方案1：将预准备语句替换为存储过程 #

把每个bankX_add_amount从预准备语句改成存储过程，这样就能用CALL正常调用：

-- 替换原来的DO块，创建存储过程
CREATE OR REPLACE PROCEDURE bank1_add_amount(client_name varchar, amount float)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE bank1_accounts
    SET balance = balance + amount
    WHERE client = client_name;
END $$;

CREATE OR REPLACE PROCEDURE bank2_add_amount(client_name varchar, amount float)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE bank2_accounts
    SET balance = balance + amount
    WHERE client = client_name;
END $$;

-- 同理创建bank3_add_amount、bank4_add_amount、bank5_add_amount存储过程

之后原add_amount过程的CALL语句就能正常执行。

方案2：直接在add_amount中动态执行UPDATE（推荐） #

无需为每个银行单独创建存储过程或预准备语句，直接在add_amount过程里动态生成UPDATE语句，简化代码：

-- 删除原来所有创建预准备语句的DO块
-- 重新定义add_amount过程
CREATE OR REPLACE PROCEDURE add_amount(bank integer, client_name varchar, amount float)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 动态检查客户是否在对应银行有账户
    IF NOT EXISTS (
        SELECT 1 FROM (EXECUTE FORMAT('SELECT client FROM bank%s_accounts WHERE client = %L', bank, client_name)) AS t
    ) THEN
        RAISE EXCEPTION '客户 % 在银行 % 无有效账户', client_name, bank;
    END IF;
    -- 动态执行更新余额的语句
    EXECUTE FORMAT(
        'UPDATE bank%s_accounts SET balance = balance + $1 WHERE client = $2',
        bank
    ) USING amount, client_name;
END $$;

这种方式避免了重复代码，也不会混淆预准备语句和存储过程的使用方式。

修正后的完整代码示例 #

-- 清理现有表
DROP TABLE IF EXISTS bank1_accounts, bank2_accounts, bank3_accounts, bank4_accounts,
bank5_accounts, clients, transfer_logs, end_of_day_transfers;

-- 创建基础表结构
CREATE TABLE IF NOT EXISTS clients
(
    name varchar PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS transfer_logs
(
    id serial PRIMARY KEY,
    bank_from integer,
    bank_to integer,
    client_name varchar,
    amount float,
    success bool
);

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

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

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

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

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

CREATE TABLE end_of_day_transfers
(
    id serial,
    bank_from integer,
    bank_to integer,
    client_name varchar,
    amount float
);

-- 插入测试数据
INSERT INTO clients VALUES ('Client 1');
INSERT INTO clients VALUES ('Client 2');
INSERT INTO clients VALUES ('Client 3');
INSERT INTO clients VALUES ('Client 4');

INSERT INTO bank1_accounts VALUES (8, 2000, 'Client 2');
INSERT INTO bank1_accounts VALUES (12, 650, 'Client 1');
INSERT INTO bank2_accounts VALUES (2, 300, 'Client 3');
INSERT INTO bank3_accounts VALUES (2, 300, 'Client 4');

-- 重新定义add_amount过程（动态执行UPDATE）
CREATE OR REPLACE PROCEDURE add_amount(bank integer, client_name varchar, amount float)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 动态检查客户账户是否存在
    IF NOT EXISTS (
        SELECT 1 FROM (EXECUTE FORMAT('SELECT client FROM bank%s_accounts WHERE client = %L', bank, client_name)) AS t
    ) THEN
        RAISE EXCEPTION '客户 % 在银行 % 无有效账户', client_name, bank;
    END IF;
    -- 动态更新余额
    EXECUTE FORMAT(
        'UPDATE bank%s_accounts SET balance = balance + $1 WHERE client = $2',
        bank
    ) USING amount, client_name;
END $$;

-- 转账过程
CREATE OR REPLACE PROCEDURE transfer_amount(bank_from integer, bank_to integer, client_name varchar, amount float)
LANGUAGE plpgsql
AS $$
BEGIN
    CALL add_amount(bank_from, client_name, -amount);
    CALL add_amount(bank_to, client_name, amount);
    INSERT INTO transfer_logs (bank_from, bank_to, client_name, amount, success) VALUES (bank_from, bank_to, client_name, amount, TRUE);
EXCEPTION WHEN OTHERS THEN
    INSERT INTO transfer_logs (bank_from, bank_to, client_name, amount, success) VALUES (bank_from, bank_to, client_name, amount, FALSE);
    RAISE; -- 可选：重新抛出异常，便于调试
END $$;

-- 日终处理过程
CREATE OR REPLACE PROCEDURE perform_end_of_day()
LANGUAGE plpgsql
AS $$
DECLARE
    ROW record;
BEGIN
    FOR ROW IN SELECT * FROM end_of_day_transfers
    LOOP
        CALL transfer_amount(ROW.bank_from, ROW.bank_to, ROW.client_name, ROW.amount);
    END LOOP;
END $$;

-- 测试执行
INSERT INTO end_of_day_transfers (bank_from, bank_to, client_name, amount) VALUES (1, 2, 'Client 1', 50);
CALL perform_end_of_day();
SELECT * FROM transfer_logs;

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