From 5758682f3750cd2e14cc485267ce7692a7301945 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Tue, 29 Nov 2011 15:15:15 -0500 Subject: [PATCH] Avoid {NULL} arrays in reporter schema definitions Whether we use ARRAY_ACCUM() or ARRAY_AGG(), we seem to get {NULL} array values back which then prevent reporter.materialized_simple_record from being populated with the new value (possibly because the regexp_replace() calls return a NULL on NULL input?). In any case, some CASE statements avoid that nonsense entirely. Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/reporter-schema.sql | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 25532b6e03..d1cf3350d2 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -156,10 +156,18 @@ SELECT r.id, r.tcn_value, FIRST(title.value) AS title, FIRST(author.value) AS author, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate, - ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, - ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn + ARRAY_TO_STRING(ARRAY_AGG( DISTINCT publisher.value), ', ') AS publisher, + ARRAY_TO_STRING(ARRAY_AGG( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate, + CASE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) + WHEN '{NULL}' THEN '{}'::text[] + ELSE + ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) + END AS isbn, + CASE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) + WHEN '{NULL}' THEN '{}'::text[] + ELSE + ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) + END AS issn FROM biblio.record_entry r LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a') LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a') -- 2.11.0