sql/base/22-anyarray.sql

   1 CREATE FUNCTION migration_tools.anyarray_agg_statefunc(state anyarray, value anyarray)
   2         RETURNS anyarray AS
   3 $BODY$
   4         SELECT array_cat($1, $2)
   5 $BODY$
   6         LANGUAGE sql IMMUTABLE;
   7
   8 DROP AGGREGATE IF EXISTS anyarray_agg(anyarray);
   9 CREATE AGGREGATE anyarray_agg(anyarray) (
  10     SFUNC = migration_tools.anyarray_agg_statefunc,
  11     STYPE = anyarray
  12 );
  13
  14 DROP FUNCTION IF EXISTS migration_tools.anyarray_sort(anyarray);
  15 CREATE OR REPLACE FUNCTION migration_tools.anyarray_sort(with_array anyarray)
  16     RETURNS anyarray AS
  17 $BODY$
  18     DECLARE
  19         return_array with_array%TYPE := '{}';
  20     BEGIN
  21         SELECT ARRAY_AGG(sorted_vals.val) AS array_value
  22         FROM
  23             (   SELECT UNNEST(with_array) AS val
  24                 ORDER BY val
  25             ) AS sorted_vals INTO return_array;
  26         RETURN return_array;
  27     END;
  28 $BODY$ LANGUAGE plpgsql;
  29
  30 DROP FUNCTION IF EXISTS migration_tools.anyarray_uniq(anyarray);
  31 CREATE OR REPLACE FUNCTION migration_tools.anyarray_uniq(with_array anyarray)
  32     RETURNS anyarray AS
  33 $BODY$
  34     DECLARE
  35         -- The variable used to track iteration over "with_array".
  36         loop_offset integer;
  37
  38         -- The array to be returned by this function.
  39         return_array with_array%TYPE := '{}';
  40     BEGIN
  41         IF with_array IS NULL THEN
  42             return NULL;
  43         END IF;
  44
  45         IF with_array = '{}' THEN
  46             return return_array;
  47         END IF;
  48
  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
  52                 IF NOT EXISTS
  53                     ( SELECT 1 FROM UNNEST(return_array) AS s(a)
  54                     WHERE a IS NULL )
  55                 THEN return_array = ARRAY_APPEND(return_array, with_array[loop_offset]);
  56                 END IF;
  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]);
  60             END IF;
  61         END LOOP;
  62
  63     RETURN return_array;
  64  END;
  65 $BODY$ LANGUAGE plpgsql;
  66
  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)
  69         RETURNS anyarray AS
  70 $BODY$
  71         DECLARE
  72                 -- The variable used to track iteration over "with_array".
  73                 loop_offset integer;
  74
  75                 -- The array to be returned by this function.
  76                 return_array with_array%TYPE;
  77         BEGIN
  78                 IF with_array IS NULL THEN
  79                         RETURN concat_array;
  80                 ELSEIF concat_array IS NULL THEN
  81                         RETURN with_array;
  82                 END IF;
  83
  84                 -- Add all items in "with_array" to "return_array".
  85                 return_array = with_array;
  86
  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]);
  90                 END LOOP;
  91
  92                 RETURN return_array;
  93         END;
  94 $BODY$ LANGUAGE plpgsql;
  95
  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)
  98         RETURNS anyarray AS
  99 $BODY$
 100         BEGIN
 101                 RETURN ANYARRAY_CONCAT(with_array, ARRAY[concat_element]);
 102         END;
 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)
 106         RETURNS anyarray AS
 107 $BODY$
 108         DECLARE
 109                 -- The variable used to track iteration over "with_array".
 110                 loop_offset integer;
 111
 112                 -- The array to be returned by this function.
 113                 return_array with_array%TYPE;
 114         BEGIN
 115                 IF with_array IS NULL THEN
 116                         RETURN concat_array;
 117                 ELSEIF concat_array IS NULL THEN
 118                         RETURN with_array;
 119                 END IF;
 120
 121                 -- Add all items in "with_array" to "return_array".
 122                 return_array = with_array;
 123
 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]);
 128                         END IF;
 129                 END LOOP;
 130
 131                 RETURN return_array;
 132         END;
 133 $BODY$ LANGUAGE plpgsql;
 134
 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)
 137         RETURNS anyarray AS
 138 $BODY$
 139         BEGIN
 140                 RETURN ANYARRAY_CONCAT_UNIQ(with_array, ARRAY[concat_element]);
 141         END;
 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)
 145         RETURNS anyarray AS
 146 $BODY$
 147         DECLARE
 148                 -- The variable used to track iteration over "with_array".
 149                 loop_offset integer;
 150
 151                 -- The array to be returned by this function.
 152                 return_array with_array%TYPE := '{}';
 153         BEGIN
 154                 IF with_array IS NULL THEN
 155                         RETURN against_array;
 156                 ELSEIF against_array IS NULL THEN
 157                         RETURN with_array;
 158                 END IF;
 159
 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]);
 164                         END IF;
 165                 END LOOP;
 166
 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]);
 171                         END IF;
 172                 END LOOP;
 173
 174                 RETURN return_array;
 175         END;
 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)
 179         RETURNS anyarray AS
 180 $BODY$
 181         DECLARE
 182                 -- The variable used to track iteration over "with_array".
 183                 loop_offset integer;
 184
 185                 -- The array to be returned by this function.
 186                 return_array with_array%TYPE := '{}';
 187         BEGIN
 188                 IF with_array IS NULL THEN
 189                         RETURN against_array;
 190                 ELSEIF against_array IS NULL THEN
 191                         RETURN with_array;
 192                 END IF;
 193
 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]);
 199                         END IF;
 200                 END LOOP;
 201
 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]);
 207                         END IF;
 208                 END LOOP;
 209
 210                 RETURN return_array;
 211         END;
 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
 216 $$
 217         SELECT
 218                 row_number() OVER (),
 219                 value
 220         FROM (
 221                 SELECT unnest($1) AS value
 222         ) AS unnested
 223 $$
 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.
 227
 228 *index* (bigint) is the index of the element within the array starting at 1.
 229
 230 *value* (anyelement) is the element from the array.
 231
 232 NOTE: Multi-dimensional arrays will be flattened as they are with *unnest()*.
 233 ';
 234 DROP FUNCTION IF EXISTS migration_tools.anyarray_is_array(anyelement);
 235 CREATE OR REPLACE FUNCTION migration_tools.anyarray_is_array(anyelement)
 236         RETURNS boolean AS
 237 $BODY$
 238         BEGIN
 239                 -- TODO: Is there a more "elegant" / less hacky of accomplishing
 240                 -- this?
 241
 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
 244                 -- an array.
 245                 EXECUTE FORMAT('WITH a AS (SELECT %L::TEXT[] AS val) SELECT ARRAY_DIMS(a.val) FROM a', $1);
 246                 RETURN TRUE;
 247         EXCEPTION WHEN
 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
 251         THEN
 252                 RETURN FALSE;
 253         END;
 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)
 257         RETURNS anyarray AS
 258 $BODY$
 259         SELECT ARRAY(
 260                 SELECT
 261                         array_values.array_value
 262                 FROM
 263                         (
 264                                 SELECT UNNEST($1) AS array_value
 265                         ) AS array_values
 266                 WHERE
 267                         array_values.array_value::TEXT ~ '^\d+(\.\d+)?$'
 268         )
 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
 273 $BODY$
 274         BEGIN
 275                 RETURN QUERY SELECT
 276                                 ARRAY_AGG(consolidated_values.consolidated_range) AS ranges
 277                         FROM
 278                                 (
 279                                         SELECT
 280                                                 (CASE WHEN COUNT(*) > 1 THEN
 281                                                         MIN(unconsolidated_values.array_value)::text || '-' || MAX(unconsolidated_values.array_value)::text
 282                                                 ELSE
 283                                                         MIN(unconsolidated_values.array_value)::text
 284                                                 END) AS consolidated_range
 285                                         FROM
 286                                                 (
 287                                                         SELECT
 288                                                                 array_values.array_value,
 289                                                                 ROW_NUMBER() OVER (ORDER BY array_values.array_value) - array_values.array_value AS consolidation_group
 290                                                         FROM
 291                                                                 (
 292                                                                         SELECT
 293                                                                                 UNNEST(from_array) AS array_value
 294                                                                 ) AS array_values
 295                                                         ORDER BY
 296                                                                 array_values.array_value
 297                                                 ) AS unconsolidated_values
 298                                         GROUP BY
 299                                                 unconsolidated_values.consolidation_group
 300                                         ORDER BY
 301                                                 MIN(unconsolidated_values.array_value)
 302                                 ) AS consolidated_values
 303                 ;
 304         END;
 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)
 308         RETURNS anyarray AS
 309 $BODY$
 310         DECLARE
 311                 -- The variable used to track iteration over "from_array".
 312                 loop_offset integer;
 313
 314                 -- The array to be returned by this function.
 315                 return_array from_array%TYPE;
 316         BEGIN
 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]);
 321                         END IF;
 322                 END LOOP;
 323
 324                 RETURN return_array;
 325         END;
 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)
 329         RETURNS anyarray AS
 330 $BODY$
 331         DECLARE
 332                 -- The variable used to track iteration over "from_array".
 333                 loop_offset integer;
 334
 335
 336                 -- The array to be returned by this function.
 337                 return_array from_array%TYPE := '{}';
 338         BEGIN
 339                 -- If either argument is NULL, there is nothing to do.
 340                 IF from_array IS NULL OR remove_array IS NULL THEN
 341                         RETURN from_array;
 342                 END IF;
 343
 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]);
 350                         END IF;
 351                 END LOOP;
 352
 353
 354                 RETURN return_array;
 355         END;
 356 $BODY$ LANGUAGE plpgsql;
 357
 358
 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)
 361         RETURNS anyarray AS
 362 $BODY$
 363         BEGIN
 364                 RETURN ANYARRAY_REMOVE(from_array, ARRAY[remove_element]);
 365         END;
 366 $BODY$ LANGUAGE plpgsql;