PostgreSQL中动态展开未知键hstore列为独立列的方法

核心思路 #

因为hstore的键集合未知，需要分两步处理：先提取所有唯一键，再动态构造SQL将每个键转为单独列，同时通过quote_ident自动处理关键字冲突问题。

步骤1：生成合法的字段表达式 #

执行以下SQL，替换your_table为你的表名、hstore_column为实际hstore列名，获取所有键对应的字段表达式：

SELECT string_agg(DISTINCT format('%I -> %L AS %I', 'hstore_column', skey, skey), ', ')
FROM (
    SELECT DISTINCT skeys(hstore_column) AS skey
    FROM your_table
) AS keys;

执行后会得到类似如下的字符串（自动将保留字用双引号包裹，避免冲突）：

hstore_column -> 'kerb' AS kerb, hstore_column -> 'highway' AS highway, hstore_column -> 'user' AS "user"

步骤2：构造并执行完整查询 #

将上一步得到的字符串拼接到基础查询中：

SELECT osm_id, 
       -- 粘贴上一步生成的字段表达式
       hstore_column -> 'kerb' AS kerb,
       hstore_column -> 'highway' AS highway,
       hstore_column -> 'user' AS "user"
FROM your_table;

执行后即可得到展开结果，没有对应键的行，对应列会返回NULL。

自动化方案（PL/pgSQL函数） #

如果需要重复使用，可创建函数自动生成展开视图：

CREATE OR REPLACE FUNCTION create_hstore_expanded_view(view_name text, table_name text, hstore_col text, id_col text)
RETURNS void AS $$
DECLARE
    cols text;
    create_view_sql text;
BEGIN
    -- 生成所有字段表达式
    SELECT string_agg(DISTINCT format('%I -> %L AS %I', hstore_col, skey, skey), ', ')
    INTO cols
    FROM (
        SELECT DISTINCT skeys(hstore_col::regclass) AS skey
        FROM table_name::regclass
    ) AS keys;

    -- 构造并执行创建视图的SQL
    create_view_sql := format('CREATE OR REPLACE VIEW %I AS SELECT %I, %s FROM %I', view_name, id_col, cols, table_name);
    EXECUTE create_view_sql;
END;
$$ LANGUAGE plpgsql;

调用方式（替换参数为你的实际值）：

SELECT create_hstore_expanded_view('expanded_osm_data', 'your_table', 'hstore_column', 'osm_id');

之后直接查询视图即可：

SELECT * FROM expanded_osm_data;

注意：如果后续hstore列新增了键，需要重新调用该函数更新视图，因为视图是静态结构，不会自动同步新键。

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