3 INSERT INTO config.upgrade_log (version) VALUES ('0202'); -- miker
8 FROM search.query_parser_fts(
9 $param_search_ou\:\:INT,
11 $param_core_query\:\:TEXT,
12 $param_statuses\:\:INT[],
13 $param_locations\:\:INT[],
18 CREATE OR REPLACE FUNCTION search.query_parser_fts (
24 param_locations INT[],
30 ) RETURNS SETOF search.search_result AS $func$
33 current_res search.search_result%ROWTYPE;
34 search_org_list INT[];
41 core_cursor REFCURSOR;
46 deleted_count INT := 0;
47 visible_count INT := 0;
48 excluded_count INT := 0;
52 core_offset := COALESCE( param_offset, 0 );
53 core_limit := COALESCE( param_limit, 1000 );
55 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
57 IF param_search_ou > 0 THEN
58 IF param_depth IS NOT NULL THEN
59 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
61 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
63 ELSIF param_search_ou < 0 THEN
64 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
65 ELSIF param_search_ou = 0 THEN
66 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
69 OPEN core_cursor FOR EXECUTE param_query;
73 FETCH core_cursor INTO core_result;
76 total_count := total_count + 1;
77 CONTINUE WHEN (total_count - 1 < core_offset);
79 check_count := check_count + 1;
80 CONTINUE WHEN (check_count > core_limit);
82 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
84 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
85 deleted_count := deleted_count + 1;
90 FROM biblio.record_entry b
91 JOIN config.bib_source s ON (b.source = s.id)
93 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
96 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
97 visible_count := visible_count + 1;
99 current_res.id = core_result.id;
100 current_res.rel = core_result.rel;
104 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
108 current_res.record = core_result.records[1];
110 current_res.record = NULL;
113 RETURN NEXT current_res;
119 FROM asset.call_number cn
120 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
121 JOIN asset.uri uri ON (map.uri = uri.id)
123 AND cn.label = '##URI##'
125 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
126 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
127 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
131 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
132 visible_count := visible_count + 1;
134 current_res.id = core_result.id;
135 current_res.rel = core_result.rel;
139 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
143 current_res.record = core_result.records[1];
145 current_res.record = NULL;
148 RETURN NEXT current_res;
153 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
156 FROM asset.call_number cn
157 JOIN asset.copy cp ON (cp.call_number = cn.id)
160 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
161 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
162 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
166 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
167 excluded_count := excluded_count + 1;
173 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
176 FROM asset.call_number cn
177 JOIN asset.copy cp ON (cp.call_number = cn.id)
180 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
181 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
182 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
186 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
187 excluded_count := excluded_count + 1;
193 IF staff IS NULL OR NOT staff THEN
196 FROM asset.call_number cn
197 JOIN asset.copy cp ON (cp.call_number = cn.id)
198 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
199 JOIN asset.copy_location cl ON (cp.location = cl.id)
200 JOIN config.copy_status cs ON (cp.status = cs.id)
207 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
208 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
212 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
213 excluded_count := excluded_count + 1;
220 FROM asset.call_number cn
221 JOIN asset.copy cp ON (cp.call_number = cn.id)
222 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
223 JOIN asset.copy_location cl ON (cp.location = cl.id)
226 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
227 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
233 FROM asset.call_number cn
234 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
238 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
239 excluded_count := excluded_count + 1;
247 visible_count := visible_count + 1;
249 current_res.id = core_result.id;
250 current_res.rel = core_result.rel;
254 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
258 current_res.record = core_result.records[1];
260 current_res.record = NULL;
263 RETURN NEXT current_res;
265 IF visible_count % 1000 = 0 THEN
266 -- RAISE NOTICE ' % visible so far ... ', visible_count;
271 current_res.id = NULL;
272 current_res.rel = NULL;
273 current_res.record = NULL;
274 current_res.total = total_count;
275 current_res.checked = check_count;
276 current_res.deleted = deleted_count;
277 current_res.visible = visible_count;
278 current_res.excluded = excluded_count;
282 RETURN NEXT current_res;
285 $func$ LANGUAGE PLPGSQL;