如何让SELECT输出的引号字符串列表适配PostgreSQL可变参数函数？

先看你提供的原函数：

CREATE OR REPLACE function get_v(variadic _v text[]) returns table (v varchar(50)) as
$F$
declare
begin
return query 
    select t.v
    from test t
    where t.v = any(_v)
end;
$F$
language plpgsql;

手动传入'x','y','z'时，函数能正常执行：

SELECT v FROM get_v('x','y','z');

但用SELECT string_agg(quote_literal(x.v), ',') from (select v from get_v_from_name('something')) as x得到的字符串传参时，函数就跑不起来——原因很简单，这个查询输出的是单个字符串，而函数需要的是text[]（文本数组）类型，类型不匹配自然报错。

解决方法 #

方法1：直接生成数组（推荐）

别用string_agg把结果拼成字符串，改用array_agg直接生成text[]类型的结果，这样可以直接传给函数：

SELECT v FROM get_v(
  (SELECT array_agg(x.v) FROM (select v from get_v_from_name('something')) as x)
);

方法2：把现有字符串转成数组

如果已经有了那个用string_agg生成的带引号的字符串，可以通过字符串处理转成数组：

SELECT v FROM get_v(
  string_to_array(
    regexp_replace(
      (SELECT string_agg(quote_literal(x.v), ',') FROM (select v from get_v_from_name('something')) as x),
      '''', '', 'g'  -- 去掉所有单引号
    ),
    ','  -- 按逗号拆分
  )
);

注意：如果你的v值里本身包含逗号，这种方法会出错，所以更推荐方法1。

关于最佳实践 #

这种通过字符串中转传参的方式并不是最优解，容易因为特殊字符（比如逗号、引号）引发错误。PostgreSQL里可以参考SQL Server的表值参数思路，用更优雅的方式实现：

• 直接用数组类型参数（就是你现在用的variadic text[]，但尽量直接生成数组而非字符串转换）
• 自定义复合类型，配合表值函数传递
• 用临时表或者CTE直接关联查询，跳过中间函数传参的步骤

比如，你甚至可以直接把两个查询关联起来，不需要通过get_v函数中转：

SELECT t.v
FROM test t
JOIN get_v_from_name('something') x ON t.v = x.v;

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