3 INSERT INTO config.upgrade_log (version) VALUES ('0423'); --miker
5 CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$
14 xpath_list := STRING_TO_ARRAY( xpaths, '|' );
16 select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
18 FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
19 IF xpath_list[i] = 'null()' THEN
20 select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i );
22 select_list := ARRAY_APPEND(
32 WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
33 ELSE xpath_list[i] || '//text()'
37 $sel$ || document_field || $sel$
45 where_list := ARRAY_APPEND(
47 'c_' || i || ' IS NOT NULL'
54 SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$)
55 )x WHERE $q$ || ARRAY_TO_STRING( where_list, ' AND ' );
56 -- RAISE NOTICE 'query: %', q;
58 FOR out_record IN EXECUTE q LOOP
59 RETURN NEXT out_record;
64 $func$ LANGUAGE PLPGSQL IMMUTABLE;