Stamping upgrade script for Vandelay on PG10
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 1163.schema.lp1820339-pg10-vandelay.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('1163', :eg_version); -- JBoyer/Dyrcona/bshum/JBoyer
4
5 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
6     record_xml TEXT
7 ) RETURNS HSTORE AS $func$
8 BEGIN
9     RETURN (SELECT
10         HSTORE(
11             ARRAY_AGG(tag || (COALESCE(subfield, ''))),
12             ARRAY_AGG(value)
13         )
14         FROM (
15             SELECT  tag, subfield, ARRAY_AGG(value)::TEXT AS value
16               FROM  (SELECT tag,
17                             subfield,
18                             CASE WHEN tag = '020' THEN -- caseless -- isbn
19                                 LOWER((SELECT REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
20                             WHEN tag = '022' THEN -- caseless -- issn
21                                 LOWER((SELECT REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
22                             WHEN tag = '024' THEN -- caseless -- upc (other)
23                                 LOWER(value || '%')
24                             ELSE
25                                 value
26                             END AS value
27                       FROM  vandelay.flatten_marc(record_xml)) x
28                 GROUP BY tag, subfield ORDER BY tag, subfield
29         ) subquery
30     );
31 END;
32 $func$ LANGUAGE PLPGSQL;
33
34 COMMIT;