如何基于另一张表的规则拼接表中列并查询唯一结果

问题根源 #

你碰到的ERROR: more than one row returned by a subquery used as an expression报错，是因为静态SQL里作为表达式的子查询只能返回单行，但你的需求是基于table2中动态的列名组合来拼接，静态SQL根本没法处理这种运行时才确定的列名，必须用动态SQL来实现。

方案一：自定义函数（推荐） #

写个PL/pgSQL函数，传入table2的criteria值，动态生成拼接语句并返回唯一结果：

CREATE OR REPLACE FUNCTION get_unique_concat(p_criteria text)
RETURNS TABLE(concat_result text) AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT DISTINCT CONCAT(%s) FROM table1',
    p_criteria
  );
END;
$$ LANGUAGE plpgsql;

调用的时候直接关联table2，就能拿到每个规则对应的唯一拼接结果：

SELECT t2.id, t2.criteria, r.concat_result
FROM table2 t2
CROSS JOIN get_unique_concat(t2.criteria) r;

方案二：无函数批量执行 #

如果不想创建函数，用DO块生成临时结果表：

-- 先创建临时表存结果
CREATE TEMP TABLE rule_concat_results (
  rule_id int,
  criteria text,
  concat_result text
);

-- 批量执行动态SQL
DO $$
DECLARE
  rule_rec record;
BEGIN
  FOR rule_rec IN SELECT id, criteria FROM table2 LOOP
    EXECUTE format(
      'INSERT INTO rule_concat_results
       SELECT %L, %L, DISTINCT CONCAT(%s) FROM table1',
       rule_rec.id, rule_rec.criteria, rule_rec.criteria
    );
  END LOOP;
END $$;

-- 查询结果
SELECT * FROM rule_concat_results;

避坑提醒 #

1. 确保table2的criteria字段里的列名和table1完全匹配，不然会报“列不存在”的错
2. 如果列名包含特殊字符（比如空格、大写），criteria里要加双引号，比如"user_name", "age"
3. 防SQL注入：如果criteria的内容不是完全可信的，要先拆分列名并用quote_ident处理，修改后的函数如下：

CREATE OR REPLACE FUNCTION get_unique_concat(p_criteria text)
RETURNS TABLE(concat_result text) AS $$
DECLARE
  safe_cols text;
BEGIN
  -- 拆分列名并转义，避免注入
  safe_cols := (
    SELECT string_agg(quote_ident(trim(col)), ', ')
    FROM unnest(string_to_array(p_criteria, ',')) col
  );
  RETURN QUERY EXECUTE format(
    'SELECT DISTINCT CONCAT(%s) FROM table1',
    safe_cols
  );
END;
$$ LANGUAGE plpgsql;

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