4 INSERT INTO config.upgrade_log (version) VALUES ('0223'); -- miker
6 DROP FUNCTION search.query_parser_fts ( INT,INT,TEXT,INT[],INT[],INT,INT,BOOL,BOOL );
8 CREATE OR REPLACE FUNCTION search.query_parser_fts (
14 param_locations INT[],
21 ) RETURNS SETOF search.search_result AS $func$
24 current_res search.search_result%ROWTYPE;
25 search_org_list INT[];
33 core_cursor REFCURSOR;
38 deleted_count INT := 0;
39 visible_count INT := 0;
40 excluded_count INT := 0;
44 check_limit := COALESCE( param_check, 1000 );
45 core_limit := COALESCE( param_limit, 25000 );
46 core_offset := COALESCE( param_offset, 0 );
48 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
50 IF param_search_ou > 0 THEN
51 IF param_depth IS NOT NULL THEN
52 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
54 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
56 ELSIF param_search_ou < 0 THEN
57 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
58 ELSIF param_search_ou = 0 THEN
59 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
62 OPEN core_cursor FOR EXECUTE param_query;
66 FETCH core_cursor INTO core_result;
68 EXIT WHEN total_count >= core_limit;
70 total_count := total_count + 1;
72 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
74 check_count := check_count + 1;
76 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
78 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
79 deleted_count := deleted_count + 1;
84 FROM biblio.record_entry b
85 JOIN config.bib_source s ON (b.source = s.id)
87 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
90 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
91 visible_count := visible_count + 1;
93 current_res.id = core_result.id;
94 current_res.rel = core_result.rel;
98 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
102 current_res.record = core_result.records[1];
104 current_res.record = NULL;
107 RETURN NEXT current_res;
113 FROM asset.call_number cn
114 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
115 JOIN asset.uri uri ON (map.uri = uri.id)
117 AND cn.label = '##URI##'
119 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
120 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
121 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
125 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
126 visible_count := visible_count + 1;
128 current_res.id = core_result.id;
129 current_res.rel = core_result.rel;
133 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
137 current_res.record = core_result.records[1];
139 current_res.record = NULL;
142 RETURN NEXT current_res;
147 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
150 FROM asset.call_number cn
151 JOIN asset.copy cp ON (cp.call_number = cn.id)
154 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
155 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
156 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
160 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
161 excluded_count := excluded_count + 1;
167 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
170 FROM asset.call_number cn
171 JOIN asset.copy cp ON (cp.call_number = cn.id)
174 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
175 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
176 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
180 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
181 excluded_count := excluded_count + 1;
187 IF staff IS NULL OR NOT staff THEN
190 FROM asset.call_number cn
191 JOIN asset.copy cp ON (cp.call_number = cn.id)
192 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
193 JOIN asset.copy_location cl ON (cp.location = cl.id)
194 JOIN config.copy_status cs ON (cp.status = cs.id)
201 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
202 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
206 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
207 excluded_count := excluded_count + 1;
214 FROM asset.call_number cn
215 JOIN asset.copy cp ON (cp.call_number = cn.id)
216 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
217 JOIN asset.copy_location cl ON (cp.location = cl.id)
220 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
221 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
227 FROM asset.call_number cn
228 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
232 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
233 excluded_count := excluded_count + 1;
241 visible_count := visible_count + 1;
243 current_res.id = core_result.id;
244 current_res.rel = core_result.rel;
248 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
252 current_res.record = core_result.records[1];
254 current_res.record = NULL;
257 RETURN NEXT current_res;
259 IF visible_count % 1000 = 0 THEN
260 -- RAISE NOTICE ' % visible so far ... ', visible_count;
265 current_res.id = NULL;
266 current_res.rel = NULL;
267 current_res.record = NULL;
268 current_res.total = total_count;
269 current_res.checked = check_count;
270 current_res.deleted = deleted_count;
271 current_res.visible = visible_count;
272 current_res.excluded = excluded_count;
276 RETURN NEXT current_res;
279 $func$ LANGUAGE PLPGSQL;