2 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT );
3 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
5 CREATE OR REPLACE FUNCTION search.query_parser_fts (
11 param_locations INT[],
17 param_pref_ou INT DEFAULT NULL
18 ) RETURNS SETOF search.search_result AS $func$
21 current_res search.search_result%ROWTYPE;
22 search_org_list INT[];
32 core_cursor REFCURSOR;
37 deleted_count INT := 0;
38 visible_count INT := 0;
39 excluded_count INT := 0;
43 check_limit := COALESCE( param_check, 1000 );
44 core_limit := COALESCE( param_limit, 25000 );
45 core_offset := COALESCE( param_offset, 0 );
47 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
49 IF param_search_ou > 0 THEN
50 IF param_depth IS NOT NULL THEN
51 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
53 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
56 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( 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;
61 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
62 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
63 luri_org_list := luri_org_list || tmp_int_list;
66 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
68 ELSIF param_search_ou = 0 THEN
69 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
72 IF param_pref_ou IS NOT NULL THEN
73 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
74 luri_org_list := luri_org_list || tmp_int_list;
77 OPEN core_cursor FOR EXECUTE param_query;
81 FETCH core_cursor INTO core_result;
83 EXIT WHEN total_count >= core_limit;
85 total_count := total_count + 1;
87 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
89 check_count := check_count + 1;
91 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
93 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
94 deleted_count := deleted_count + 1;
99 FROM biblio.record_entry b
100 JOIN config.bib_source s ON (b.source = s.id)
102 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
105 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
106 visible_count := visible_count + 1;
108 current_res.id = core_result.id;
109 current_res.rel = core_result.rel;
113 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
117 current_res.record = core_result.records[1];
119 current_res.record = NULL;
122 RETURN NEXT current_res;
128 FROM asset.call_number cn
129 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
130 JOIN asset.uri uri ON (map.uri = uri.id)
132 AND cn.label = '##URI##'
134 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
135 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
136 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
140 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
141 visible_count := visible_count + 1;
143 current_res.id = core_result.id;
144 current_res.rel = core_result.rel;
148 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
152 current_res.record = core_result.records[1];
154 current_res.record = NULL;
157 RETURN NEXT current_res;
162 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
165 FROM asset.call_number cn
166 JOIN asset.copy cp ON (cp.call_number = cn.id)
169 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
170 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
171 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
176 FROM biblio.peer_bib_copy_map pr
177 JOIN asset.copy cp ON (cp.id = pr.target_copy)
179 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
180 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
181 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
185 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
186 excluded_count := excluded_count + 1;
193 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
196 FROM asset.call_number cn
197 JOIN asset.copy cp ON (cp.call_number = cn.id)
200 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
201 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
202 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
207 FROM biblio.peer_bib_copy_map pr
208 JOIN asset.copy cp ON (cp.id = pr.target_copy)
210 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
211 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
212 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
216 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
217 excluded_count := excluded_count + 1;
224 IF staff IS NULL OR NOT staff THEN
227 FROM asset.opac_visible_copies
228 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
229 AND record IN ( SELECT * FROM unnest( core_result.records ) )
234 FROM biblio.peer_bib_copy_map pr
235 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
236 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
237 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
242 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
243 excluded_count := excluded_count + 1;
251 FROM asset.call_number cn
252 JOIN asset.copy cp ON (cp.call_number = cn.id)
255 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
256 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
262 FROM biblio.peer_bib_copy_map pr
263 JOIN asset.copy cp ON (cp.id = pr.target_copy)
265 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
266 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
272 FROM asset.call_number cn
273 JOIN asset.copy cp ON (cp.call_number = cn.id)
274 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
279 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
280 excluded_count := excluded_count + 1;
289 visible_count := visible_count + 1;
291 current_res.id = core_result.id;
292 current_res.rel = core_result.rel;
296 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
300 current_res.record = core_result.records[1];
302 current_res.record = NULL;
305 RETURN NEXT current_res;
307 IF visible_count % 1000 = 0 THEN
308 -- RAISE NOTICE ' % visible so far ... ', visible_count;
313 current_res.id = NULL;
314 current_res.rel = NULL;
315 current_res.record = NULL;
316 current_res.total = total_count;
317 current_res.checked = check_count;
318 current_res.deleted = deleted_count;
319 current_res.visible = visible_count;
320 current_res.excluded = excluded_count;
324 RETURN NEXT current_res;
327 $func$ LANGUAGE PLPGSQL;