From e5a662726678d55f8f7c1878dbd8691f0399d7d7 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 28 Sep 2010 16:35:24 +0000 Subject: [PATCH] add support for the null() xpath function, which works in pgxml (AKA xml2) but not in the builtin XPATH function for 8.3+ git-svn-id: svn://svn.open-ils.org/ILS/trunk@18077 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.functions.config.sql | 56 +++++++++------- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- ...a.support-null-function-in-xpath_table.sql | 67 +++++++++++++++++++ 3 files changed, 98 insertions(+), 27 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0423.schema.support-null-function-in-xpath_table.sql diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index 6b43ceb4b3..46daf9e777 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -272,33 +272,37 @@ BEGIN select_list := ARRAY_APPEND( select_list, key || '::INT AS key' ); FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP - select_list := ARRAY_APPEND( - select_list, - $sel$ - EXPLODE_ARRAY( - COALESCE( - NULLIF( - oils_xpath( - $sel$ || - quote_literal( - CASE - WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i] - ELSE xpath_list[i] || '//text()' - END - ) || - $sel$, - $sel$ || document_field || $sel$ + IF xpath_list[i] = 'null()' THEN + select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i ); + ELSE + select_list := ARRAY_APPEND( + select_list, + $sel$ + EXPLODE_ARRAY( + COALESCE( + NULLIF( + oils_xpath( + $sel$ || + quote_literal( + CASE + WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i] + ELSE xpath_list[i] || '//text()' + END + ) || + $sel$, + $sel$ || document_field || $sel$ + ), + '{}'::TEXT[] ), - '{}'::TEXT[] - ), - '{NULL}'::TEXT[] - ) - ) AS c_$sel$ || i - ); - where_list := ARRAY_APPEND( - where_list, - 'c_' || i || ' IS NOT NULL' - ); + '{NULL}'::TEXT[] + ) + ) AS c_$sel$ || i + ); + where_list := ARRAY_APPEND( + where_list, + 'c_' || i || ' IS NOT NULL' + ); + END IF; END LOOP; q := $q$ diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 25d36be27f..e4960f61bf 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0422'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0423'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0423.schema.support-null-function-in-xpath_table.sql b/Open-ILS/src/sql/Pg/upgrade/0423.schema.support-null-function-in-xpath_table.sql new file mode 100644 index 0000000000..1d90b44dc9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0423.schema.support-null-function-in-xpath_table.sql @@ -0,0 +1,67 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0423'); --miker + +CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$ +DECLARE + xpath_list TEXT[]; + select_list TEXT[]; + where_list TEXT[]; + q TEXT; + out_record RECORD; + empty_test RECORD; +BEGIN + xpath_list := STRING_TO_ARRAY( xpaths, '|' ); + + select_list := ARRAY_APPEND( select_list, key || '::INT AS key' ); + + FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP + IF xpath_list[i] = 'null()' THEN + select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i ); + ELSE + select_list := ARRAY_APPEND( + select_list, + $sel$ + EXPLODE_ARRAY( + COALESCE( + NULLIF( + oils_xpath( + $sel$ || + quote_literal( + CASE + WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i] + ELSE xpath_list[i] || '//text()' + END + ) || + $sel$, + $sel$ || document_field || $sel$ + ), + '{}'::TEXT[] + ), + '{NULL}'::TEXT[] + ) + ) AS c_$sel$ || i + ); + where_list := ARRAY_APPEND( + where_list, + 'c_' || i || ' IS NOT NULL' + ); + END IF; + END LOOP; + + q := $q$ +SELECT * FROM ( + SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$) +)x WHERE $q$ || ARRAY_TO_STRING( where_list, ' AND ' ); + -- RAISE NOTICE 'query: %', q; + + FOR out_record IN EXECUTE q LOOP + RETURN NEXT out_record; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + +COMMIT; + -- 2.43.2