3 --Check if we can apply the upgrade.
4 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
7 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT );
8 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
10 CREATE OR REPLACE FUNCTION search.query_parser_fts (
16 param_locations INT[],
23 param_pref_ou INT DEFAULT NULL
24 ) RETURNS SETOF search.search_result AS $func$
27 current_res search.search_result%ROWTYPE;
28 search_org_list INT[];
38 core_cursor REFCURSOR;
43 deleted_count INT := 0;
44 visible_count INT := 0;
45 excluded_count INT := 0;
49 check_limit := COALESCE( param_check, 1000 );
50 core_limit := COALESCE( param_limit, 25000 );
51 core_offset := COALESCE( param_offset, 0 );
53 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
55 IF param_search_ou > 0 THEN
56 IF param_depth IS NOT NULL THEN
57 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
59 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
62 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
64 ELSIF param_search_ou < 0 THEN
65 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
67 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
68 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
69 luri_org_list := luri_org_list || tmp_int_list;
72 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
74 ELSIF param_search_ou = 0 THEN
75 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
78 IF param_pref_ou IS NOT NULL THEN
79 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
80 luri_org_list := luri_org_list || tmp_int_list;
83 OPEN core_cursor FOR EXECUTE param_query;
87 FETCH core_cursor INTO core_result;
89 EXIT WHEN total_count >= core_limit;
91 total_count := total_count + 1;
93 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
95 check_count := check_count + 1;
97 IF NOT deleted_search THEN
99 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
101 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
102 deleted_count := deleted_count + 1;
107 FROM biblio.record_entry b
108 JOIN config.bib_source s ON (b.source = s.id)
110 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
113 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
114 visible_count := visible_count + 1;
116 current_res.id = core_result.id;
117 current_res.rel = core_result.rel;
121 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
125 current_res.record = core_result.records[1];
127 current_res.record = NULL;
130 RETURN NEXT current_res;
136 FROM asset.call_number cn
137 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
138 JOIN asset.uri uri ON (map.uri = uri.id)
140 AND cn.label = '##URI##'
142 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
143 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
144 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
148 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
149 visible_count := visible_count + 1;
151 current_res.id = core_result.id;
152 current_res.rel = core_result.rel;
156 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
160 current_res.record = core_result.records[1];
162 current_res.record = NULL;
165 RETURN NEXT current_res;
170 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
173 FROM asset.call_number cn
174 JOIN asset.copy cp ON (cp.call_number = cn.id)
177 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
178 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
179 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
184 FROM biblio.peer_bib_copy_map pr
185 JOIN asset.copy cp ON (cp.id = pr.target_copy)
187 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
188 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
189 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
193 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
194 excluded_count := excluded_count + 1;
201 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
204 FROM asset.call_number cn
205 JOIN asset.copy cp ON (cp.call_number = cn.id)
208 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
209 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
210 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
215 FROM biblio.peer_bib_copy_map pr
216 JOIN asset.copy cp ON (cp.id = pr.target_copy)
218 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
219 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
220 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
224 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
225 excluded_count := excluded_count + 1;
232 IF staff IS NULL OR NOT staff THEN
235 FROM asset.opac_visible_copies
236 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
237 AND record IN ( SELECT * FROM unnest( core_result.records ) )
242 FROM biblio.peer_bib_copy_map pr
243 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
244 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
245 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
250 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
251 excluded_count := excluded_count + 1;
259 FROM asset.call_number cn
260 JOIN asset.copy cp ON (cp.call_number = cn.id)
263 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
264 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
270 FROM biblio.peer_bib_copy_map pr
271 JOIN asset.copy cp ON (cp.id = pr.target_copy)
273 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
274 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
280 FROM asset.call_number cn
281 JOIN asset.copy cp ON (cp.call_number = cn.id)
282 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
287 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
288 excluded_count := excluded_count + 1;
299 visible_count := visible_count + 1;
301 current_res.id = core_result.id;
302 current_res.rel = core_result.rel;
306 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
310 current_res.record = core_result.records[1];
312 current_res.record = NULL;
315 RETURN NEXT current_res;
317 IF visible_count % 1000 = 0 THEN
318 -- RAISE NOTICE ' % visible so far ... ', visible_count;
323 current_res.id = NULL;
324 current_res.rel = NULL;
325 current_res.record = NULL;
326 current_res.total = total_count;
327 current_res.checked = check_count;
328 current_res.deleted = deleted_count;
329 current_res.visible = visible_count;
330 current_res.excluded = excluded_count;
334 RETURN NEXT current_res;
337 $func$ LANGUAGE PLPGSQL;