Open-ILS/src/sql/Pg/upgrade/0880.function.authority.calculate_authority_linking.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0880', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION authority.calculate_authority_linking(
   6     rec_id BIGINT, rec_control_set INT, rec_marc_xml XML
   7 ) RETURNS SETOF authority.authority_linking AS $func$
   8 DECLARE
   9     acsaf       authority.control_set_authority_field%ROWTYPE;
  10     link        TEXT;
  11     aal         authority.authority_linking%ROWTYPE;
  12 BEGIN
  13     IF rec_control_set IS NULL THEN
  14         -- No control_set on record?  Guess at one
  15         SELECT control_set INTO rec_control_set
  16             FROM authority.control_set_authority_field
  17             WHERE tag IN (
  18                 SELECT UNNEST(
  19                     XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc_xml)::TEXT[]
  20                 )
  21             ) LIMIT 1;
  22
  23         IF NOT FOUND THEN
  24             RAISE WARNING 'Could not even guess at control set for authority record %', rec_id;
  25             RETURN;
  26         END IF;
  27     END IF;
  28
  29     aal.source := rec_id;
  30
  31     FOR acsaf IN
  32         SELECT * FROM authority.control_set_authority_field
  33         WHERE control_set = rec_control_set
  34             AND linking_subfield IS NOT NULL
  35             AND main_entry IS NOT NULL
  36     LOOP
  37         -- Loop over the trailing-number contents of all linking subfields
  38         FOR link IN
  39             SELECT  SUBSTRING( x::TEXT, '\d+$' )
  40               FROM  UNNEST(
  41                         XPATH(
  42                             '//*[@tag="'
  43                                 || acsaf.tag
  44                                 || '"]/*[@code="'
  45                                 || acsaf.linking_subfield
  46                                 || '"]/text()',
  47                             rec_marc_xml
  48                         )
  49                     ) x
  50         LOOP
  51
  52             -- Ignore links that are null, malformed, circular, or point to
  53             -- non-existent authority records.
  54             IF link IS NOT NULL AND link::BIGINT <> rec_id THEN
  55                 PERFORM * FROM authority.record_entry WHERE id = link::BIGINT;
  56                 IF FOUND THEN
  57                     aal.target := link::BIGINT;
  58                     aal.field := acsaf.id;
  59                     RETURN NEXT aal;
  60                 END IF;
  61             END IF;
  62         END LOOP;
  63     END LOOP;
  64 END;
  65 $func$ LANGUAGE PLPGSQL;
  66
  67 COMMIT;
  68