From 4ce058763027e724ee2bfc3b339a0dc77ad61107 Mon Sep 17 00:00:00 2001
From: Jason Boyer <jboyer@library.in.gov>
Date: Fri, 15 Mar 2019 15:35:26 -0400
Subject: [PATCH] LP1820339: Vandelay Imports on Pg 10
In vandelay.flatten_marc_hstore there are a couple
instances of set-returning functions used inside a
CASE statement, which Pg10 is unhappy about. This
branch changes these regexp_matches calls to use
regexp_match instead which avoids this error.
Signed-off-by: Jason Boyer <jboyer@library.in.gov>
Signed-off-by: Jason Stephenson <jason@sigio.com>
Signed-off-by: Ben Shum <ben@evergreener.net>
Signed-off-by: Jason Boyer <jboyer@library.in.gov>
---
Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 4 +--
.../XXXX.schema.lp1820339-pg10-vandelay.sql | 34 ++++++++++++++++++++++
2 files changed, 36 insertions(+), 2 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1820339-pg10-vandelay.sql
diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql
index 18268f465a..5a11ac6816 100644
--- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql
+++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql
@@ -632,9 +632,9 @@ BEGIN
FROM (SELECT tag,
subfield,
CASE WHEN tag = '020' THEN -- caseless -- isbn
- LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
+ LOWER((REGEXP_MATCH(value,$$^(\S{10,17})$$))[1] || '%')
WHEN tag = '022' THEN -- caseless -- issn
- LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
+ LOWER((REGEXP_MATCH(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
WHEN tag = '024' THEN -- caseless -- upc (other)
LOWER(value || '%')
ELSE
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1820339-pg10-vandelay.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1820339-pg10-vandelay.sql
new file mode 100644
index 0000000000..1f7416cc75
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1820339-pg10-vandelay.sql
@@ -0,0 +1,34 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
+ record_xml TEXT
+) RETURNS HSTORE AS $func$
+BEGIN
+ RETURN (SELECT
+ HSTORE(
+ ARRAY_AGG(tag || (COALESCE(subfield, ''))),
+ ARRAY_AGG(value)
+ )
+ FROM (
+ SELECT tag, subfield, ARRAY_AGG(value)::TEXT AS value
+ FROM (SELECT tag,
+ subfield,
+ CASE WHEN tag = '020' THEN -- caseless -- isbn
+ LOWER((REGEXP_MATCH(value,$$^(\S{10,17})$$))[1] || '%')
+ WHEN tag = '022' THEN -- caseless -- issn
+ LOWER((REGEXP_MATCH(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
+ WHEN tag = '024' THEN -- caseless -- upc (other)
+ LOWER(value || '%')
+ ELSE
+ value
+ END AS value
+ FROM vandelay.flatten_marc(record_xml)) x
+ GROUP BY tag, subfield ORDER BY tag, subfield
+ ) subquery
+ );
+END;
+$func$ LANGUAGE PLPGSQL;
+
+COMMIT;
--
2.11.0