1 CREATE FUNCTION migration_tools.anyarray_agg_statefunc(state anyarray, value anyarray)
4 SELECT array_cat($1, $2)
6 LANGUAGE sql IMMUTABLE;
8 DROP AGGREGATE IF EXISTS anyarray_agg(anyarray);
9 CREATE AGGREGATE anyarray_agg(anyarray) (
10 SFUNC = migration_tools.anyarray_agg_statefunc,
14 DROP FUNCTION IF EXISTS migration_tools.anyarray_sort(anyarray);
15 CREATE OR REPLACE FUNCTION migration_tools.anyarray_sort(with_array anyarray)
19 return_array with_array%TYPE := '{}';
21 SELECT ARRAY_AGG(sorted_vals.val) AS array_value
23 ( SELECT UNNEST(with_array) AS val
25 ) AS sorted_vals INTO return_array;
28 $BODY$ LANGUAGE plpgsql;
30 DROP FUNCTION IF EXISTS migration_tools.anyarray_uniq(anyarray);
31 CREATE OR REPLACE FUNCTION migration_tools.anyarray_uniq(with_array anyarray)
35 -- The variable used to track iteration over "with_array".
38 -- The array to be returned by this function.
39 return_array with_array%TYPE := '{}';
41 IF with_array IS NULL THEN
45 IF with_array = '{}' THEN
49 -- Iterate over each element in "concat_array".
50 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
51 IF with_array[loop_offset] IS NULL THEN
53 ( SELECT 1 FROM UNNEST(return_array) AS s(a)
55 THEN return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
57 -- When an array contains a NULL value, ANY() returns NULL instead of FALSE...
58 ELSEIF NOT(with_array[loop_offset] = ANY(return_array)) OR NOT(NULL IS DISTINCT FROM (with_array[loop_offset] = ANY(return_array))) THEN
59 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
65 $BODY$ LANGUAGE plpgsql;
67 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat(anyarray, anyarray);
68 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat(with_array anyarray, concat_array anyarray)
72 -- The variable used to track iteration over "with_array".
75 -- The array to be returned by this function.
76 return_array with_array%TYPE;
78 IF with_array IS NULL THEN
80 ELSEIF concat_array IS NULL THEN
84 -- Add all items in "with_array" to "return_array".
85 return_array = with_array;
87 -- Iterate over each element in "concat_array", appending it to "return_array".
88 FOR loop_offset IN ARRAY_LOWER(concat_array, 1)..ARRAY_UPPER(concat_array, 1) LOOP
89 return_array = ARRAY_APPEND(return_array, concat_array[loop_offset]);
94 $BODY$ LANGUAGE plpgsql;
96 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat(anyarray, anynonarray);
97 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat(with_array anyarray, concat_element anynonarray)
101 RETURN ANYARRAY_CONCAT(with_array, ARRAY[concat_element]);
103 $BODY$ LANGUAGE plpgsql;
104 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat_uniq(anyarray, anyarray);
105 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat_uniq(with_array anyarray, concat_array anyarray)
109 -- The variable used to track iteration over "with_array".
112 -- The array to be returned by this function.
113 return_array with_array%TYPE;
115 IF with_array IS NULL THEN
117 ELSEIF concat_array IS NULL THEN
121 -- Add all items in "with_array" to "return_array".
122 return_array = with_array;
124 -- Iterate over each element in "concat_array".
125 FOR loop_offset IN ARRAY_LOWER(concat_array, 1)..ARRAY_UPPER(concat_array, 1) LOOP
126 IF NOT concat_array[loop_offset] = ANY(return_array) THEN
127 return_array = ARRAY_APPEND(return_array, concat_array[loop_offset]);
133 $BODY$ LANGUAGE plpgsql;
135 DROP FUNCTION IF EXISTS migration_tools.anyarray_concat_uniq(anyarray, anynonarray);
136 CREATE OR REPLACE FUNCTION migration_tools.anyarray_concat_uniq(with_array anyarray, concat_element anynonarray)
140 RETURN ANYARRAY_CONCAT_UNIQ(with_array, ARRAY[concat_element]);
142 $BODY$ LANGUAGE plpgsql;
143 DROP FUNCTION IF EXISTS migration_tools.anyarray_diff(anyarray, anyarray);
144 CREATE OR REPLACE FUNCTION migration_tools.anyarray_diff(with_array anyarray, against_array anyarray)
148 -- The variable used to track iteration over "with_array".
151 -- The array to be returned by this function.
152 return_array with_array%TYPE := '{}';
154 IF with_array IS NULL THEN
155 RETURN against_array;
156 ELSEIF against_array IS NULL THEN
160 -- Iterate over with_array.
161 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
162 IF NOT with_array[loop_offset] = ANY(against_array) THEN
163 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
167 -- Iterate over against_array.
168 FOR loop_offset IN ARRAY_LOWER(against_array, 1)..ARRAY_UPPER(against_array, 1) LOOP
169 IF NOT against_array[loop_offset] = ANY(with_array) THEN
170 return_array = ARRAY_APPEND(return_array, against_array[loop_offset]);
176 $BODY$ LANGUAGE plpgsql;
177 DROP FUNCTION IF EXISTS migration_tools.anyarray_diff_uniq(anyarray, anyarray);
178 CREATE OR REPLACE FUNCTION migration_tools.anyarray_diff_uniq(with_array anyarray, against_array anyarray)
182 -- The variable used to track iteration over "with_array".
185 -- The array to be returned by this function.
186 return_array with_array%TYPE := '{}';
188 IF with_array IS NULL THEN
189 RETURN against_array;
190 ELSEIF against_array IS NULL THEN
194 -- Iterate over with_array.
195 FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP
196 RAISE NOTICE '% %', with_array[loop_offset], return_array;
197 IF (NOT with_array[loop_offset] = ANY(against_array)) AND (NOT with_array[loop_offset] = ANY(return_array)) THEN
198 return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
202 -- Iterate over against_array.
203 FOR loop_offset IN ARRAY_LOWER(against_array, 1)..ARRAY_UPPER(against_array, 1) LOOP
204 RAISE NOTICE '% %', against_array[loop_offset], return_array;
205 IF (NOT against_array[loop_offset] = ANY(with_array)) AND (NOT against_array[loop_offset] = ANY(return_array)) THEN
206 return_array = ARRAY_APPEND(return_array, against_array[loop_offset]);
212 $BODY$ LANGUAGE plpgsql;
213 DROP FUNCTION IF EXISTS migration_tools.anyarray_enumerate(anyarray);
214 CREATE FUNCTION migration_tools.anyarray_enumerate(anyarray)
215 RETURNS TABLE (index bigint, value anyelement) AS
218 row_number() OVER (),
221 SELECT unnest($1) AS value
224 LANGUAGE sql IMMUTABLE;
225 COMMENT ON FUNCTION migration_tools.anyarray_enumerate(anyarray) IS '
226 Unnests the array along with the indices of each element.
228 *index* (bigint) is the index of the element within the array starting at 1.
230 *value* (anyelement) is the element from the array.
232 NOTE: Multi-dimensional arrays will be flattened as they are with *unnest()*.
234 DROP FUNCTION IF EXISTS migration_tools.anyarray_is_array(anyelement);
235 CREATE OR REPLACE FUNCTION migration_tools.anyarray_is_array(anyelement)
239 -- TODO: Is there a more "elegant" / less hacky of accomplishing
242 -- If the following function call throws an exception, we know the
243 -- element is not an array. If the call succeeds, then it must be
245 EXECUTE FORMAT('WITH a AS (SELECT %L::TEXT[] AS val) SELECT ARRAY_DIMS(a.val) FROM a', $1);
248 SQLSTATE '42804' -- Unknown data-type passed
249 OR SQLSTATE '42883' -- Function doesn't exist
250 OR SQLSTATE '22P02' -- Unable to cast to an array
254 $BODY$ LANGUAGE plpgsql;
255 DROP FUNCTION IF EXISTS migration_tools.anyarray_numeric_only(anyarray);
256 CREATE OR REPLACE FUNCTION migration_tools.anyarray_numeric_only(anyarray)
261 array_values.array_value
264 SELECT UNNEST($1) AS array_value
267 array_values.array_value::TEXT ~ '^\d+(\.\d+)?$'
269 $BODY$ LANGUAGE sql IMMUTABLE;
270 DROP FUNCTION IF EXISTS migration_tools.anyarray_ranges(anyarray);
271 CREATE OR REPLACE FUNCTION migration_tools.anyarray_ranges(from_array anyarray)
272 RETURNS SETOF text[] AS
276 ARRAY_AGG(consolidated_values.consolidated_range) AS ranges
280 (CASE WHEN COUNT(*) > 1 THEN
281 MIN(unconsolidated_values.array_value)::text || '-' || MAX(unconsolidated_values.array_value)::text
283 MIN(unconsolidated_values.array_value)::text
284 END) AS consolidated_range
288 array_values.array_value,
289 ROW_NUMBER() OVER (ORDER BY array_values.array_value) - array_values.array_value AS consolidation_group
293 UNNEST(from_array) AS array_value
296 array_values.array_value
297 ) AS unconsolidated_values
299 unconsolidated_values.consolidation_group
301 MIN(unconsolidated_values.array_value)
302 ) AS consolidated_values
305 $BODY$ LANGUAGE plpgsql;
306 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove_null(anyarray);
307 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove_null(from_array anyarray)
311 -- The variable used to track iteration over "from_array".
314 -- The array to be returned by this function.
315 return_array from_array%TYPE;
317 -- Iterate over each element in "from_array".
318 FOR loop_offset IN ARRAY_LOWER(from_array, 1)..ARRAY_UPPER(from_array, 1) LOOP
319 IF from_array[loop_offset] IS NOT NULL THEN -- If NULL, will omit from "return_array".
320 return_array = ARRAY_APPEND(return_array, from_array[loop_offset]);
326 $BODY$ LANGUAGE plpgsql;
327 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove(anyarray, anyarray);
328 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove(from_array anyarray, remove_array anyarray)
332 -- The variable used to track iteration over "from_array".
336 -- The array to be returned by this function.
337 return_array from_array%TYPE := '{}';
339 -- If either argument is NULL, there is nothing to do.
340 IF from_array IS NULL OR remove_array IS NULL THEN
344 -- Iterate over each element in "from_array".
345 FOR loop_offset IN ARRAY_LOWER(from_array, 1)..ARRAY_UPPER(from_array, 1) LOOP
346 -- If the element being iterated over is in "remove_array",
347 -- do not append it to "return_array".
348 IF (from_array[loop_offset] = ANY(remove_array)) IS DISTINCT FROM TRUE THEN
349 return_array = ARRAY_APPEND(return_array, from_array[loop_offset]);
356 $BODY$ LANGUAGE plpgsql;
359 DROP FUNCTION IF EXISTS migration_tools.anyarray_remove(anyarray, anynonarray);
360 CREATE OR REPLACE FUNCTION migration_tools.anyarray_remove(from_array anyarray, remove_element anynonarray)
364 RETURN ANYARRAY_REMOVE(from_array, ARRAY[remove_element]);
366 $BODY$ LANGUAGE plpgsql;