Open-ILS/src/sql/Pg/upgrade/XXXX.schema.lp1820339-pg10-vandelay.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   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;