寻求自动更新的PL/PGSQL动态列返回函数及视图实现方案

需求说明 #

编写PL/pgSQL逻辑生成N个动态列，并将结果存入可自动更新的视图类对象，实现原数据变更时无需人工干预即可同步目标格式表格。

示例数据 #

--- 示例数据
DROP TABLE IF EXISTS split_clm;
CREATE TABLE split_clm(
  id integer PRIMARY KEY,
  name text,
  hobby text, 
  value int
);
INSERT INTO split_clm (id, name, hobby,value) VALUES
(1, 'Rene', 'Python, Monkey_Bars',5),
(2, 'CJ', 'Trading, Python',25),
(3, 'Herlinda', 'Fashion',15),
(4, 'DJ', 'Consutling, Sales',35),
(5, 'Martha', 'Social_Media, Teaching',45),
(6, 'Doug', 'Leadership, Management',55),
(7, 'Mathew', 'Finance, Emp_Engagement',65),
(8, 'Mayers', 'Sleeping, Coding, Crossfit',75),
(9, 'Mike', 'YouTube, Athletics',85),
(10, 'Peter', 'Eat, Sleep, Python',95),
(11, 'Thomas', 'Read, Trading, Sales',105);

实现步骤 #

1. 标准化数据格式 #

拆分多值hobby字段为第一范式结构，统一转为小写便于后续处理：

--- 转换为第一范式结构
DROP TABLE IF EXISTS split_clm_Nor2;
CREATE TABLE split_clm_Nor2 AS
SELECT id, name, lower(unnest(string_to_array(hobby, ', '))) AS ivalues, value
FROM split_clm
GROUP BY 1,2,3,4
ORDER BY id;

2. 创建动态列结构模板 #

通过PL/pgSQL生成包含所有唯一hobby值的空表，作为动态列的结构基准：

DROP TABLE IF EXISTS tmpTblTyp2 CASCADE;
DO LANGUAGE plpgsql $$ 
DECLARE v_sqlstring VARCHAR = ''; 
BEGIN 
v_sqlstring := CONCAT(
  'CREATE TABLE tmpTblTyp2 AS SELECT ',
  (SELECT STRING_AGG(CONCAT('NULL::int AS ', ivalues)::TEXT, ', ' ORDER BY ivalues)::TEXT
   FROM (SELECT DISTINCT ivalues FROM split_clm_Nor2) a),
  ' LIMIT 0'
);
EXECUTE(v_sqlstring); 
END $$;

3. 生成自动更新的动态视图 #

创建函数动态构建透视查询，再基于函数创建视图，确保原数据变更时视图自动同步：

--- 创建返回动态列的函数
CREATE OR REPLACE FUNCTION get_dynamic_hobby_pivot()
RETURNS SETOF tmpTblTyp2
LANGUAGE plpgsql
AS $$
DECLARE
  v_sql text;
BEGIN
  -- 动态构建透视SQL语句
  v_sql := CONCAT(
    'SELECT name, ',
    (SELECT STRING_AGG(CONCAT('MAX(CASE WHEN ivalues = ''', ivalues, ''' THEN value END) AS ', ivalues), ', ' ORDER BY ivalues)
     FROM (SELECT DISTINCT ivalues FROM split_clm_Nor2) a),
    ' FROM split_clm_Nor2 GROUP BY name ORDER BY name'
  );
  RETURN QUERY EXECUTE v_sql;
END $$;

--- 创建自动更新的视图
CREATE OR REPLACE VIEW dynamic_hobby_pivot_view AS
SELECT * FROM get_dynamic_hobby_pivot();

期望结果 #

查询视图dynamic_hobby_pivot_view将得到以下格式的自动更新表格：

内容的提问来源于stack exchange，提问作者C'perota