在PostgreSQL函数中动态访问列并转换数据类型

一、动态访问NEW对象的列 #

完全可以实现类似Map的动态列访问，常用两种方案：

1. 借助hstore类型转换 #

PostgreSQL的hstore扩展支持将行对象转换为键值对结构，直接通过列名字符串取值：

-- 先安装hstore扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE OR REPLACE FUNCTION dynamic_access_new_hstore()
RETURNS TRIGGER AS $$
DECLARE
    target_col TEXT := 'description'; -- 执行时可动态传入或从配置获取
    col_value TEXT;
BEGIN
    -- 将NEW行转为hstore后，通过列名取值
    col_value := hstore(NEW)->target_col;
    RAISE NOTICE '动态获取列%的值：%', target_col, col_value;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 用动态SQL（EXECUTE）实现 #

如果需要处理更复杂的类型或逻辑，动态SQL是更灵活的选择：

CREATE OR REPLACE FUNCTION dynamic_access_new_sql()
RETURNS TRIGGER AS $$
DECLARE
    target_col TEXT := 'city';
    col_value TEXT;
BEGIN
    -- 用format函数安全拼接标识符，避免SQL注入
    EXECUTE format('SELECT ($1).%I', target_col) INTO col_value USING NEW;
    RAISE NOTICE '动态获取列%的值：%', target_col, col_value;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

二、动态转换NEW列值为指定类型 #

结合动态SQL，可根据执行时获取的类型名完成转换，示例如下：

假设存在配置表存储目标类型：

CREATE TABLE IF NOT EXISTS type_config (
    id SERIAL PRIMARY KEY,
    target_type TEXT NOT NULL -- 存储类型名，如'INTEGER'、'BOOLEAN'、'TEXT'
);

INSERT INTO type_config (target_type) VALUES ('INTEGER');

实现动态转换的函数：

CREATE OR REPLACE FUNCTION dynamic_cast_new_value()
RETURNS TRIGGER AS $$
DECLARE
    target_col TEXT := 'age'; -- 动态列名
    target_type TEXT;
    casted_value TEXT;
BEGIN
    -- 从配置表获取目标类型
    SELECT target_type INTO target_type FROM type_config WHERE id = 1;
    
    -- 动态拼接转换语句，注意类型名需合法
    EXECUTE format('SELECT CAST(($1).%I AS %s)', target_col, target_type) 
        INTO casted_value 
        USING NEW;
        
    RAISE NOTICE '列%转换为%类型后的值：%', target_col, target_type, casted_value;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

注意事项 #

• 动态SQL需用format的%I处理列名（标识符），避免SQL注入风险；类型名如果来自可信源（如自有配置表），用%s拼接即可
• hstore方案仅支持基本数据类型，复杂类型（如数组、JSON）需转为文本后处理
• 类型转换需确保源列值与目标类型兼容，否则会抛出异常，建议添加EXCEPTION块处理错误

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