1 --Upgrade Script for 2.8.2 to 2.8.3
2 \set eg_version '''2.8.3'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.8.3', :eg_version);
5 -- Evergreen DB patch 0924.schema.rank_cp_visibility.sql
7 -- rank_cp() is meant to return the most-available copies, so it needs to
8 -- factor in the opac_visible flag on the copies themselves
11 -- check whether patch can be applied
12 SELECT evergreen.upgrade_deps_block_check('0924', :eg_version);
14 -- function is being expanded and renamed, so drop the old version
15 DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT);
17 -- this version exists mainly to accommodate JSON query transform limitations
18 -- (the transform argument must be an IDL field, not an entire row/object)
19 -- XXX is there another way?
20 CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy_id BIGINT)
23 copy asset.copy%ROWTYPE;
25 SELECT * INTO copy FROM asset.copy WHERE id = copy_id;
26 RETURN evergreen.rank_cp(copy);
28 $$ LANGUAGE PLPGSQL STABLE;
30 CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy asset.copy)
35 WITH totally_available AS (
36 SELECT id, 0 AS avail_rank
37 FROM config.copy_status
38 WHERE opac_visible IS TRUE
39 AND copy_active IS TRUE
40 AND id != 1 -- "Checked out"
41 ), almost_available AS (
42 SELECT id, 10 AS avail_rank
43 FROM config.copy_status
44 WHERE holdable IS TRUE
45 AND opac_visible IS TRUE
46 AND copy_active IS FALSE
47 OR id = 1 -- "Checked out"
50 CASE WHEN NOT copy.opac_visible THEN 100 END,
51 (SELECT avail_rank FROM totally_available WHERE copy.status IN (id)),
52 CASE WHEN copy.holdable THEN
53 (SELECT avail_rank FROM almost_available WHERE copy.status IN (id))
60 $$ LANGUAGE PLPGSQL STABLE;
62 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
65 depth INT DEFAULT NULL,
66 slimit HSTORE DEFAULT NULL,
67 soffset HSTORE DEFAULT NULL,
68 pref_lib INT DEFAULT NULL,
69 includes TEXT[] DEFAULT NULL::TEXT[]
70 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
71 WITH RECURSIVE ou_depth AS (
76 FROM actor.org_unit_type aout
77 INNER JOIN actor.org_unit ou ON ou_type = aout.id
81 ), descendant_depth AS (
85 FROM actor.org_unit ou
86 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
87 JOIN anscestor_depth ad ON (ad.id = ou.id),
89 WHERE ad.depth = ou_depth.depth
94 FROM actor.org_unit ou
95 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
96 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
97 ), anscestor_depth AS (
101 FROM actor.org_unit ou
102 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
108 FROM actor.org_unit ou
109 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
110 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
112 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
115 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
116 SELECT acn.id, aou.name, acn.label_sortkey,
117 evergreen.rank_cp(acp),
119 FROM asset.call_number acn
120 JOIN asset.copy acp ON (acn.id = acp.call_number)
121 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
122 WHERE acn.record = ANY ($1)
123 AND acn.deleted IS FALSE
124 AND acp.deleted IS FALSE
125 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
128 FROM asset.opac_visible_copies
129 WHERE copy_id = acp.id AND record = acn.record
131 GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id
135 CASE WHEN aou.id = $2 THEN -20000 END,
136 CASE WHEN aou.id = $6 THEN -10000 END,
137 (SELECT distance - 5000
138 FROM actor.org_unit_descendants_distance($6) as x
139 WHERE x.id = aou.id AND $6 IN (
140 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
141 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
144 evergreen.rank_cp(acp)
147 GROUP BY ua.id, ua.name, ua.label_sortkey
148 ORDER BY rank, ua.name, ua.label_sortkey
149 LIMIT ($4 -> 'acn')::INT
150 OFFSET ($5 -> 'acn')::INT;
151 $$ LANGUAGE SQL STABLE ROWS 10;
153 CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
157 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
158 'tag:open-ils.org:U2@acn/' || acn.id AS id,
159 acn.id AS vol_id, o.shortname AS lib,
160 o.opac_visible AS opac_visible,
161 deleted, label, label_sortkey, label_class, record
163 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
165 WHEN ('acp' = ANY ($4)) THEN
166 CASE WHEN $6 IS NOT NULL THEN
167 XMLELEMENT( name copies,
168 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
169 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
170 evergreen.rank_cp(cp) AS rank_avail
172 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
173 WHERE cp.call_number = acn.id
174 AND cp.deleted IS FALSE
175 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
176 LIMIT ($7 -> 'acp')::INT
177 OFFSET ($8 -> 'acp')::INT
181 XMLELEMENT( name copies,
182 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
183 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
184 evergreen.rank_cp(cp) AS rank_avail
186 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
187 WHERE cp.call_number = acn.id
188 AND cp.deleted IS FALSE
189 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
190 LIMIT ($7 -> 'acp')::INT
191 OFFSET ($8 -> 'acp')::INT
199 (SELECT XMLAGG(auri) FROM (SELECT unapi.auri(uri,'xml','uri', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE call_number = acn.id)x)
201 unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
202 unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
203 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END
205 FROM asset.call_number acn
206 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
208 AND acn.deleted IS FALSE
209 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
210 $F$ LANGUAGE SQL STABLE;
213 SELECT evergreen.upgrade_deps_block_check('0925', :eg_version);
215 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
217 -- Only keeps the most recent five settings changes.
218 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
219 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4);
221 IF (TG_OP = 'UPDATE') THEN
223 ELSIF (TG_OP = 'INSERT') THEN
228 $oustl_limit$ LANGUAGE plpgsql;
230 DROP TRIGGER IF EXISTS limit_logs_oust ON config.org_unit_setting_type_log;
232 CREATE TRIGGER limit_logs_oust
233 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
234 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
237 SELECT evergreen.upgrade_deps_block_check('0926', :eg_version);
239 CREATE OR REPLACE FUNCTION acq.copy_fund_tags(
247 FOR fund_tag_rec IN SELECT * FROM acq.fund_tag_map WHERE fund=old_fund_id LOOP
249 INSERT INTO acq.fund_tag_map(fund, tag) VALUES(new_fund_id, fund_tag_rec.tag);
251 WHEN unique_violation THEN
252 -- RAISE NOTICE 'Fund tag already propagated', old_fund.id;
260 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
264 encumb_only BOOL DEFAULT FALSE,
265 include_desc BOOL DEFAULT TRUE
270 new_year INT := old_year + 1;
273 xfer_amount NUMERIC := 0;
277 roll_distrib_forms BOOL;
283 IF old_year IS NULL THEN
284 RAISE EXCEPTION 'Input year argument is NULL';
285 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
286 RAISE EXCEPTION 'Input year is out of range';
289 IF user_id IS NULL THEN
290 RAISE EXCEPTION 'Input user id argument is NULL';
293 IF org_unit_id IS NULL THEN
294 RAISE EXCEPTION 'Org unit id argument is NULL';
297 -- Validate the org unit
302 WHERE id = org_unit_id;
304 IF org_found IS NULL THEN
305 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
306 ELSIF encumb_only THEN
307 SELECT INTO perm_ous value::BOOL FROM
308 actor.org_unit_ancestor_setting(
309 'acq.fund.allow_rollover_without_money', org_unit_id
311 IF NOT FOUND OR NOT perm_ous THEN
312 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
317 -- Loop over the propagable funds to identify the details
318 -- from the old fund plus the id of the new one, if it exists.
328 newf.id AS new_fund_id
331 LEFT JOIN acq.fund AS newf
332 ON ( oldf.code = newf.code )
336 AND newf.year = new_year
337 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
338 OR (NOT include_desc AND oldf.org = org_unit_id ) )
340 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
342 IF roll_fund.new_fund_id IS NULL THEN
344 -- The old fund hasn't been propagated yet. Propagate it now.
346 INSERT INTO acq.fund (
354 balance_warning_percent,
360 roll_fund.currency_type,
364 roll_fund.balance_warning_percent,
365 roll_fund.balance_stop_percent
367 RETURNING id INTO new_fund;
369 PERFORM acq.copy_fund_tags(roll_fund.id,new_fund);
372 new_fund = roll_fund.new_fund_id;
375 -- Determine the amount to transfer
379 FROM acq.fund_spent_balance
380 WHERE fund = roll_fund.old_fund;
382 IF xfer_amount <> 0 THEN
383 IF NOT encumb_only AND roll_fund.rollover THEN
385 -- Transfer balance from old fund to new
387 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
389 PERFORM acq.transfer_fund(
399 -- Transfer balance from old fund to the void
401 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
403 PERFORM acq.transfer_fund(
409 'Rollover into the void'
414 IF roll_fund.rollover THEN
416 -- Move any lineitems from the old fund to the new one
417 -- where the associated debit is an encumbrance.
419 -- Any other tables tying expenditure details to funds should
420 -- receive similar treatment. At this writing there are none.
422 UPDATE acq.lineitem_detail
425 fund = roll_fund.old_fund -- this condition may be redundant
431 fund = roll_fund.old_fund
435 -- Move encumbrance debits from the old fund to the new fund
437 UPDATE acq.fund_debit
440 fund = roll_fund.old_fund
444 -- Rollover distribution formulae funds
445 SELECT INTO roll_distrib_forms value::BOOL FROM
446 actor.org_unit_ancestor_setting(
447 'acq.fund.rollover_distrib_forms', org_unit_id
450 IF roll_distrib_forms THEN
451 UPDATE acq.distribution_formula_entry
452 SET fund = roll_fund.new_fund_id
453 WHERE fund = roll_fund.old_fund;
457 -- Mark old fund as inactive, now that we've closed it
461 WHERE id = roll_fund.old_fund;
466 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
470 include_desc BOOL DEFAULT TRUE
482 IF old_year IS NULL THEN
483 RAISE EXCEPTION 'Input year argument is NULL';
484 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
485 RAISE EXCEPTION 'Input year is out of range';
488 IF user_id IS NULL THEN
489 RAISE EXCEPTION 'Input user id argument is NULL';
492 IF org_unit_id IS NULL THEN
493 RAISE EXCEPTION 'Org unit id argument is NULL';
495 SELECT TRUE INTO org_found
497 WHERE id = org_unit_id;
499 IF org_found IS NULL THEN
500 RAISE EXCEPTION 'Org unit id is invalid';
504 -- Loop over the applicable funds
506 FOR old_fund in SELECT * FROM acq.fund
510 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
511 OR (NOT include_desc AND org = org_unit_id ) )
515 INSERT INTO acq.fund (
523 balance_warning_percent,
529 old_fund.currency_type,
533 old_fund.balance_warning_percent,
534 old_fund.balance_stop_percent
536 RETURNING id INTO new_id;
538 WHEN unique_violation THEN
539 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
543 PERFORM acq.copy_fund_tags(old_fund.id,new_id);
545 --RAISE NOTICE 'Propagating fund % to fund %',
546 -- old_fund.code, new_id;
554 SELECT evergreen.upgrade_deps_block_check('0927', :eg_version);
556 CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS
558 COALESCE((SELECT circ_count FROM extend_reporter.legacy_circ_count WHERE id = cp.id), 0)
559 + (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id)
560 + (SELECT COUNT(*) FROM action.aged_circulation WHERE target_copy = cp.id) AS circ_count
563 -- make record attributes definitions that use xpath to extract values
567 SELECT evergreen.upgrade_deps_block_check('0936', :eg_version);
569 CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$
571 transformed_xml TEXT;
572 rmarc TEXT := prmarc;
576 xfrm config.xml_transform%ROWTYPE;
577 attr_vector INT[] := '{}'::INT[];
578 attr_vector_tmp INT[];
579 attr_list TEXT[] := pattr_list;
581 norm_attr_value TEXT[];
583 attr_def config.record_attr_definition%ROWTYPE;
584 ccvm_row config.coded_value_map%ROWTYPE;
587 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
588 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
591 IF rmarc IS NULL THEN
592 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
595 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
597 attr_value := '{}'::TEXT[];
598 norm_attr_value := '{}'::TEXT[];
599 attr_vector_tmp := '{}'::INT[];
601 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
603 -- tag+sf attrs only support SVF
604 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
605 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
606 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
608 AND tag LIKE attr_def.tag
610 WHEN attr_def.sf_list IS NOT NULL
611 THEN POSITION(subfield IN attr_def.sf_list) > 0
618 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
619 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
621 IF NOT attr_def.multi THEN
622 attr_value := ARRAY[attr_value[1]];
625 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
627 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
629 -- See if we can skip the XSLT ... it's expensive
630 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
631 -- Can't skip the transform
632 IF xfrm.xslt <> '---' THEN
633 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
635 transformed_xml := rmarc;
638 prev_xfrm := xfrm.name;
641 IF xfrm.name IS NULL THEN
642 -- just grab the marcxml (empty) transform
643 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
644 prev_xfrm := xfrm.name;
647 FOR tmp_xml IN SELECT UNNEST(oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]])) LOOP
648 tmp_val := oils_xpath_string(
651 COALESCE(attr_def.joiner,' '),
652 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
654 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
655 attr_value := attr_value || tmp_val;
656 EXIT WHEN NOT attr_def.multi;
660 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
661 SELECT ARRAY_AGG(m.value) INTO attr_value
662 FROM vandelay.marc21_physical_characteristics(rmarc) v
663 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
664 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
665 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
667 IF NOT attr_def.multi THEN
668 attr_value := ARRAY[attr_value[1]];
673 -- apply index normalizers to attr_value
674 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
676 SELECT n.func AS func,
677 n.param_count AS param_count,
679 FROM config.index_normalizer n
680 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
681 WHERE attr = attr_def.name
683 EXECUTE 'SELECT ' || normalizer.func || '(' ||
684 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
686 WHEN normalizer.param_count > 0
687 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
693 IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
694 -- note that a string that contains only blanks
695 -- is a valid value for some attributes
696 norm_attr_value := norm_attr_value || tmp_val;
700 IF attr_def.filter THEN
701 -- Create unknown uncontrolled values and find the IDs of the values
702 IF ccvm_row.id IS NULL THEN
703 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
704 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
705 BEGIN -- use subtransaction to isolate unique constraint violations
706 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
707 EXCEPTION WHEN unique_violation THEN END;
711 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value );
713 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
716 -- Add the new value to the vector
717 attr_vector := attr_vector || attr_vector_tmp;
720 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
721 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
722 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
727 /* We may need to rewrite the vlist to contain
728 the intersection of new values for requested
729 attrs and old values for ignored attrs. To
730 do this, we take the old attr vlist and
731 subtract any values that are valid for the
732 requested attrs, and then add back the new
733 set of attr values. */
735 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
736 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
737 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
738 attr_vector := attr_vector || attr_vector_tmp;
741 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
742 -- attributes can depend on earlier ones.
743 PERFORM metabib.compile_composite_attr_cache_init();
744 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
746 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
748 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
749 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
751 IF attr_def.filter THEN
752 IF attr_vector @@ tmp_val::query_int THEN
753 attr_vector = attr_vector + intset(ccvm_row.id);
754 EXIT WHEN NOT attr_def.multi;
758 IF attr_def.sorter THEN
759 IF attr_vector @@ tmp_val THEN
760 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
761 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
769 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
770 IF rdeleted THEN -- initial insert OR revivication
771 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
772 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
774 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
780 $func$ LANGUAGE PLPGSQL;
783 SELECT evergreen.upgrade_deps_block_check('0940', :eg_version);
785 CREATE OR REPLACE FUNCTION evergreen.pg_statistics (tab TEXT, col TEXT) RETURNS TABLE(element TEXT, frequency INT) AS $$
787 -- This query will die on PG < 9.2, but the function can be created. We just won't use it where we can't.
791 FROM (SELECT ROW_NUMBER() OVER (),
793 FROM (SELECT UNNEST(most_common_elem_freqs) AS f
795 WHERE tablename = tab
799 JOIN (SELECT ROW_NUMBER() OVER (),
801 FROM (SELECT UNNEST(most_common_elems::text::text[]) AS e
803 WHERE tablename = tab
806 ) AS elems USING (row_number);
810 CREATE OR REPLACE FUNCTION evergreen.query_int_wrapper (INT[],TEXT) RETURNS BOOL AS $$
812 RETURN $1 @@ $2::query_int;
814 $$ LANGUAGE PLPGSQL STABLE;