Select with case in postgres function-CodePudding

I have following sql function:

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

It works for me, but I have over 10 field types like

(xpath('//control-rodzaj-wsparcia/item/label/text()', xml))

I can create one function like previous posted per field type, but I thinking about using case in this function with field_type paramter passed, something like that:

CREATE OR REPLACE FUNCTION convert_reapeted_sections_to_labels_for_sl_export(ids text[], field_type text)
RETURNS text
LANGUAGE SQL
AS $$

  SELECT string_agg(label, ' | ' ORDER BY idx)
  FROM unnest(ids) WITH ORDINALITY AS id(value, idx)
  JOIN (
    case when field_type='control-rodzaj-wsparcia' then
        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
    case when field_type='control-rodzaj-wsparcia2' then 
            SELECT
            unnest (xpath('//control-rodzaj-wsparcia2/item/label/text()', xml))::text AS label,
            unnest (xpath('//control-rodzaj-wsparcia2/item/value/text()', xml))::text AS value
    end
        FROM sl_export_newest_definition
  ) AS cte_sl USING (value);
 
$$;

But with this function I get 'syntax error near case'. It is possible to use case inside join in my function? Or I must create one function per one field_type? It will be code duplication :/

CodePudding user response：

CASE is a function to be used in an expression. It is not the equivalent of if that you are used to have in procedural languages like Python or C . Especially you can't use CASE to "run" one SELECT statement or the other.

In your case however, you don't need multiple cases at all. Just build your path string dynamically like so:

xpath('//' || field_type || '/item/label/text()', xml)