1 -- Evergreen DB patch 0573.schema.staff_search_find_no_copies.sql
7 -- check whether patch can be applied
8 SELECT evergreen.upgrade_deps_block_check('0573', :eg_version);
10 CREATE OR REPLACE FUNCTION search.query_parser_fts (
16 param_locations INT[],
23 ) RETURNS SETOF search.search_result AS $func$
26 current_res search.search_result%ROWTYPE;
27 search_org_list INT[];
35 core_cursor REFCURSOR;
40 deleted_count INT := 0;
41 visible_count INT := 0;
42 excluded_count INT := 0;
46 check_limit := COALESCE( param_check, 1000 );
47 core_limit := COALESCE( param_limit, 25000 );
48 core_offset := COALESCE( param_offset, 0 );
50 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
52 IF param_search_ou > 0 THEN
53 IF param_depth IS NOT NULL THEN
54 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
56 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
58 ELSIF param_search_ou < 0 THEN
59 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
60 ELSIF param_search_ou = 0 THEN
61 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
64 OPEN core_cursor FOR EXECUTE param_query;
68 FETCH core_cursor INTO core_result;
70 EXIT WHEN total_count >= core_limit;
72 total_count := total_count + 1;
74 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
76 check_count := check_count + 1;
78 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
80 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
81 deleted_count := deleted_count + 1;
86 FROM biblio.record_entry b
87 JOIN config.bib_source s ON (b.source = s.id)
89 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
92 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
93 visible_count := visible_count + 1;
95 current_res.id = core_result.id;
96 current_res.rel = core_result.rel;
100 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
104 current_res.record = core_result.records[1];
106 current_res.record = NULL;
109 RETURN NEXT current_res;
115 FROM asset.call_number cn
116 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
117 JOIN asset.uri uri ON (map.uri = uri.id)
119 AND cn.label = '##URI##'
121 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
122 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
123 AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) )
127 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
128 visible_count := visible_count + 1;
130 current_res.id = core_result.id;
131 current_res.rel = core_result.rel;
135 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
139 current_res.record = core_result.records[1];
141 current_res.record = NULL;
144 RETURN NEXT current_res;
149 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
152 FROM asset.call_number cn
153 JOIN asset.copy cp ON (cp.call_number = cn.id)
156 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
157 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
158 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
163 FROM biblio.peer_bib_copy_map pr
164 JOIN asset.copy cp ON (cp.id = pr.target_copy)
166 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
167 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
168 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
172 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
173 excluded_count := excluded_count + 1;
180 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
183 FROM asset.call_number cn
184 JOIN asset.copy cp ON (cp.call_number = cn.id)
187 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
188 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
189 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
194 FROM biblio.peer_bib_copy_map pr
195 JOIN asset.copy cp ON (cp.id = pr.target_copy)
197 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
198 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
199 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
203 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
204 excluded_count := excluded_count + 1;
211 IF staff IS NULL OR NOT staff THEN
214 FROM asset.opac_visible_copies
215 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
216 AND record IN ( SELECT * FROM unnest( core_result.records ) )
221 FROM biblio.peer_bib_copy_map pr
222 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
223 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
224 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
229 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
230 excluded_count := excluded_count + 1;
238 FROM asset.call_number cn
239 JOIN asset.copy cp ON (cp.call_number = cn.id)
242 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
243 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
249 FROM biblio.peer_bib_copy_map pr
250 JOIN asset.copy cp ON (cp.id = pr.target_copy)
252 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
253 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
259 FROM asset.call_number cn
260 JOIN asset.copy cp ON (cp.call_number = cn.id)
261 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
266 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
267 excluded_count := excluded_count + 1;
276 visible_count := visible_count + 1;
278 current_res.id = core_result.id;
279 current_res.rel = core_result.rel;
283 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
287 current_res.record = core_result.records[1];
289 current_res.record = NULL;
292 RETURN NEXT current_res;
294 IF visible_count % 1000 = 0 THEN
295 -- RAISE NOTICE ' % visible so far ... ', visible_count;
300 current_res.id = NULL;
301 current_res.rel = NULL;
302 current_res.record = NULL;
303 current_res.total = total_count;
304 current_res.checked = check_count;
305 current_res.deleted = deleted_count;
306 current_res.visible = visible_count;
307 current_res.excluded = excluded_count;
311 RETURN NEXT current_res;
314 $func$ LANGUAGE PLPGSQL;