PostgreSQL中如何将表名作为参数实现通用自增ID触发器函数？

针对PostgreSQL 14.4，将多个重复的自增ID触发器函数重构为单一通用函数，可按以下步骤实现：

1. 前提假设 #

• 所有需要自动生成ID的表，其ID字段统一命名为id；
• 每个表对应自增序列的命名规则为{表名}_id_seq（这是PostgreSQL使用SERIAL/IDENTITY类型时的默认序列命名方式，若你的序列命名不同，需调整后续代码中的拼接逻辑）。

2. 创建通用触发器函数 #

使用PL/pgSQL编写函数，利用触发器内置变量TG_TABLE_NAME自动获取当前触发的表名，无需手动传参（若你坚持要显式传递表名参数，可参考注释中的方式2）：

CREATE OR REPLACE FUNCTION set_id()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当ID字段为空时生成自增ID
    IF NEW.id IS NULL THEN
        -- 方式1：自动获取当前表名拼接序列（推荐）
        EXECUTE format('SELECT nextval(''%I_id_seq'')', TG_TABLE_NAME) INTO NEW.id;
        
        -- 方式2：显式接收表名参数（触发器创建时需传入表名）
        -- EXECUTE format('SELECT nextval(''%I_id_seq'')', TG_ARGV[0]) INTO NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

3. 为目标表绑定触发器 #

为每个需要自动生成ID的表创建触发器，调用通用函数：

方式1（自动获取表名）的触发器创建语句 #

-- 为account_object_bank_account表创建触发器
CREATE TRIGGER trigger_account_object_bank_account_set_id
BEFORE INSERT ON account_object_bank_account
FOR EACH ROW EXECUTE FUNCTION set_id();

-- 为account_object_belong_to_group表创建触发器
CREATE TRIGGER trigger_account_object_belong_to_group_set_id
BEFORE INSERT ON account_object_belong_to_group
FOR EACH ROW EXECUTE FUNCTION set_id();

方式2（显式传参）的触发器创建语句 #

CREATE TRIGGER trigger_account_object_bank_account_set_id
BEFORE INSERT ON account_object_bank_account
FOR EACH ROW EXECUTE FUNCTION set_id('account_object_bank_account');

4. 额外优化（可选） #

如果你的表ID字段名不统一，可进一步将字段名作为参数传入，让函数通用性更强：

CREATE OR REPLACE FUNCTION set_id(field_name text)
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('SELECT $1.%I IS NULL', field_name) USING NEW INTO STRICT has_null;
    IF has_null THEN
        EXECUTE format('SELECT nextval(''%I_%I_seq'')', TG_TABLE_NAME, field_name) INTO NEW.*;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

创建触发器时传入字段名：

CREATE TRIGGER trigger_account_object_bank_account_set_id
BEFORE INSERT ON account_object_bank_account
FOR EACH ROW EXECUTE FUNCTION set_id('id');

5. 验证功能 #

插入测试数据，不指定ID字段，检查是否自动生成自增ID：

INSERT INTO account_object_bank_account (bank_name, account_no) VALUES ('测试银行', '123456');
SELECT id, bank_name, account_no FROM account_object_bank_account WHERE account_no = '123456';

内容的提问来源于stack exchange，提问作者Đỗ Như Vỹ