From 1fe59bd9b407012bad144c4af33d08af0d6a170e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 5 Apr 2013 16:46:21 -0400 Subject: [PATCH] Bring back #deleted modifier post-revive-qp-fts Signed-off-by: Mike Rylander Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- .../Application/Storage/Publisher/metabib.pm | 2 + .../src/sql/Pg/300.schema.staged_search.sql | 283 ++++++++------- .../XXXX.schema.deleted-search-fts.sql | 340 ++++++++++++++++++ 3 files changed, 486 insertions(+), 139 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm index 423d5d874c..9709fa8a8c 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm @@ -3117,6 +3117,7 @@ sub query_parser_fts { my $param_statuses = '$${' . join(',', map { s/\$//go; "\"$_\""} @statuses) . '}$$'; my $param_locations = '$${' . join(',', map { s/\$//go; "\"$_\""} @location) . '}$$'; my $staff = ($self->api_name =~ /staff/ or $query->parse_tree->find_modifier('staff')) ? "'t'" : "'f'"; + my $deleted_search = ($query->parse_tree->find_modifier('deleted')) ? "'t'" : "'f'"; my $metarecord = ($self->api_name =~ /metabib/ or $query->parse_tree->find_modifier('metabib') or $query->parse_tree->find_modifier('metarecord')) ? "'t'" : "'f'"; my $param_pref_ou = $pref_ou || 'NULL'; @@ -3133,6 +3134,7 @@ sub query_parser_fts { $param_limit\:\:INT, $metarecord\:\:BOOL, $staff\:\:BOOL, + $deleted_search\:\:BOOL, $param_pref_ou\:\:INT ); SQL diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index 26d9b530bf..6cd9918451 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -45,6 +45,7 @@ CREATE OR REPLACE FUNCTION search.query_parser_fts ( param_limit INT, metarecord BOOL, staff BOOL, + deleted_search BOOL, param_pref_ou INT DEFAULT NULL ) RETURNS SETOF search.search_result AS $func$ DECLARE @@ -119,198 +120,202 @@ BEGIN check_count := check_count + 1; - PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); - IF NOT FOUND THEN - -- RAISE NOTICE ' % were all deleted ... ', core_result.records; - deleted_count := deleted_count + 1; - CONTINUE; - END IF; - - PERFORM 1 - FROM biblio.record_entry b - JOIN config.bib_source s ON (b.source = s.id) - WHERE s.transcendant - AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); - - IF FOUND THEN - -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; - visible_count := visible_count + 1; - - current_res.id = core_result.id; - current_res.rel = core_result.rel; - - tmp_int := 1; - IF metarecord THEN - SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; - END IF; + IF NOT deleted_search THEN - IF tmp_int = 1 THEN - current_res.record = core_result.records[1]; - ELSE - current_res.record = NULL; + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; END IF; - RETURN NEXT current_res; + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); - CONTINUE; - END IF; + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; - PERFORM 1 - FROM asset.call_number cn - JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) - JOIN asset.uri uri ON (map.uri = uri.id) - WHERE NOT cn.deleted - AND cn.label = '##URI##' - AND uri.active - AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) - AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) ) - LIMIT 1; - - IF FOUND THEN - -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; - visible_count := visible_count + 1; - - current_res.id = core_result.id; - current_res.rel = core_result.rel; - - tmp_int := 1; - IF metarecord THEN - SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; - END IF; + current_res.id = core_result.id; + current_res.rel = core_result.rel; - IF tmp_int = 1 THEN - current_res.record = core_result.records[1]; - ELSE - current_res.record = NULL; - END IF; + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; - RETURN NEXT current_res; + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; - CONTINUE; - END IF; + RETURN NEXT current_res; - IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + CONTINUE; + END IF; PERFORM 1 FROM asset.call_number cn - JOIN asset.copy cp ON (cp.call_number = cn.id) + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) WHERE NOT cn.deleted - AND NOT cp.deleted - AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) ) LIMIT 1; - IF NOT FOUND THEN + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + PERFORM 1 - FROM biblio.peer_bib_copy_map pr - JOIN asset.copy cp ON (cp.id = pr.target_copy) - WHERE NOT cp.deleted + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) - AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN - -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; - excluded_count := excluded_count + 1; - CONTINUE; - END IF; - END IF; + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; - END IF; + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; - IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + END IF; - PERFORM 1 - FROM asset.call_number cn - JOIN asset.copy cp ON (cp.call_number = cn.id) - WHERE NOT cn.deleted - AND NOT cp.deleted - AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) - AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) - LIMIT 1; + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN - IF NOT FOUND THEN PERFORM 1 - FROM biblio.peer_bib_copy_map pr - JOIN asset.copy cp ON (cp.id = pr.target_copy) - WHERE NOT cp.deleted + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) - AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN - -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; - excluded_count := excluded_count + 1; - CONTINUE; - END IF; - END IF; + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; - END IF; + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; - IF staff IS NULL OR NOT staff THEN + END IF; - PERFORM 1 - FROM asset.opac_visible_copies - WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) - AND record IN ( SELECT * FROM unnest( core_result.records ) ) - LIMIT 1; + IF staff IS NULL OR NOT staff THEN - IF NOT FOUND THEN PERFORM 1 - FROM biblio.peer_bib_copy_map pr - JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) - WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; - -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; - excluded_count := excluded_count + 1; - CONTINUE; - END IF; - END IF; + IF NOT FOUND THEN - ELSE - - PERFORM 1 - FROM asset.call_number cn - JOIN asset.copy cp ON (cp.call_number = cn.id) - WHERE NOT cn.deleted - AND NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) - AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - LIMIT 1; + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; - IF NOT FOUND THEN + ELSE PERFORM 1 - FROM biblio.peer_bib_copy_map pr - JOIN asset.copy cp ON (cp.id = pr.target_copy) - WHERE NOT cp.deleted + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 - FROM asset.call_number cn - JOIN asset.copy cp ON (cp.call_number = cn.id) - WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - AND NOT cp.deleted + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; - IF FOUND THEN - -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; - excluded_count := excluded_count + 1; - CONTINUE; + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND NOT cp.deleted + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; END IF; + END IF; END IF; @@ -357,5 +362,5 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; + COMMIT; - diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql new file mode 100644 index 0000000000..e44c0fcd59 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.deleted-search-fts.sql @@ -0,0 +1,340 @@ +BEGIN; + +--Check if we can apply the upgrade. +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + + +CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT ); +CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT ); + +CREATE OR REPLACE FUNCTION search.query_parser_fts ( + + param_search_ou INT, + param_depth INT, + param_query TEXT, + param_statuses INT[], + param_locations INT[], + param_offset INT, + param_check INT, + param_limit INT, + metarecord BOOL, + staff BOOL, + deleted_search BOOL, + param_pref_ou INT DEFAULT NULL +) RETURNS SETOF search.search_result AS $func$ +DECLARE + + current_res search.search_result%ROWTYPE; + search_org_list INT[]; + luri_org_list INT[]; + tmp_int_list INT[]; + + check_limit INT; + core_limit INT; + core_offset INT; + tmp_int INT; + + core_result RECORD; + core_cursor REFCURSOR; + core_rel_query TEXT; + + total_count INT := 0; + check_count INT := 0; + deleted_count INT := 0; + visible_count INT := 0; + excluded_count INT := 0; + +BEGIN + + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + ELSE + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + END IF; + + SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); + + ELSIF param_search_ou < 0 THEN + SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + + FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); + luri_org_list := luri_org_list || tmp_int_list; + END LOOP; + + SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); + + ELSIF param_search_ou = 0 THEN + -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. + END IF; + + IF param_pref_ou IS NOT NULL THEN + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou); + luri_org_list := luri_org_list || tmp_int_list; + END IF; + + OPEN core_cursor FOR EXECUTE param_query; + + LOOP + + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; + + total_count := total_count + 1; + + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; + + check_count := check_count + 1; + + IF NOT deleted_search THEN + + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; + END IF; + + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF staff IS NULL OR NOT staff THEN + + PERFORM 1 + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + ELSE + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND NOT cp.deleted + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + END IF; + + END IF; + + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; + END IF; + + END LOOP; + + current_res.id = NULL; + current_res.rel = NULL; + current_res.record = NULL; + current_res.total = total_count; + current_res.checked = check_count; + current_res.deleted = deleted_count; + current_res.visible = visible_count; + current_res.excluded = excluded_count; + + CLOSE core_cursor; + + RETURN NEXT current_res; + +END; +$func$ LANGUAGE PLPGSQL; + + +COMMIT; -- 2.43.2