4 SELECT evergreen.upgrade_deps_block_check('0865', :eg_version);
6 -- First, explode the field into constituent parts
7 WITH format_parts_array AS (
9 STRING_TO_ARRAY(a.holdable_formats, '-') AS parts
10 FROM action.hold_request a
11 WHERE a.hold_type = 'M'
12 AND a.fulfillment_time IS NULL
13 ), format_parts_wide AS (
15 regexp_split_to_array(parts[1], '') AS item_type,
16 regexp_split_to_array(parts[2], '') AS item_form,
18 FROM format_parts_array
19 ), converted_formats_flat AS (
21 CASE WHEN ARRAY_LENGTH(item_type,1) > 0
22 THEN '"0":[{"_attr":"item_type","_val":"' || ARRAY_TO_STRING(item_type,'"},{"_attr":"item_type","_val":"') || '"}]'
25 CASE WHEN ARRAY_LENGTH(item_form,1) > 0
26 THEN '"1":[{"_attr":"item_form","_val":"' || ARRAY_TO_STRING(item_form,'"},{"_attr":"item_form","_val":"') || '"}]'
29 CASE WHEN item_lang <> ''
30 THEN '"2":[{"_attr":"item_lang","_val":"' || item_lang ||'"}]'
33 FROM format_parts_wide
34 ) UPDATE action.hold_request SET holdable_formats = '{' ||
35 converted_formats_flat.item_type || ',' ||
36 converted_formats_flat.item_form || ',' ||
37 converted_formats_flat.item_lang || '}'
38 FROM converted_formats_flat WHERE converted_formats_flat.id = action.hold_request.id;