A set of PostgeSQL functions adding highly desirable, data-type independent array functionality.
Inspired by intarray's complete disregard for all non-integer data-types.
Please refer to the LICENSE file for licensing and copyright information.
anyarray source code, documentation and examples are available on GitHub at: https://www.github.com/JDBurnZ/anyarray
Tested on PostgreSQL 9.1, 9.2 and 9.3, but should be compatible with all versions which support arrays.
- PostgreSQL 8.x
- PostgreSQL 9.x
| Method | Returns | Description |
|---|---|---|
| anyarray_concat(anyarray, anyarray) | anyarray | Returns the first argument with values from the second argument appended to it. |
| anyarray_concat(anyarray, anynonarray) | anyarray | Returns the first argument with the second argument appended appended to it. |
| anyarray_concat_uniq(anyarray, anyarray) | anyarray | Returns the first argument with values from the second argument (which are not in the first argument) appended to it. |
| anyarray_concat_uniq(anyarray, anynonarray) | anyarray | Returns the first argument with the second argument appended to it, if the second argument isn't in the first argument. |
| anyarray_diff(anyarray, anyarray) | anyarray | Returns an array of every element which is not common between arrays. |
| anyarray_diff_uniq(anyarray, anyarray) | anyarray | Returns an array of every unique value which is not common between arrays. |
| anyarray_enumerate(anyarray) | TABLE (index bigint, value anyelement) | Unnests the array along with the indices of each element. |
| anyarray_is_array(anyelement) | boolean | Determines whether or not the argument passed is an array. |
| anyarray_numeric_only(anyarray) | anyarray | Returns the array passed with all non-numeric values removed from it. Retains whole and decimal values. |
| anyarray_ranges(anyarray) | text[] | Converts an array of values into ranges. Currently only supports smalling, integer and bigint. |
| anyarray_remove(anyarray, anyarray) | anyarray | Returns the first argument with all values from the second argument removed from it. |
| anyarray_remove(anyarray, anynonarray) | anyarray | Returns the first argument with all values matching the second argument removed from it. |
| anyarray_remove_null(anyarray) | anyarray | Returns an array with all occurrences of NULL omitted. |
| anyarray_sort(anyarray) | anyarray | Returns the array, sorted. |
| anyarray_uniq(anyarray) | anyarray | Returns an array of unique values present within the array passed. |
| Method | Returns | Description |
|---|---|---|
| anyarray_agg(anyarray) | anyarray | Concatenates arrays into a single array when aggregating. |
Coming Soon!
| Query | Return Data-Type | Result |
|---|---|---|
anyarray_concat( ARRAY[1, 2], ARRAY[2, 3] ) | integer[] | {1,2,2,3} |
anyarray_concat( ARRAY['one', 'two'], ARRAY['two', 'three'] ) | text[] | {one,two,two,three} |
anyarray_concat( ARRAY[1, 2], 2 ) | integer[] | {1,2,2} |
anyarray_concat( ARRAY['one', 'two'], 'two'::text ) | text[] | {one,two,two} |
anyarray_concat_uniq( ARRAY[1, 2], ARRAY[2, 3] ) | integer[] | {1,2,3} |
anyarray_concat_uniq( ARRAY['one', 'two'], ARRAY['two', 'three'] ) | text[] | {one,two,three} |
anyarray_concat_uniq( ARRAY[1, 2], 2 ) | integer[] | {1,2} |
anyarray_concat_uniq( ARRAY[1, 2], 3 ) | integer[] | {1,2,3} |
anyarray_concat_uniq( ARRAY['one', 'two'], 'two'::text ) | text[] | {one,two} |
anyarray_concat_uniq( ARRAY['one', 'two'], 'three'::text ) | text[] | {one,two,three} |
anyarray_diff( ARRAY[1, 1, 2], ARRAY[2, 3, 4, 4] ) | integer[] | {1,1,3,4,4} |
anyarray_diff( ARRAY['one', 'one', 'two'], ARRAY['two', 'three', 'four', 'four'] ) | text[] | {one,one,three,four,four} |
anyarray_diff_uniq( ARRAY[1, 1, 2], ARRAY[2, 3, 4, 4] ) | integer[] | {1,3,4} |
anyarray_diff_uniq( ARRAY['one', 'one', 'two'], ARRAY['two', 'three', 'four', 'four'] ) | text[] | {one,three,four} |
SELECT * FROM anyarray_enumerate( ARRAY[ 'foo', 'bar', 'spam', 'eggs' ]::TEXT[] ); |
TABLE (index bigint, value text) | {1,'foo'}
{2,'bar'}
{3,'spam'}
{4,'eggs'} |
SELECT * FROM anyarray_enumerate( ARRAY[ ARRAY['foo', 'bar'], ARRAY['spam', 'eggs'] ]::TEXT[] ); |
TABLE (index bigint, value text) | {1,'foo'}
{2,'bar'}
{3,'spam'}
{4,'eggs'} |
SELECT anyarray_numeric_only( ARRAY['1', '1.1', '1.1a', '1.a', 'a']::text[] ) | text[] | {1,1.1} |
SELECT anyarray_numeric_only( ARRAY[1, 1.1, 1.1234567890]::numeric[] ) | numeric[] | {1,1.1,1.1234567890} |
anyarray_is_array(ARRAY[1, 2]) | boolean[] | TRUE |
anyarray_is_array(ARRAY['one', 'two']) | boolean[] | TRUE |
anyarray_is_array(1) | boolean[] | FALSE |
anyarray_is_array('one'::text) | boolean[] | FALSE |
anyarray_ranges(ARRAY[1, 2, 4, 5, 6, 9]) | text[] | {1-2,4-6,9} |
anyarray_ranges(ARRAY[1.1, 1.2, 2, 3, 5]) | text[] | {1.1,1.2,2-3,5} |
anyarray_remove( ARRAY[1, 2], ARRAY[2, 3] ) | integer[] | {1} |
anyarray_remove( ARRAY['one', 'two'], ARRAY['two', 'three'] ) | text[] | {one} |
anyarray_remove( ARRAY[1, 2], 2 ) | integer[] | {1} |
anyarray_remove( ARRAY['one', 'two'], 'two'::text ) | text[] | {one} |
anyarray_remove_null(ARRAY[1, 2, NULL, 4]) | integer[] | {1,2,4} |
anyarray_remove_null(ARRAY['one', 'two', NULL, 'four']) | text[] | {one,two,four} |
anyarray_sort(ARRAY[1, 46, 15, 3]) | integer[] | {1,3,15,46} |
anyarray_sort(ARRAY['1', '46', '15', '3']) | integer[] | {1,15,3,46} |
anyarray_sort(ARRAY['one', 'forty-six', 'fifteen', 'three']) | text[] | {fifteen,forty-six,one,three} |
anyarray_uniq(ARRAY[1, 2, 3, 2, 1]) | integer[] | {1,2,3} |
anyarray_uniq(ARRAY['one', 'two', 'three', 'two', 'one']) | text[] | {one,two,three} |
SELECT id, anyarray_agg(list)
FROM (VALUES
('a', ARRAY[1,2]),
('a', ARRAY[3,4]),
('b', ARRAY[5,6]),
('b', ARRAY[7,8])
) AS data (id, list)
GROUP BY id | text, integer[] | 'a', {1,2,3,4}
'b', {5,6,7,8} |
SELECT id, anyarray_agg(ARRAY[list])
FROM (VALUES
('a', ARRAY[1,2]),
('a', ARRAY[3,4]),
('b', ARRAY[5,6]),
('b', ARRAY[7,8])
) AS data (id, list)
GROUP BY id | text, integer[] | 'a', {{1,2},{3,4}} |
- Test on PostgreSQL 8.3
- Implement
anyarray_shift(anyarray): Returns the array passed with the first element removed. - Implement
anyarray_pop(anyarray): Returns the array passed with the last element removed. - Implement
anyarray_remove_at(anyarray, offset): Returns the array passed with the element atoffsetremoved. Should offset start at 1 similar to PostgreSQL's array access, or start at 0 (ordinal) like most programming languages use? Leaning toward ordinal, because that would be assumed functionality unless you already have a solid understanding of PostgreSQL arrays.)
AnyArray is free software, but donations help the developer spend more time maintaining this projects and others like it.
