Select with case in postgres function

Question

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 :/

using xmltable() might be easier. But without the actual XML content and the expected result this is impossible to tell. — user330315
– user330315, Commented Nov 30, 2022 at 9:35

SebDieBln · Accepted Answer · 2022-11-30 09:55:46Z

1

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)

answered Nov 30, 2022 at 9:55

SebDieBln

3,4891 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select with case 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