Using cte table in postgres function

Question

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?

Valid sql must follow a CTE. A CTE is just syntactic sugar for a subquery, so use accordingly. The way your code is currently written I can't understand the relationship between your CTE and your q:= line. How do you intend to use the SQL inside your CTE for this functionality? — JNevill
– JNevill, Commented Nov 29, 2022 at 21:02
Ok, I edited my question. In result:= I need to translate ids passed as parameter with labels from cte_sl table — mtmx
– mtmx, Commented Nov 29, 2022 at 21:38

Bergi · Accepted Answer · 2022-11-29 21:53:34Z

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

or

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;

or

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

Collectives™ on Stack Overflow

Using cte table in postgres function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related