PostgreSQL聚合函数返回元素带单引号的数组及查询调整需求

嘿，这个问题我之前帮同事处理过，给你几个简单直接的方案，完美适配你的需求：

核心方法：聚合前先给元素加单引号 #

PostgreSQL没有专门返回单引号包裹元素的聚合函数，但我们可以把「元素格式化」和「聚合」两步结合起来，轻松搞定。

1. 最安全的方式：用format('%L', ...) #

format('%L', 字段)会自动把字段值转换成带单引号的字符串，还会智能转义值里的单引号（比如值是O'Connell的话，会变成'O''Connell'），能避免SQL注入风险，非常推荐：

-- 基础版本：聚合所有municipality_id，用逗号分隔，单引号包裹
SELECT string_agg(format('%L', municipality_id), ',') AS formatted_ids
FROM your_table;

-- 进阶版本：去重+排序
SELECT string_agg(DISTINCT format('%L', municipality_id), ',' ORDER BY municipality_id) AS formatted_ids
FROM your_table;

2. 更简洁的方式：用quote_literal() #

如果你的字段值里没有特殊字符，quote_literal()函数也能直接给值加上单引号，用法更简单：

SELECT string_agg(quote_literal(municipality_id), ',') AS formatted_ids
FROM your_table;

3. 处理已有的数组字段 #

如果你的现有查询返回的是数组类型的结果（比如municipality_ids是text[]），可以先拆分数组再聚合：

SELECT string_agg(format('%L', unnest(municipality_ids)), ',') AS formatted_ids
FROM your_table;

替换原有双引号输出 #

你说现有查询返回的是双引号包裹的元素，应该是用了quote_ident()函数（这个函数是给SQL标识符加双引号）。只需要把quote_ident()替换成上面的format('%L')或者quote_literal()，就能直接得到单引号包裹的结果。

举个实际例子：
原查询（双引号输出）：

SELECT string_agg(quote_ident(municipality_id::text), ',') AS old_result
FROM your_table;
-- 输出："NYC","LA","Chicago"

修改后的查询（单引号输出）：

SELECT string_agg(format('%L', municipality_id), ',') AS new_result
FROM your_table;
-- 输出：'NYC','LA','Chicago'

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