Open-ILS/src/sql/Pg/upgrade/0036.schema.flatten_marc.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0036'); -- miker
   4
   5 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
   6 DECLARE
   7         bib     biblio.record_entry%ROWTYPE;
   8         output  metabib.full_rec%ROWTYPE;
   9         field   RECORD;
  10 BEGIN
  11         SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
  12
  13         FOR field IN SELECT * FROM biblio.flatten_marc( bib.marc ) LOOP
  14                 output.record := rid;
  15                 output.ind1 := field.ind1;
  16                 output.ind2 := field.ind2;
  17                 output.tag := field.tag;
  18                 output.subfield := field.subfield;
  19                 IF field.subfield IS NOT NULL THEN
  20                         output.value := naco_normalize(field.value, field.subfield);
  21                 ELSE
  22                         output.value := field.value;
  23                 END IF;
  24
  25                 RETURN NEXT output;
  26         END LOOP;
  27 END;
  28 $func$ LANGUAGE PLPGSQL;
  29
  30 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( TEXT ) RETURNS SETOF metabib.full_rec AS $func$
  31
  32 use MARC::Record;
  33 use MARC::File::XML;
  34
  35 my $xml = shift;
  36 my $r = MARC::Record->new_from_xml( $xml );
  37
  38 return_next( { tag => 'LDR', value => $r->leader } );
  39
  40 for my $f ( $r->fields ) {
  41         if ($f->is_control_field) {
  42                 return_next({ tag => $f->tag, value => $f->data });
  43         } else {
  44                 for my $s ($f->subfields) {
  45                         return_next({
  46                                 tag      => $f->tag,
  47                                 ind1     => $f->indicator(1),
  48                                 ind2     => $f->indicator(2),
  49                                 subfield => $s->[0],
  50                                 value    => $s->[1]
  51                         });
  52
  53                         if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
  54                                 my $trim = $f->indicator(2) || 0;
  55                                 return_next({
  56                                         tag      => 'tnf',
  57                                         ind1     => $f->indicator(1),
  58                                         ind2     => $f->indicator(2),
  59                                         subfield => 'a',
  60                                         value    => substr( $s->[1], $trim )
  61                                 });
  62                         }
  63                 }
  64         }
  65 }
  66
  67 return undef;
  68
  69 $func$ LANGUAGE PLPERLU;
  70
  71 COMMIT;