3 SELECT evergreen.upgrade_deps_block_check('0882', :eg_version);
5 CREATE OR REPLACE FUNCTION search.query_parser_fts (
11 param_locations INT[],
18 param_pref_ou INT DEFAULT NULL
19 ) RETURNS SETOF search.search_result AS $func$
22 current_res search.search_result%ROWTYPE;
23 search_org_list INT[];
33 core_cursor REFCURSOR;
38 deleted_count INT := 0;
39 visible_count INT := 0;
40 excluded_count INT := 0;
45 check_limit := COALESCE( param_check, 1000 );
46 core_limit := COALESCE( param_limit, 25000 );
47 core_offset := COALESCE( param_offset, 0 );
49 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
51 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
53 IF param_search_ou > 0 THEN
54 IF param_depth IS NOT NULL THEN
55 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
57 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
61 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
63 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
66 ELSIF param_search_ou < 0 THEN
67 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
69 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
72 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
74 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
77 luri_org_list := luri_org_list || tmp_int_list;
80 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
82 ELSIF param_search_ou = 0 THEN
83 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
86 IF param_pref_ou IS NOT NULL THEN
88 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
90 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
93 luri_org_list := luri_org_list || tmp_int_list;
96 OPEN core_cursor FOR EXECUTE param_query;
100 FETCH core_cursor INTO core_result;
102 EXIT WHEN total_count >= core_limit;
104 total_count := total_count + 1;
106 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
108 check_count := check_count + 1;
110 IF NOT deleted_search THEN
112 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
114 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
115 deleted_count := deleted_count + 1;
120 FROM biblio.record_entry b
121 JOIN config.bib_source s ON (b.source = s.id)
123 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
126 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
127 visible_count := visible_count + 1;
129 current_res.id = core_result.id;
130 current_res.rel = core_result.rel;
134 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
138 current_res.record = core_result.records[1];
140 current_res.record = NULL;
143 RETURN NEXT current_res;
149 FROM asset.call_number cn
150 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
151 JOIN asset.uri uri ON (map.uri = uri.id)
153 AND cn.label = '##URI##'
155 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
156 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
157 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
161 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
162 visible_count := visible_count + 1;
164 current_res.id = core_result.id;
165 current_res.rel = core_result.rel;
169 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
173 current_res.record = core_result.records[1];
175 current_res.record = NULL;
178 RETURN NEXT current_res;
183 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
186 FROM asset.call_number cn
187 JOIN asset.copy cp ON (cp.call_number = cn.id)
190 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
191 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
192 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
197 FROM biblio.peer_bib_copy_map pr
198 JOIN asset.copy cp ON (cp.id = pr.target_copy)
200 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
201 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
202 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
206 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
207 excluded_count := excluded_count + 1;
214 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
217 FROM asset.call_number cn
218 JOIN asset.copy cp ON (cp.call_number = cn.id)
221 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
222 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
223 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
228 FROM biblio.peer_bib_copy_map pr
229 JOIN asset.copy cp ON (cp.id = pr.target_copy)
231 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
232 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
233 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
237 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
238 excluded_count := excluded_count + 1;
245 IF staff IS NULL OR NOT staff THEN
248 FROM asset.opac_visible_copies
249 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
250 AND record IN ( SELECT * FROM unnest( core_result.records ) )
255 FROM biblio.peer_bib_copy_map pr
256 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
257 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
258 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
263 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
264 excluded_count := excluded_count + 1;
272 FROM asset.call_number cn
273 JOIN asset.copy cp ON (cp.call_number = cn.id)
276 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
277 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
283 FROM biblio.peer_bib_copy_map pr
284 JOIN asset.copy cp ON (cp.id = pr.target_copy)
286 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
287 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
293 FROM asset.call_number cn
294 JOIN asset.copy cp ON (cp.call_number = cn.id)
295 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
300 -- Recheck Located URI visibility in the case of no "foreign" copies
302 FROM asset.call_number cn
303 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
304 JOIN asset.uri uri ON (map.uri = uri.id)
306 AND cn.label = '##URI##'
308 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
309 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
313 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
314 excluded_count := excluded_count + 1;
318 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
319 excluded_count := excluded_count + 1;
330 visible_count := visible_count + 1;
332 current_res.id = core_result.id;
333 current_res.rel = core_result.rel;
337 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
341 current_res.record = core_result.records[1];
343 current_res.record = NULL;
346 RETURN NEXT current_res;
348 IF visible_count % 1000 = 0 THEN
349 -- RAISE NOTICE ' % visible so far ... ', visible_count;
354 current_res.id = NULL;
355 current_res.rel = NULL;
356 current_res.record = NULL;
357 current_res.total = total_count;
358 current_res.checked = check_count;
359 current_res.deleted = deleted_count;
360 current_res.visible = visible_count;
361 current_res.excluded = excluded_count;
365 RETURN NEXT current_res;
368 $func$ LANGUAGE PLPGSQL;