PL/pgSQL场景下VARCHAR列动态类型识别与转换方案咨询

该需求面向一款开源分析库。

以下是从events_view查询得到的结果：

id  | visit_id |  name  |             prop0          | prop1 | url
------+----------+--------+----------------------------+-------+------------
 2004 |        4 | Magnus | 2021-10-26 02:25:55.790999 | 142   | cnn.com
 2007 |        4 | Hartis | 2021-10-26 02:26:37.773999 | 25    | fox.com

当前除id外所有列的类型均为VARCHAR，表结构如下：

Column  |        Type       | Collation | Nullable | Default
----------+-------------------+-----------+----------+---------
 id       | bigint            |           |          |
 visit_id | character varying |           |          |
 name     | character varying |           |          |
 prop0    | character varying |           |          |
 prop1    | character varying |           |          |
 url      | character varying |           |          |

理想的表结构应为：

Column  |           Type         | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
 id       | bigint                 |           |          |
 visit_id | bigint                 |           |          |
 name     | character varying      |           |          |
 prop0    | time without time zone |           |          |
 prop1    | bigint                 |           |          |
 url      | character varying      |           |          |

无法使用类似SELECT visit::bigint, name::varchar, prop0::time, prop1::integer, url::varchar FROM tbl的硬编码转换逻辑，因为列名仅在运行时才能确定。

为简化实现，可仅将各列转换为三类类型：boolean、numeric、varchar，类型匹配规则采用以下正则：

• boolean: ^(true|false|t|f)$
• numeric: ^(,-)[0-9]+(,\.[0-9]+)$
• varchar: 所有不匹配上述boolean和numeric规则的内容

请问应当编写怎样的SQL，实现自动识别各列类型并完成动态转换？

首先修正你给出的numeric正则存在明显笔误，调整为可正确匹配正负整数、浮点数的规则：^[+-]?\d+(\.\d+)?$，以下方案基于PostgreSQL环境实现，核心思路是通过元数据查询+动态SQL拼接完成自动类型转换：

实现逻辑 #

类型判断优先级为boolean > numeric > varchar，对每个非id列，校验所有非空值是否符合某类类型的正则规则，所有值都符合则转换为对应类型，否则降级到更宽松的类型。

完整实现代码 #

1. 通用转换函数 #

CREATE OR REPLACE FUNCTION dynamic_type_convert(target_table text)
RETURNS SETOF record AS $$
DECLARE
    cur_col record;
    select_part text := 'id';
    target_col_type text;
    exec_sql text;
BEGIN
    -- 遍历目标表所有非id列
    FOR cur_col IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = target_table 
          AND column_name != 'id'
        ORDER BY ordinal_position
    LOOP
        -- 判断当前列应转换的类型
        EXECUTE format(
            'SELECT CASE
                WHEN NOT EXISTS (
                    SELECT 1 FROM %I WHERE %I IS NOT NULL AND lower(%I) !~* ''^(true|false|t|f)$''
                ) THEN ''boolean''
                WHEN NOT EXISTS (
                    SELECT 1 FROM %I WHERE %I IS NOT NULL AND %I !~ ''^[+-]?\d+(\.\d+)?$''
                ) THEN ''numeric''
                ELSE ''varchar''
            END',
            target_table, cur_col.column_name, cur_col.column_name,
            target_table, cur_col.column_name, cur_col.column_name
        ) INTO target_col_type;
        -- 拼接转换逻辑
        select_part := select_part || format(', %I::%s AS %I', cur_col.column_name, target_col_type, cur_col.column_name);
    END LOOP;
    -- 生成最终执行SQL
    exec_sql := format('SELECT %s FROM %I', select_part, target_table);
    -- 执行并返回结果
    RETURN QUERY EXECUTE exec_sql;
END;
$$ LANGUAGE plpgsql;

2. 调用示例 #

-- 调用时指定返回列结构即可
SELECT * FROM dynamic_type_convert('events_view') 
AS t(id bigint, visit_id numeric, name varchar, prop0 varchar, prop1 numeric, url varchar);

3. 一键生成转换后视图（可选） #

如果需要长期使用转换后的结构，可以直接生成固化视图，后续直接查询视图即可：

DO $$
DECLARE
    cur_col record;
    view_def text := 'CREATE OR REPLACE VIEW events_view_typed AS SELECT id';
    target_col_type text;
BEGIN
    FOR cur_col IN 
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'events_view' 
          AND column_name != 'id'
        ORDER BY ordinal_position
    LOOP
        EXECUTE format(
            'SELECT CASE
                WHEN NOT EXISTS (
                    SELECT 1 FROM events_view WHERE %I IS NOT NULL AND lower(%I) !~* ''^(true|false|t|f)$''
                ) THEN ''boolean''
                WHEN NOT EXISTS (
                    SELECT 1 FROM events_view WHERE %I IS NOT NULL AND %I !~ ''^[+-]?\d+(\.\d+)?$''
                ) THEN ''numeric''
                ELSE ''varchar''
            END',
            cur_col.column_name, cur_col.column_name, cur_col.column_name, cur_col.column_name
        ) INTO target_col_type;
        view_def := view_def || format(', %I::%s AS %I', cur_col.column_name, target_col_type, cur_col.column_name);
    END LOOP;
    view_def := view_def || ' FROM events_view';
    EXECUTE view_def;
END $$;

-- 直接查询转换后的视图
SELECT * FROM events_view_typed;

扩展说明 #

如果需要支持你最初期望的时间类型，只需要在类型判断逻辑中，将时间类型的校验规则放在numeric之前即可，可增加时间正则匹配^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d+)?$对应转换为timestamp/time类型。
如果数据量较大，全表扫描判断类型性能不足，可以改为采样前N行数据判断类型，降低性能损耗。

内容的提问来源于stack exchange，提问作者Cássio