postgresql 在postgres函数中使用cte表

hsgswve4 于 2022-12-03 发布在 PostgreSQL

关注(0)|答案(1)|浏览(216)

我有以下功能：

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;

我想使用此函数将函数参数中传递的id转换为cte_sl_export_control_rodzaj_przyznanego_wsparcia中的标签。但如果我取消注解cte表行，我得到
结果：= concat（从cte_sl中选择标签，其中值= id [1]，从cte_sl中选择标签，其中值= id [2]）;
有可能以那种方式使用cte函数吗？或者我需要另一种方式来实现？

postgresql

来源：https://stackoverflow.com/questions/74620153/using-cte-table-in-postgres-function

1条答案

按热度按时间

bzzcjhmw1#

这是一个语法错误，因为CTE只能作为查询的前奏，而不能作为PL/PGSQL语句的前奏。

result := (
  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]
  )
);

或

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;

或

result := (
  SELECT concat(
    SELECT label FROM cte_sl WHERE value=ids[1],
    SELECT label FROM cte_sl WHERE value=ids[2]
  )
  FROM (
    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
  ) AS cte_sl
);

请注意，我将简化整个函数以使用语言SQL：

CREATE OR REPLACE FUNCTION convert_reapeted_sections_to_labels_for_sl_export(ids text[])
RETURNS text
LANGUAGE SQL
AS $$
  SELECT string_agg(label, '' ORDER BY idx)
  FROM unnest(ids) WITH ORDINALITY AS id(value, idx)
  JOIN (
    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
  ) AS cte_sl USING (value);
$$;

赞(0）回复(0）举报 2022-12-03

我来回答

postgresql 在postgres函数中使用cte表

1条答案

相关问题

热门标签

最新问答