1 -- Evergreen DB patch 0704.schema.query_parser_fts.sql
3 -- Add pref_ou query filter for preferred library searching
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0704', :eg_version);
11 -- Create the new 11-parameter function, featuring param_pref_ou
12 CREATE OR REPLACE FUNCTION search.query_parser_fts (
18 param_locations INT[],
24 param_pref_ou INT DEFAULT NULL
25 ) RETURNS SETOF search.search_result AS $func$
28 current_res search.search_result%ROWTYPE;
29 search_org_list INT[];
39 core_cursor REFCURSOR;
44 deleted_count INT := 0;
45 visible_count INT := 0;
46 excluded_count INT := 0;
50 check_limit := COALESCE( param_check, 1000 );
51 core_limit := COALESCE( param_limit, 25000 );
52 core_offset := COALESCE( param_offset, 0 );
54 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
56 IF param_search_ou > 0 THEN
57 IF param_depth IS NOT NULL THEN
58 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
60 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
63 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
65 ELSIF param_search_ou < 0 THEN
66 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
68 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
69 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
70 luri_org_list := luri_org_list || tmp_int_list;
73 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
75 ELSIF param_search_ou = 0 THEN
76 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
79 IF param_pref_ou IS NOT NULL THEN
80 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
81 luri_org_list := luri_org_list || tmp_int_list;
84 OPEN core_cursor FOR EXECUTE param_query;
88 FETCH core_cursor INTO core_result;
90 EXIT WHEN total_count >= core_limit;
92 total_count := total_count + 1;
94 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
96 check_count := check_count + 1;
98 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
100 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
101 deleted_count := deleted_count + 1;
106 FROM biblio.record_entry b
107 JOIN config.bib_source s ON (b.source = s.id)
109 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
112 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
113 visible_count := visible_count + 1;
115 current_res.id = core_result.id;
116 current_res.rel = core_result.rel;
120 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
124 current_res.record = core_result.records[1];
126 current_res.record = NULL;
129 RETURN NEXT current_res;
135 FROM asset.call_number cn
136 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
137 JOIN asset.uri uri ON (map.uri = uri.id)
139 AND cn.label = '##URI##'
141 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
142 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
143 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
147 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
148 visible_count := visible_count + 1;
150 current_res.id = core_result.id;
151 current_res.rel = core_result.rel;
155 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
159 current_res.record = core_result.records[1];
161 current_res.record = NULL;
164 RETURN NEXT current_res;
169 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
172 FROM asset.call_number cn
173 JOIN asset.copy cp ON (cp.call_number = cn.id)
176 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
177 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
178 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
183 FROM biblio.peer_bib_copy_map pr
184 JOIN asset.copy cp ON (cp.id = pr.target_copy)
186 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
187 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
188 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
192 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
193 excluded_count := excluded_count + 1;
200 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
203 FROM asset.call_number cn
204 JOIN asset.copy cp ON (cp.call_number = cn.id)
207 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
208 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
209 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
214 FROM biblio.peer_bib_copy_map pr
215 JOIN asset.copy cp ON (cp.id = pr.target_copy)
217 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
218 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
219 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
223 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
224 excluded_count := excluded_count + 1;
231 IF staff IS NULL OR NOT staff THEN
234 FROM asset.opac_visible_copies
235 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
236 AND record IN ( SELECT * FROM unnest( core_result.records ) )
241 FROM biblio.peer_bib_copy_map pr
242 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
243 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
244 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
249 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
250 excluded_count := excluded_count + 1;
258 FROM asset.call_number cn
259 JOIN asset.copy cp ON (cp.call_number = cn.id)
262 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
263 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
269 FROM biblio.peer_bib_copy_map pr
270 JOIN asset.copy cp ON (cp.id = pr.target_copy)
272 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
273 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
279 FROM asset.call_number cn
280 JOIN asset.copy cp ON (cp.call_number = cn.id)
281 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
286 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
287 excluded_count := excluded_count + 1;
296 visible_count := visible_count + 1;
298 current_res.id = core_result.id;
299 current_res.rel = core_result.rel;
303 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
307 current_res.record = core_result.records[1];
309 current_res.record = NULL;
312 RETURN NEXT current_res;
314 IF visible_count % 1000 = 0 THEN
315 -- RAISE NOTICE ' % visible so far ... ', visible_count;
320 current_res.id = NULL;
321 current_res.rel = NULL;
322 current_res.record = NULL;
323 current_res.total = total_count;
324 current_res.checked = check_count;
325 current_res.deleted = deleted_count;
326 current_res.visible = visible_count;
327 current_res.excluded = excluded_count;
331 RETURN NEXT current_res;
334 $func$ LANGUAGE PLPGSQL;
336 -- Drop the old 10-parameter function
337 DROP FUNCTION IF EXISTS search.query_parser_fts (
338 INT, INT, TEXT, INT[], INT[], INT, INT, INT, BOOL, BOOL