From aa0ec9cba396a8b89322fc05a3c0664e16e69f2e Mon Sep 17 00:00:00 2001 From: dbs Date: Sat, 12 Mar 2011 02:57:32 +0000 Subject: [PATCH] Resolve inconsistent results when invoking LOWER() in C vs. UTF8 locale databases As reported by the Fundamental Science Library of Armenia, patron searches were not retrieving patrons who were known to be part of the patron database. While the initial approach to solve this problem used an encode_utf8() call to encode the data before passing it on to the database, this turned out to break the patron search function in other environments. Testing by Dan Wells confirmed that the LOWER() function returned different results when invoked against text in a database created with LC_CTYPE=C vs a database created with LC_CTYPE=*.UTF-8. As the patron search function used a Perl lc() function call to convert the incoming data to lowercase, the success of the call depended on the LC_CTYPE value of the database. To avoid this problem in the future, we define our own evergreen.lowercase() function that can reliably produce lowercase text for characters outside the Latin1 range, and we convert our indexes and function calls to use that function consistently. git-svn-id: svn://svn.open-ils.org/ILS/trunk@19715 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../Application/Storage/Publisher/actor.pm | 30 ++-- .../lib/OpenILS/Application/Vandelay.pm | 4 +- .../lib/OpenILS/Reporter/SQLBuilder.pm | 2 +- .../lib/OpenILS/WWW/Reporter/transforms.pm | 2 +- Open-ILS/src/sql/Pg/002.functions.general.sql | 7 + Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/005.schema.actors.sql | 30 ++-- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 2 +- Open-ILS/src/sql/Pg/build-db.sh | 1 + .../Pg/upgrade/0498.lowercase_via_perl.sql | 130 ++++++++++++++++++ 10 files changed, 173 insertions(+), 37 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/002.functions.general.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0498.lowercase_via_perl.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm index 212dd4587b..3c6543a0e7 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm @@ -11,8 +11,6 @@ use DateTime::Format::ISO8601; use DateTime::Set; use DateTime::SpanSet; -use Encode; - my $_dt_parser = DateTime::Format::ISO8601->new; my $log = 'OpenSRF::Utils::Logger'; @@ -643,20 +641,20 @@ sub patron_search { # group 2 = phone, ident # group 3 = barcode - my $usr = join ' AND ', map { "LOWER(CAST($_ AS text)) ~ ?" } grep { ''.$$search{$_}{group} eq '0' } keys %$search; - my @usrv = map { "^" . encode_utf8($$search{$_}{value}) } grep { ''.$$search{$_}{group} eq '0' } keys %$search; + my $usr = join ' AND ', map { "evergreen.lowercase(CAST($_ AS text)) ~ ?" } grep { ''.$$search{$_}{group} eq '0' } keys %$search; + my @usrv = map { "^" . $$search{$_}{value} } grep { ''.$$search{$_}{group} eq '0' } keys %$search; - my $addr = join ' AND ', map { "LOWER(CAST($_ AS text)) ~ ?" } grep { ''.$$search{$_}{group} eq '1' } keys %$search; - my @addrv = map { "^" . encode_utf8($$search{$_}{value}) } grep { ''.$$search{$_}{group} eq '1' } keys %$search; + my $addr = join ' AND ', map { "evergreen.lowercase(CAST($_ AS text)) ~ ?" } grep { ''.$$search{$_}{group} eq '1' } keys %$search; + my @addrv = map { "^" . $$search{$_}{value} } grep { ''.$$search{$_}{group} eq '1' } keys %$search; - my $pv = encode_utf8($$search{phone}{value}); - my $iv = encode_utf8($$search{ident}{value}); - my $nv = encode_utf8($$search{name}{value}); - my $cv = encode_utf8($$search{card}{value}); + my $pv = $$search{phone}{value}; + my $iv = $$search{ident}{value}; + my $nv = $$search{name}{value}; + my $cv = $$search{card}{value}; my $card = ''; if ($cv) { - $card = 'JOIN (SELECT DISTINCT usr FROM actor.card WHERE LOWER(barcode) LIKE ?||\'%\') AS card ON (card.usr = users.id)'; + $card = 'JOIN (SELECT DISTINCT usr FROM actor.card WHERE evergreen.lowercase(barcode) LIKE ?||\'%\') AS card ON (card.usr = users.id)'; unshift(@usrv, $cv); } @@ -665,7 +663,7 @@ sub patron_search { my @phonev; if ($pv) { for my $p ( qw/day_phone evening_phone other_phone/ ) { - push @ps, "LOWER($p) ~ ?"; + push @ps, "evergreen.lowercase($p) ~ ?"; push @phonev, "^$pv"; } $phone = '(' . join(' OR ', @ps) . ')'; @@ -676,7 +674,7 @@ sub patron_search { my @identv; if ($iv) { for my $i ( qw/ident_value ident_value2/ ) { - push @is, "LOWER($i) ~ ?"; + push @is, "evergreen.lowercase($i) ~ ?"; push @identv, "^$iv"; } $ident = '(' . join(' OR ', @is) . ')'; @@ -687,7 +685,7 @@ sub patron_search { my @namev; if (0 && $nv) { for my $n ( qw/first_given_name second_given_name family_name/ ) { - push @ns, "LOWER($n) ~ ?"; + push @ns, "evergreen.lowercase($n) ~ ?"; push @namev, "^$nv"; } $name = '(' . join(' OR ', @ns) . ')'; @@ -724,8 +722,8 @@ sub patron_search { return undef if (!$select && !$card); - my $order_by = join ', ', map { 'LOWER(CAST(users.'. (split / /,$_)[0] . ' AS text)) ' . (split / /,$_)[1] } @$sort; - my $distinct_list = join ', ', map { 'LOWER(CAST(users.'. (split / /,$_)[0] . ' AS text))' } @$sort; + my $order_by = join ', ', map { 'evergreen.lowercase(CAST(users.'. (split / /,$_)[0] . ' AS text)) ' . (split / /,$_)[1] } @$sort; + my $distinct_list = join ', ', map { 'evergreen.lowercase(CAST(users.'. (split / /,$_)[0] . ' AS text))' } @$sort; my $group_list = $distinct_list; if ($inactive) { diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm index 1311800be0..119e766349 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm @@ -869,10 +869,10 @@ sub owner_queue_retrieve { if($self->{record_type} eq 'bib') { $queues = $e->search_vandelay_bib_queue( - [$search, {order_by => {vbq => 'lower(name)'}}]); + [$search, {order_by => {vbq => 'evergreen.lowercase(name)'}}]); } else { $queues = $e->search_vandelay_authority_queue( - [$search, {order_by => {vaq => 'lower(name)'}}]); + [$search, {order_by => {vaq => 'evergreen.lowercase(name)'}}]); } $conn->respond($_) for @$queues; $e->rollback; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm index 637cbc50d8..b7d7177322 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm @@ -596,7 +596,7 @@ sub toSQL { my $params = $self->resolve_param( $self->{_column}->{params} ); my $start = $$params[0]; my $len = $$params[1]; - return 'LOWER("' . $self->{_relation} . '"."' . $self->name . '")'; + return 'evergreen.lowercase("' . $self->{_relation} . '"."' . $self->name . '")'; } sub is_aggregate { return 0 } diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/Reporter/transforms.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/Reporter/transforms.pm index 8123d15c41..dbf4dacf59 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/Reporter/transforms.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/Reporter/transforms.pm @@ -57,7 +57,7 @@ our $dtype_xforms = { 'group' => 1 }, 'lower' => { 'label' => 'Transform string to lower case', - 'select' => 'LOWER(?COLNAME?)', + 'select' => 'evergreen.lowercase(?COLNAME?)', 'group' => 1 }, 'upper' => { 'label' => 'Transform string to upper case', diff --git a/Open-ILS/src/sql/Pg/002.functions.general.sql b/Open-ILS/src/sql/Pg/002.functions.general.sql new file mode 100644 index 0000000000..659adcc54c --- /dev/null +++ b/Open-ILS/src/sql/Pg/002.functions.general.sql @@ -0,0 +1,7 @@ +-- Rather than polluting the public schema with general Evergreen +-- functions, carve out a dedicated schema +CREATE SCHEMA evergreen; + +CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$ + return lc(shift); +$$ LANGUAGE PLPERLU STRICT IMMUTABLE; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index daff4d7a69..018d806aca 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0497'); -- miker for tsbere +INSERT INTO config.upgrade_log (version) VALUES ('0498'); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index a1ae60ce0d..a13644017e 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -97,18 +97,18 @@ CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup); CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address); CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address); -CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name)); -CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name)); -CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name)); +CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name)); +CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name)); +CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name)); -CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email)); +CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); -CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone)); -CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone)); -CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone)); +CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone)); +CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone)); +CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone)); -CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value)); -CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2)); +CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value)); +CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2)); CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$ BEGIN @@ -316,7 +316,7 @@ COMMENT ON TABLE actor.card IS $$ $$; CREATE INDEX actor_card_usr_idx ON actor.card (usr); -CREATE INDEX actor_card_barcode_lower_idx ON actor.card (lower(barcode)); +CREATE INDEX actor_card_barcode_evergreen.lowercase_idx ON actor.card (evergreen.lowercase(barcode)); CREATE TABLE actor.org_unit_type ( id SERIAL PRIMARY KEY, @@ -515,12 +515,12 @@ CREATE TABLE actor.usr_address ( CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr); -CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1)); -CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2)); +CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1)); +CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2)); -CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city)); -CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state)); -CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code)); +CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city)); +CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state)); +CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code)); CREATE TABLE actor.usr_password_reset ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 0add48df0e..476ee07745 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -1126,7 +1126,7 @@ BEGIN -- Looks like an ISBN? check for an isbn match IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN - FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP + FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE; IF FOUND THEN INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record); diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh index e1ba82cbf3..bc988d7099 100755 --- a/Open-ILS/src/sql/Pg/build-db.sh +++ b/Open-ILS/src/sql/Pg/build-db.sh @@ -86,6 +86,7 @@ ordered_file_list=" 002.schema.config.sql 002.functions.aggregate.sql 002.functions.config.sql + 002.functions.general.sql 005.schema.actors.sql 006.schema.permissions.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/0498.lowercase_via_perl.sql b/Open-ILS/src/sql/Pg/upgrade/0498.lowercase_via_perl.sql new file mode 100644 index 0000000000..da9cf0c05a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0498.lowercase_via_perl.sql @@ -0,0 +1,130 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0498'); -- dbs + +-- Rather than polluting the public schema with general Evergreen +-- functions, carve out a dedicated schema +CREATE SCHEMA evergreen; + +-- Replace all uses of PostgreSQL's built-in LOWER() function with +-- a more locale-savvy PLPERLU evergreen.lowercase() function +CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$ + return lc(shift); +$$ LANGUAGE PLPERLU STRICT IMMUTABLE; + +-- update actor.usr_address indexes +DROP INDEX IF EXISTS actor.actor_usr_addr_street1_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_street2_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_city_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_state_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_post_code_idx; + +CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1)); +CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2)); +CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city)); +CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state)); +CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code)); + +-- update actor.usr indexes +DROP INDEX IF EXISTS actor.actor_usr_first_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_second_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_family_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_email_idx; +DROP INDEX IF EXISTS actor.actor_usr_day_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_evening_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_other_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value2_idx; + +CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name)); +CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name)); +CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name)); +CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); +CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone)); +CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone)); +CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone)); +CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value)); +CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2)); + +CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$ +DECLARE + attr RECORD; + attr_def RECORD; + eg_rec RECORD; + id_value TEXT; + exact_id BIGINT; +BEGIN + + DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; + + SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; + + IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN + id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove); + + IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN + SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted; + SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1; + IF exact_id IS NOT NULL THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id); + END IF; + END IF; + END IF; + + IF exact_id IS NULL THEN + FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP + + -- All numbers? check for an id match + IF (attr.attr_value ~ $r$^\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- Looks like an ISBN? check for an isbn match + IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN + FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP + PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE; + IF FOUND THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record); + END IF; + END LOOP; + + -- subcheck for isbn-as-tcn + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for an OCLC tcn_value match + IF (attr.attr_value ~ $r$^o\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for a direct tcn_value match + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + + -- check for a direct item barcode match + FOR eg_rec IN + SELECT DISTINCT b.* + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE + LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + + END LOOP; + END IF; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + + +COMMIT; -- 2.43.2