Using cte table in postgres function-CodePudding

I have following function:

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;

I want to use this function for translate ids passed in function parameter to labels which are in cte_sl_export_control_rodzaj_przyznanego_wsparcia. But if I uncomment cte table lines i got

result:= concat(select label from cte_sl where value=ids[1],select label from cte_sl where value=ids[2]);

It is possible to use cte function in that way? Or I need another way to achieve that?

CodePudding user response：

It's a syntax error since a CTE may only prelude a query, but not a PL/PGSQL statement. What you can do instead is use

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
);

Notice I'd simplify the entire function to use language-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);
$$;