如何在PostgreSQL函数中正确使用CTE实现ID转标签？

问题 #

我有如下PL/pgSQL函数，希望通过传入的ids数组参数，将其转换为对应标签，标签数据来自XML解析生成的CTE（cte_sl）。但取消注释CTE代码段后，赋值语句出现问题，请问是否可以这样使用CTE？或是需要其他实现方式？

函数代码：

drop function if exists convert_reapeted_sections_to_labels_for_sl_export();
create or replace function convert_reapeted_sections_to_labels_for_sl_export(ids text[])
returns text as
$$
declare
    result text;
begin
--  with cte_sl as (
--select
--    unnest (xpath('//control-rodzaj-wsparcia/item/label/text()', xml))::text as label,
--    unnest (xpath('//control-rodzaj-wsparcia/item/value/text()', xml))::text as value
--FROM sl_export_newest_definition
--)
    result:= concat(select label from cte_sl where value=ids[1],select label from cte_sl where value=ids[2]);

    return result;
end;
$$
language plpgsql;

解答 #

你不能像原来那样单独定义CTE后在后续赋值语句里引用，因为CTE的作用域仅限于定义它的单个SQL语句，无法跨语句使用。正确的做法是将CTE与查询赋值逻辑整合到同一个SQL语句中，具体实现如下：

修正后的函数代码 #

drop function if exists convert_reapeted_sections_to_labels_for_sl_export();
create or replace function convert_reapeted_sections_to_labels_for_sl_export(ids text[])
returns text as
$$
declare
    result text;
begin
    WITH cte_sl AS (
        SELECT
            unnest(xpath('//control-rodzaj-wsparcia/item/label/text()', xml))::text AS label,
            unnest(xpath('//control-rodzaj-wsparcia/item/value/text()', xml))::text AS value
        FROM sl_export_newest_definition
    )
    SELECT concat(
        (SELECT label FROM cte_sl WHERE value = ids[1]),
        (SELECT label FROM cte_sl WHERE value = ids[2])
    ) INTO result;

    return result;
end;
$$
language plpgsql;

关键说明 #

1. 将CTE定义与查询拼接结果的SQL语句合并，CTE的作用域覆盖整个SELECT语句，因此可以在子查询中正常引用cte_sl。
2. 使用SELECT ... INTO result语法直接将拼接后的标签结果赋值给变量，替代原来的直接赋值写法。
3. 如果需要处理ids数组中多个元素（不止前两个），可以改用更灵活的string_agg方式，示例：

   WITH cte_sl AS (
       -- 同上面的CTE定义
   )
   SELECT string_agg(label, '') INTO result 
   FROM cte_sl 
   WHERE value = ANY(ids);
   

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