1 -- Evergreen DB patch 0555.unnest_oils_xpath_table.sql
3 -- Replace usage of custom explode_array() function with native unnest()
7 -- check whether patch can be applied
8 SELECT evergreen.upgrade_deps_block_check('0555', :eg_version);
10 CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$
19 xpath_list := STRING_TO_ARRAY( xpaths, '|' );
21 select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
23 FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
24 IF xpath_list[i] = 'null()' THEN
25 select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i );
27 select_list := ARRAY_APPEND(
37 WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
38 ELSE xpath_list[i] || '//text()'
42 $sel$ || document_field || $sel$
50 where_list := ARRAY_APPEND(
52 'c_' || i || ' IS NOT NULL'
59 SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$)
60 )x WHERE $q$ || ARRAY_TO_STRING( where_list, ' OR ' );
61 -- RAISE NOTICE 'query: %', q;
63 FOR out_record IN EXECUTE q LOOP
64 RETURN NEXT out_record;
69 $func$ LANGUAGE PLPGSQL IMMUTABLE;