1 DROP TABLE IF EXISTS migration_tools.search_format_map;
2 CREATE TABLE migration_tools.search_format_map (code TEXT, itype CHAR(1), iform CHAR(1), phy CHAR(1), phyv CHAR(1), phyp SMALLINT,
3 biblevel CHAR(1), iform_exclude CHAR(1)[], srform_exclude CHAR(1)[] );
4 INSERT INTO migration_tools.search_format_map (code, itype, iform, phy, phyv, phyp, biblevel, iform_exclude, srform_exclude) VALUES
5 -- itype iform phy phyv phyp bib itemform exclude sr format exclude
6 ('blu-ray', 'g', NULL, 'v', 's', 4, NULL, NULL, NULL)
7 ,('book', 'a', NULL, NULL, NULL, NULL, 'a', '{a,b,c,f,o,q,r,s}', NULL)
8 ,('braille', 'a', 'f', NULL, NULL, NULL, NULL, NULL, NULL)
9 ,('casaudiobook', 'i', NULL, 's', 'l', 3, NULL, NULL, NULL)
10 ,('casmusic', 'j', NULL, 's', 'l', 3, NULL, NULL, NULL)
11 ,('cdaudiobook', 'i', NULL, 's', 'f', 3, NULL, NULL, NULL)
12 ,('cdmusic', 'j', NULL, 's', 'f', 3, NULL, NULL, NULL)
13 ,('dvd', 'g', NULL, 'v', 'v', 4, NULL, NULL, NULL)
14 ,('eaudio', 'i', 'o', NULL, NULL, NULL, NULL, NULL, NULL)
15 ,('ebook', 'a', 's', NULL, NULL, NULL, 'a' , NULL, NULL)
16 ,('electronic', 's', 'o', NULL, NULL, NULL, NULL, NULL, NULL)
17 ,('equip', 'r', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
18 ,('evideo', 'g', 'o', NULL, NULL, NULL, NULL, NULL, NULL)
19 ,('kit', 'o', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
20 ,('lpbook', 'a', 'd', NULL, NULL, NULL, 'a' , NULL, NULL)
21 ,('map', 'e', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
22 ,('microform', 'a', 'b', NULL, NULL, NULL, NULL, NULL, NULL)
23 ,('music', 'j', NULL, NULL, NULL, NULL, NULL, NULL, '{a,b,c,d,e,f}')
24 ,('phonomusic', 'j', NULL, 's', 'a', 3, NULL, NULL, NULL)
25 ,('phonospoken', 'i', NULL, 's', 'a', 3, NULL, NULL, NULL)
26 ,('picture', 'k', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
27 ,('preloadedaudio', 'i', 'q', NULL, NULL, NULL, NULL, NULL, '{a,b,c,d,e,f,s}')
28 ,('score', 'c', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
29 ,('serial', NULL, NULL, NULL, NULL, NULL, 's' , NULL, NULL)
30 ,('software', 'm', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
31 ,('vhs', 'g', NULL, 'v', 'b', 4, NULL, NULL, NULL)
34 CREATE OR REPLACE FUNCTION migration_tools.reingest_staged_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS INTEGER[] AS $func$
41 xfrm config.xml_transform%ROWTYPE;
42 attr_vector INT[] := '{}'::INT[];
43 attr_vector_tmp INT[];
44 attr_list TEXT[] := pattr_list;
46 norm_attr_value TEXT[];
49 attr_def config.record_attr_definition%ROWTYPE;
50 ccvm_row config.coded_value_map%ROWTYPE;
54 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
55 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition
58 fixed_field IS NOT NULL OR
60 phys_char_sf IS NOT NULL OR
67 SELECT marc INTO rmarc FROM biblio_record_entry_legacy WHERE id = rid;
70 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
72 jump_past := FALSE; -- This gets set when we are non-multi and have found something
73 attr_value := '{}'::TEXT[];
74 norm_attr_value := '{}'::TEXT[];
75 attr_vector_tmp := '{}'::INT[];
77 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
79 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
80 SELECT ARRAY_AGG(value) INTO attr_value
81 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
83 AND tag LIKE attr_def.tag
85 WHEN attr_def.sf_list IS NOT NULL
86 THEN POSITION(subfield IN attr_def.sf_list) > 0
92 IF NOT attr_def.multi THEN
93 attr_value := ARRAY[ARRAY_TO_STRING(attr_value, COALESCE(attr_def.joiner,' '))];
98 IF NOT jump_past AND attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
99 attr_value := attr_value || vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
101 IF NOT attr_def.multi THEN
102 attr_value := ARRAY[attr_value[1]];
107 IF NOT jump_past AND attr_def.xpath IS NOT NULL THEN -- and xpath expression
109 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
111 -- See if we can skip the XSLT ... it's expensive
112 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
113 -- Can't skip the transform
114 IF xfrm.xslt <> '---' THEN
115 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
117 transformed_xml := rmarc;
120 prev_xfrm := xfrm.name;
123 IF xfrm.name IS NULL THEN
124 -- just grab the marcxml (empty) transform
125 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
126 prev_xfrm := xfrm.name;
129 FOR tmp_xml IN SELECT UNNEST(oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]])) LOOP
130 tmp_val := oils_xpath_string(
133 COALESCE(attr_def.joiner,' '),
134 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
136 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
137 attr_value := attr_value || tmp_val;
138 EXIT WHEN NOT attr_def.multi;
143 IF NOT jump_past AND attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
144 SELECT ARRAY_AGG(m.value) INTO tmp_array
145 FROM vandelay.marc21_physical_characteristics(rmarc) v
146 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
147 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
148 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
150 attr_value := attr_value || tmp_array;
152 IF NOT attr_def.multi THEN
153 attr_value := ARRAY[attr_value[1]];
158 -- apply index normalizers to attr_value
159 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
161 SELECT n.func AS func,
162 n.param_count AS param_count,
164 FROM config.index_normalizer n
165 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
166 WHERE attr = attr_def.name
168 EXECUTE 'SELECT ' || normalizer.func || '(' ||
169 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
171 WHEN normalizer.param_count > 0
172 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
178 IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
179 -- note that a string that contains only blanks
180 -- is a valid value for some attributes
181 norm_attr_value := norm_attr_value || tmp_val;
185 IF attr_def.filter THEN
186 -- Create unknown uncontrolled values and find the IDs of the values
187 IF ccvm_row.id IS NULL THEN
188 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
189 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
190 BEGIN -- use subtransaction to isolate unique constraint violations
191 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
192 EXCEPTION WHEN unique_violation THEN END;
196 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 );
198 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
201 -- Add the new value to the vector
202 attr_vector := attr_vector || attr_vector_tmp;
207 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
208 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
209 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
210 attr_vector := attr_vector || attr_vector_tmp;
213 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
214 -- attributes can depend on earlier ones.
215 PERFORM metabib.compile_composite_attr_cache_init();
216 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
218 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
220 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
221 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
223 IF attr_def.filter THEN
224 IF attr_vector @@ tmp_val::query_int THEN
225 attr_vector = attr_vector + intset(ccvm_row.id);
226 EXIT WHEN NOT attr_def.multi;
230 IF attr_def.sorter THEN
231 IF attr_vector @@ tmp_val THEN
232 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
233 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
243 $func$ LANGUAGE PLPGSQL;
246 CREATE OR REPLACE FUNCTION migration_tools.view_staged_vlist (rid BIGINT) RETURNS TABLE (r_ctype text, r_code text, r_value text) AS $func$
252 SELECT migration_tools.reingest_staged_record_attributes(rid) INTO vlist;
254 RETURN QUERY SELECT ctype, code, value FROM config.coded_value_map WHERE id IN (SELECT UNNEST(vlist));
256 $func$ LANGUAGE PLPGSQL;
258 CREATE OR REPLACE FUNCTION migration_tools.stage_vlist (rid BIGINT) RETURNS VOID AS $func$
263 SELECT migration_tools.reingest_staged_record_attributes(rid) INTO vlist;
265 SELECT ARRAY_AGG(code) FROM config.coded_value_map WHERE id IN (SELECT UNNEST(vlist))
266 AND ctype = 'search_format' INTO search;
268 UPDATE biblio_record_entry_legacy SET x_search_format = search WHERE id = rid;
270 $func$ LANGUAGE PLPGSQL;
272 CREATE OR REPLACE FUNCTION migration_tools.show_staged_vlist (rid BIGINT) RETURNS TEXT[] AS $func$
277 SELECT migration_tools.reingest_staged_record_attributes(rid) INTO vlist;
279 SELECT ARRAY_AGG(code) FROM config.coded_value_map WHERE id IN (SELECT UNNEST(vlist))
280 AND ctype = 'search_format' INTO search;
284 $func$ LANGUAGE PLPGSQL;
287 CREATE OR REPLACE FUNCTION migration_tools.postfix_vlist (rid BIGINT) RETURNS VOID AS $func$
292 SELECT migration_tools.reingest_staged_record_attributes(rid) INTO vlist;
294 SELECT ARRAY_AGG(code) FROM config.coded_value_map WHERE id IN (SELECT UNNEST(vlist))
295 AND ctype = 'search_format' INTO search;
297 UPDATE biblio_record_entry_legacy SET x_after_search_format = search WHERE id = rid;
299 $func$ LANGUAGE PLPGSQL;
302 CREATE OR REPLACE FUNCTION migration_tools.set_exp_sfs (rid BIGINT) RETURNS VOID AS $func$
308 SELECT circ_mods FROM biblio_record_entry_legacy WHERE id = rid INTO cms;
309 IF cms IS NOT NULL THEN
310 FOREACH y IN ARRAY cms LOOP
311 w := w || (SELECT sf1 FROM circ_mod_to_sf_map WHERE circ_mod = y);
312 w := w || (SELECT sf2 FROM circ_mod_to_sf_map WHERE circ_mod = y);
313 w := w || (SELECT sf3 FROM circ_mod_to_sf_map WHERE circ_mod = y);
315 UPDATE biblio_record_entry_legacy SET expected_sfs = w WHERE id = rid;
318 $func$ LANGUAGE PLPGSQL;
320 DROP AGGREGATE IF EXISTS anyarray_agg(anyarray);
321 CREATE AGGREGATE anyarray_agg(anyarray) (
322 SFUNC = migration_tools.anyarray_agg_statefunc,
326 DROP FUNCTION IF EXISTS migration_tools.anyarray_agg_statefunc(anyarray, anyarray);
327 CREATE FUNCTION migration_tools.anyarray_agg_statefunc(state anyarray, value anyarray)
330 SELECT array_cat($1, $2)
332 LANGUAGE sql IMMUTABLE;
334 DROP FUNCTION IF EXISTS migration_tools.anyarray_sort(anyarray);
335 CREATE OR REPLACE FUNCTION migration_tools.anyarray_sort(with_array anyarray)
339 return_array with_array%TYPE := '{}';
341 SELECT ARRAY_AGG(sorted_vals.val) AS array_value
343 ( SELECT UNNEST(with_array) AS val
345 ) AS sorted_vals INTO return_array;
348 $BODY$ LANGUAGE plpgsql;
350 DROP FUNCTION IF EXISTS migration_tools.anyarray_uniq(anyarray);
351 CREATE OR REPLACE FUNCTION migration_tools.anyarray_uniq(with_array anyarray)
355 -- The variable used to track iteration over "with_array".
358 -- The array to be returned by this function.
359 return_array with_array%TYPE := '{}';
361 IF with_array IS NULL THEN
365 IF with_array = '{}' THEN
369 -- Iterate over each element in "concat_array".
370 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
371 IF with_array[loop_offset] IS NULL THEN
373 ( SELECT 1 FROM UNNEST(return_array) AS s(a)
375 THEN return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
377 -- When an array contains a NULL value, ANY() returns NULL instead of FALSE...
378 ELSEIF NOT(with_array[loop_offset] = ANY(return_array)) OR NOT(NULL IS DISTINCT FROM (with_array[loop_offset] = ANY(return_array))) THEN
379 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
385 $BODY$ LANGUAGE plpgsql;
387 DROP FUNCTION IF EXISTS migration_tools.modify_staged_fixed_fields (BIGINT,TEXT);
388 CREATE OR REPLACE FUNCTION migration_tools.modify_staged_fixed_fields (bib_id BIGINT, xcode TEXT)
400 xiform_exclude CHAR(1)[];
401 xsrform_exclude CHAR(1)[];
403 ysrform_exclude TEXT;
405 SELECT itype, iform, phy, phyv, phyp, biblevel, iform_exclude, srform_exclude FROM migration_tools.search_format_map WHERE code = xcode
406 INTO xitype, xiform, xphy, xphyv, xphyp, xbiblevel, xiform_exclude, xsrform_exclude;
407 IF xiform_exclude IS NOT NULL THEN
408 yiform_exclude := ARRAY_TO_STRING(xiform_exclude,',');
410 yiform_exclude := '';
412 IF xsrform_exclude IS NOT NULL THEN
413 ysrform_exclude := ARRAY_TO_STRING(ysrform_exclude,',');
415 ysrform_exclude := '';
417 SELECT modify_fixed_fields(marc,xcode,xitype,xiform,xphy,xphyv,xphyp,xbiblevel,yiform_exclude,ysrform_exclude) FROM biblio_record_entry_legacy WHERE id = bib_id INTO r;
418 UPDATE biblio_record_entry_legacy SET marc = r WHERE id = bib_id;
423 DROP FUNCTION IF EXISTS migration_tools.modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT);
424 CREATE OR REPLACE FUNCTION migration_tools.modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT)
429 # assumption is that there should only be a single format per item
435 use MARC::File::XML (BinaryEncoding => 'utf8');
439 my ($marcxml, $code, $itype, $iform, $phy, $phyv, $phyp, $biblevel, $iform_exclude_temp, $srform_exclude_temp) = @_;
442 if ($iform_exclude_temp) { @iform_exclude = split /,/, $iform_exclude_temp; }
444 if ($srform_exclude_temp) { @srform_exclude = split /,/, $srform_exclude_temp; }
446 $marcxml =~ s/(<leader>.........)./${1}a/;
447 eval { $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); };
449 import MARC::File::XML (BinaryEncoding => 'utf8');
450 return 'failed to parse marcxml';
453 my $ldr = $marc->leader();
454 if ($itype) { substr($ldr,6,1) = $itype; } else { substr($ldr,6,1) = '|'; }
455 if ($biblevel) { substr($ldr,7,1) = $biblevel; } else { substr($ldr,7,1) = '|'; }
459 my $zze_str = '0000000000000000000000000000000000000000';
462 $zedzedeight = $marc->field('008');
464 $zze_str = $zedzedeight->data();
466 if (length($zze_str) < 30) {
467 my $nneight = MARC::Field->new( 918, '1', '0', 'a' => $zze_str );
468 $marc->insert_fields_ordered($nneight);
469 $zze_str = '0000000000000000000000000000000000000000';
471 if ($itype eq 'e' or $itype eq 'g' or $itype eq 'k')
472 { $formchar = substr($zze_str,29,1); }
473 else { $formchar = substr($zze_str,23,1); }
474 if (@iform_exclude and $itype) {
475 if ($itype eq 'e' or $itype eq 'g' or $itype eq 'k') { #visual materials
476 if ($formchar ~~ @iform_exclude) { substr($zze_str,29,1) = '|'; }
477 } else { if ($formchar ~~ @iform_exclude) { substr($zze_str,23,1) = '|'; } }
480 if ($itype eq 'e' or $itype eq 'g' or $itype eq 'k') { #visual materials
481 substr($zze_str,29,1) = $iform;
483 substr($zze_str,23,1) = $iform;
486 if ($itype eq 'e' or $itype eq 'g' or $itype eq 'k') { #visual materials
487 substr($zze_str,29,1) = '|';
489 substr($zze_str,23,1) = '|';
493 $new_zze = MARC::Field->new('008',$zze_str);
494 if ($zedzedeight) { $zedzedeight->replace_with($new_zze); } else
495 { $marc->insert_fields_ordered($new_zze); }
497 my @todelzzsx = $marc->field('006');
498 #save the old 006s in 916 fields
499 foreach my $sx (@todelzzsx) {
500 my $nfield = MARC::Field->new( 916, '1', '0', 'a' => $sx->data() );
501 $marc->insert_fields_ordered($nfield);
503 $marc->delete_fields(@todelzzsx);
505 my $zzsx_str = '00000000000000000';
506 if ($iform) { substr($zzsx_str,6,1) = $iform; }
507 my $zedzedsix = MARC::Field->new('006', $zzsx_str);
508 $marc->insert_fields_ordered($zedzedsix);
510 my @todelzzsv = $marc->field('007');
511 #save the old 007s in 917 fields
512 foreach my $sv (@todelzzsv) {
513 my $nfield = MARC::Field->new( 917, '1', '0', 'a' => $sv->data() );
514 $marc->insert_fields_ordered($nfield);
516 $marc->delete_fields(@todelzzsv);
518 my $nn = MARC::Field->new( 919, '1', '0', 'a' => 'record modified by automated fixed field changes' );
519 $marc->insert_fields_ordered($nn);
524 if ($phy eq 'o' or $phy eq 'q' or $phy eq 'z' or $phy eq 't') { $zzs_str = '00'; }
525 if ($phy eq 's' or $phy eq 'c') { $zzs_str = '00000000000000'; }
526 if ($phy eq 'r') { $zzs_str = '00000000000'; }
527 if ($phy eq 'm') { $zzs_str = '00000000000000000000000'; }
528 if ($phy eq 'a') { $zzs_str = '00000000'; }
529 if ($phy eq 'd') { $zzs_str = '000000'; }
530 if ($phy eq 'f') { $zzs_str = '0000000000'; }
531 if ($phy eq 'g') { $zzs_str = '000000000'; }
532 if ($phy eq 'h') { $zzs_str = '0000000000000'; }
533 if ($phy eq 'k') { $zzs_str = '000000'; }
534 if ($phy eq 'v') { $zzs_str = '000000000'; }
535 substr($zzs_str,0,1) = $phy;
536 substr($zzs_str,$phyp,1) = $phyv;
537 $zedzedseven = MARC::Field->new('007', $zzs_str);
538 $marc->insert_fields_ordered($zedzedseven);
540 return $marc->as_xml_record;
543 DROP FUNCTION IF EXISTS migration_tools.anyarray_agg_statefunc(anyarray, anyarray);
544 CREATE FUNCTION migration_tools.anyarray_agg_statefunc(state anyarray, value anyarray)
547 SELECT array_cat($1, $2)
549 LANGUAGE sql IMMUTABLE;
551 DROP AGGREGATE IF EXISTS anyarray_agg(anyarray);
552 CREATE AGGREGATE anyarray_agg(anyarray) (
553 SFUNC = migration_tools.anyarray_agg_statefunc,
558 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat(anyarray, anyarray);
559 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat(with_array anyarray, concat_array anyarray)
563 -- The variable used to track iteration over "with_array".
566 -- The array to be returned by this function.
567 return_array with_array%TYPE;
569 IF with_array IS NULL THEN
571 ELSEIF concat_array IS NULL THEN
575 -- Add all items in "with_array" to "return_array".
576 return_array = with_array;
578 -- Iterate over each element in "concat_array", appending it to "return_array".
579 FOR loop_offset IN ARRAY_LOWER(concat_array, 1)..ARRAY_UPPER(concat_array, 1) LOOP
580 return_array = ARRAY_APPEND(return_array, concat_array[loop_offset]);
585 $BODY$ LANGUAGE plpgsql;
587 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat(anyarray, anynonarray);
588 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat(with_array anyarray, concat_element anynonarray)
592 RETURN ANYARRAY_CONCAT(with_array, ARRAY[concat_element]);
594 $BODY$ LANGUAGE plpgsql;
595 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat_uniq(anyarray, anyarray);
596 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat_uniq(with_array anyarray, concat_array anyarray)
600 -- The variable used to track iteration over "with_array".
603 -- The array to be returned by this function.
604 return_array with_array%TYPE;
606 IF with_array IS NULL THEN
608 ELSEIF concat_array IS NULL THEN
612 -- Add all items in "with_array" to "return_array".
613 return_array = with_array;
615 -- Iterate over each element in "concat_array".
616 FOR loop_offset IN ARRAY_LOWER(concat_array, 1)..ARRAY_UPPER(concat_array, 1) LOOP
617 IF NOT concat_array[loop_offset] = ANY(return_array) THEN
618 return_array = ARRAY_APPEND(return_array, concat_array[loop_offset]);
624 $BODY$ LANGUAGE plpgsql;
626 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat_uniq(anyarray, anynonarray);
627 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat_uniq(with_array anyarray, concat_element anynonarray)
631 RETURN ANYARRAY_CONCAT_UNIQ(with_array, ARRAY[concat_element]);
633 $BODY$ LANGUAGE plpgsql;
634 DROP FUNCTION IF EXISTS migration_tools.anyarray_diff(anyarray, anyarray);
635 CREATE OR REPLACE FUNCTION migration_tools.anyarray_diff(with_array anyarray, against_array anyarray)
639 -- The variable used to track iteration over "with_array".
642 -- The array to be returned by this function.
643 return_array with_array%TYPE := '{}';
645 IF with_array IS NULL THEN
646 RETURN against_array;
647 ELSEIF against_array IS NULL THEN
651 -- Iterate over with_array.
652 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
653 IF NOT with_array[loop_offset] = ANY(against_array) THEN
654 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
658 -- Iterate over against_array.
659 FOR loop_offset IN ARRAY_LOWER(against_array, 1)..ARRAY_UPPER(against_array, 1) LOOP
660 IF NOT against_array[loop_offset] = ANY(with_array) THEN
661 return_array = ARRAY_APPEND(return_array, against_array[loop_offset]);
667 $BODY$ LANGUAGE plpgsql;
668 DROP FUNCTION IF EXISTS migration_tools.anyarray_diff_uniq(anyarray, anyarray);
669 CREATE OR REPLACE FUNCTION migration_tools.anyarray_diff_uniq(with_array anyarray, against_array anyarray)
673 -- The variable used to track iteration over "with_array".
676 -- The array to be returned by this function.
677 return_array with_array%TYPE := '{}';
679 IF with_array IS NULL THEN
680 RETURN against_array;
681 ELSEIF against_array IS NULL THEN
685 -- Iterate over with_array.
686 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
687 RAISE NOTICE '% %', with_array[loop_offset], return_array;
688 IF (NOT with_array[loop_offset] = ANY(against_array)) AND (NOT with_array[loop_offset] = ANY(return_array)) THEN
689 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
693 -- Iterate over against_array.
694 FOR loop_offset IN ARRAY_LOWER(against_array, 1)..ARRAY_UPPER(against_array, 1) LOOP
695 RAISE NOTICE '% %', against_array[loop_offset], return_array;
696 IF (NOT against_array[loop_offset] = ANY(with_array)) AND (NOT against_array[loop_offset] = ANY(return_array)) THEN
697 return_array = ARRAY_APPEND(return_array, against_array[loop_offset]);
703 $BODY$ LANGUAGE plpgsql;
704 DROP FUNCTION IF EXISTS migration_tools.anyarray_enumerate(anyarray);
705 CREATE FUNCTION migration_tools.anyarray_enumerate(anyarray)
706 RETURNS TABLE (index bigint, value anyelement) AS
709 row_number() OVER (),
712 SELECT unnest($1) AS value
715 LANGUAGE sql IMMUTABLE;
716 COMMENT ON FUNCTION migration_tools.anyarray_enumerate(anyarray) IS '
717 Unnests the array along with the indices of each element.
719 *index* (bigint) is the index of the element within the array starting at 1.
721 *value* (anyelement) is the element from the array.
723 NOTE: Multi-dimensional arrays will be flattened as they are with *unnest()*.
725 DROP FUNCTION IF EXISTS migration_tools.anyarray_is_array(anyelement);
726 CREATE OR REPLACE FUNCTION migration_tools.anyarray_is_array(anyelement)
730 -- TODO: Is there a more "elegant" / less hacky of accomplishing
733 -- If the following function call throws an exception, we know the
734 -- element is not an array. If the call succeeds, then it must be
736 EXECUTE FORMAT('WITH a AS (SELECT %L::TEXT[] AS val) SELECT ARRAY_DIMS(a.val) FROM a', $1);
739 SQLSTATE '42804' -- Unknown data-type passed
740 OR SQLSTATE '42883' -- Function doesn't exist
741 OR SQLSTATE '22P02' -- Unable to cast to an array
745 $BODY$ LANGUAGE plpgsql;
746 DROP FUNCTION IF EXISTS migration_tools.anyarray_numeric_only(anyarray);
747 CREATE OR REPLACE FUNCTION migration_tools.anyarray_numeric_only(anyarray)
752 array_values.array_value
755 SELECT UNNEST($1) AS array_value
758 array_values.array_value::TEXT ~ '^\d+(\.\d+)?$'
760 $BODY$ LANGUAGE sql IMMUTABLE;
761 DROP FUNCTION IF EXISTS migration_tools.anyarray_ranges(anyarray);
762 CREATE OR REPLACE FUNCTION migration_tools.anyarray_ranges(from_array anyarray)
763 RETURNS SETOF text[] AS
767 ARRAY_AGG(consolidated_values.consolidated_range) AS ranges
771 (CASE WHEN COUNT(*) > 1 THEN
772 MIN(unconsolidated_values.array_value)::text || '-' || MAX(unconsolidated_values.array_value)::text
774 MIN(unconsolidated_values.array_value)::text
775 END) AS consolidated_range
779 array_values.array_value,
780 ROW_NUMBER() OVER (ORDER BY array_values.array_value) - array_values.array_value AS consolidation_group
784 UNNEST(from_array) AS array_value
787 array_values.array_value
788 ) AS unconsolidated_values
790 unconsolidated_values.consolidation_group
792 MIN(unconsolidated_values.array_value)
793 ) AS consolidated_values
796 $BODY$ LANGUAGE plpgsql;
797 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove_null(anyarray);
798 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove_null(from_array anyarray)
802 -- The variable used to track iteration over "from_array".
805 -- The array to be returned by this function.
806 return_array from_array%TYPE;
808 -- Iterate over each element in "from_array".
809 FOR loop_offset IN ARRAY_LOWER(from_array, 1)..ARRAY_UPPER(from_array, 1) LOOP
810 IF from_array[loop_offset] IS NOT NULL THEN -- If NULL, will omit from "return_array".
811 return_array = ARRAY_APPEND(return_array, from_array[loop_offset]);
817 $BODY$ LANGUAGE plpgsql;
818 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove(anyarray, anyarray);
819 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove(from_array anyarray, remove_array anyarray)
823 -- The variable used to track iteration over "from_array".
827 -- The array to be returned by this function.
828 return_array from_array%TYPE := '{}';
830 -- If either argument is NULL, there is nothing to do.
831 IF from_array IS NULL OR remove_array IS NULL THEN
835 -- Iterate over each element in "from_array".
836 FOR loop_offset IN ARRAY_LOWER(from_array, 1)..ARRAY_UPPER(from_array, 1) LOOP
837 -- If the element being iterated over is in "remove_array",
838 -- do not append it to "return_array".
839 IF (from_array[loop_offset] = ANY(remove_array)) IS DISTINCT FROM TRUE THEN
840 return_array = ARRAY_APPEND(return_array, from_array[loop_offset]);
847 $BODY$ LANGUAGE plpgsql;
850 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove(anyarray, anynonarray);
851 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove(from_array anyarray, remove_element anynonarray)
855 RETURN ANYARRAY_REMOVE(from_array, ARRAY[remove_element]);
857 $BODY$ LANGUAGE plpgsql;
858 DROP FUNCTION IF EXISTS migration_tools.anyarray_sort(anyarray);
859 CREATE OR REPLACE FUNCTION migration_tools.anyarray_sort(with_array anyarray)
860 RETURNS SETOF anyarray AS
864 ARRAY_AGG(sorted_vals.val) AS array_value
868 UNNEST(with_array) AS val
874 $BODY$ LANGUAGE plpgsql;
875 DROP FUNCTION IF EXISTS migration_tools.anyarray_uniq(anyarray);
876 CREATE OR REPLACE FUNCTION migration_tools.anyarray_uniq(with_array anyarray)
880 -- The variable used to track iteration over "with_array".
883 -- The array to be returned by this function.
884 return_array with_array%TYPE := '{}';
886 IF with_array IS NULL THEN
890 IF with_array = '{}' THEN
894 -- Iterate over each element in "concat_array".
895 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
896 IF with_array[loop_offset] IS NULL THEN
899 FROM UNNEST(return_array) AS s(a)
902 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
904 -- When an array contains a NULL value, ANY() returns NULL instead of FALSE...
905 ELSEIF NOT(with_array[loop_offset] = ANY(return_array)) OR NOT(NULL IS DISTINCT FROM (with_array[loop_offset] = ANY(return_array))) THEN
906 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
912 $BODY$ LANGUAGE plpgsql;