3 --Check if we can apply the upgrade.
4 --SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
8 CREATE OR REPLACE FUNCTION search.query_parser_fts (
14 param_locations INT[],
21 param_pref_ou INT DEFAULT NULL
22 ) RETURNS SETOF search.search_result AS $func$
25 current_res search.search_result%ROWTYPE;
26 search_org_list INT[];
36 core_cursor REFCURSOR;
41 deleted_count INT := 0;
42 visible_count INT := 0;
43 excluded_count INT := 0;
47 check_limit := COALESCE( param_check, 1000 );
48 core_limit := COALESCE( param_limit, 25000 );
49 core_offset := COALESCE( param_offset, 0 );
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_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
57 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
60 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
62 ELSIF param_search_ou < 0 THEN
63 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
65 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
66 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
67 luri_org_list := luri_org_list || tmp_int_list;
70 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
72 ELSIF param_search_ou = 0 THEN
73 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
76 IF param_pref_ou IS NOT NULL THEN
77 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
78 luri_org_list := luri_org_list || tmp_int_list;
81 OPEN core_cursor FOR EXECUTE param_query;
85 FETCH core_cursor INTO core_result;
87 EXIT WHEN total_count >= core_limit;
89 total_count := total_count + 1;
91 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
93 check_count := check_count + 1;
95 IF NOT deleted_search THEN
97 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
99 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
100 deleted_count := deleted_count + 1;
105 FROM biblio.record_entry b
106 JOIN config.bib_source s ON (b.source = s.id)
108 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
111 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
112 visible_count := visible_count + 1;
114 current_res.id = core_result.id;
115 current_res.rel = core_result.rel;
119 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
123 current_res.record = core_result.records[1];
125 current_res.record = NULL;
128 RETURN NEXT current_res;
134 FROM asset.call_number cn
135 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
136 JOIN asset.uri uri ON (map.uri = uri.id)
138 AND cn.label = '##URI##'
140 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
141 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
142 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
146 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
147 visible_count := visible_count + 1;
149 current_res.id = core_result.id;
150 current_res.rel = core_result.rel;
154 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
158 current_res.record = core_result.records[1];
160 current_res.record = NULL;
163 RETURN NEXT current_res;
168 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
171 FROM asset.call_number cn
172 JOIN asset.copy cp ON (cp.call_number = cn.id)
175 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
176 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
177 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
182 FROM biblio.peer_bib_copy_map pr
183 JOIN asset.copy cp ON (cp.id = pr.target_copy)
185 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
186 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
187 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
191 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
192 excluded_count := excluded_count + 1;
199 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
202 FROM asset.call_number cn
203 JOIN asset.copy cp ON (cp.call_number = cn.id)
206 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
207 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
208 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
213 FROM biblio.peer_bib_copy_map pr
214 JOIN asset.copy cp ON (cp.id = pr.target_copy)
216 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
217 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
218 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
222 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
223 excluded_count := excluded_count + 1;
230 IF staff IS NULL OR NOT staff THEN
233 FROM asset.opac_visible_copies
234 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
235 AND record IN ( SELECT * FROM unnest( core_result.records ) )
240 FROM biblio.peer_bib_copy_map pr
241 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
242 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
243 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
248 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
249 excluded_count := excluded_count + 1;
257 FROM asset.call_number cn
258 JOIN asset.copy cp ON (cp.call_number = cn.id)
261 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
262 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
268 FROM biblio.peer_bib_copy_map pr
269 JOIN asset.copy cp ON (cp.id = pr.target_copy)
271 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
272 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
278 FROM asset.call_number cn
279 JOIN asset.copy cp ON (cp.call_number = cn.id)
280 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
285 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
286 excluded_count := excluded_count + 1;
297 visible_count := visible_count + 1;
299 current_res.id = core_result.id;
300 current_res.rel = core_result.rel;
304 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
308 current_res.record = core_result.records[1];
310 current_res.record = NULL;
313 RETURN NEXT current_res;
315 IF visible_count % 1000 = 0 THEN
316 -- RAISE NOTICE ' % visible so far ... ', visible_count;
321 current_res.id = NULL;
322 current_res.rel = NULL;
323 current_res.record = NULL;
324 current_res.total = total_count;
325 current_res.checked = check_count;
326 current_res.deleted = deleted_count;
327 current_res.visible = visible_count;
328 current_res.excluded = excluded_count;
332 RETURN NEXT current_res;
335 $func$ LANGUAGE PLPGSQL;