PostgeSQL extension adding highly desirable, data-type independent array functionality.
PLpgSQL SQLPL
Fetching latest commit…
Cannot retrieve the latest commit at this time.

README.md

anyarray

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.

license

Please refer to the LICENSE file for licensing and copyright information.

source code

anyarray source code, documentation and examples are available on GitHub at: https://www.github.com/JDBurnZ/anyarray

compatibility

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

functions

MethodReturnsDescription
anyarray_concat(anyarray, anyarray)anyarrayReturns the first argument with values from the second argument appended to it.
anyarray_concat(anyarray, anynonarray)anyarrayReturns the first argument with the second argument appended appended to it.
anyarray_concat_uniq(anyarray, anyarray)anyarrayReturns the first argument with values from the second argument (which are not in the first argument) appended to it.
anyarray_concat_uniq(anyarray, anynonarray)anyarrayReturns the first argument with the second argument appended to it, if the second argument isn't in the first argument.
anyarray_diff(anyarray, anyarray)anyarrayReturns an array of every element which is not common between arrays.
anyarray_diff_uniq(anyarray, anyarray)anyarrayReturns 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)booleanDetermines whether or not the argument passed is an array.
anyarray_numeric_only(anyarray)anyarrayReturns 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)anyarrayReturns the first argument with all values from the second argument removed from it.
anyarray_remove(anyarray, anynonarray)anyarrayReturns the first argument with all values matching the second argument removed from it.
anyarray_remove_null(anyarray)anyarrayReturns an array with all occurrences of NULL omitted.
anyarray_sort(anyarray)anyarrayReturns the array, sorted.
anyarray_uniq(anyarray)anyarrayReturns an array of unique values present within the array passed.

aggregates

MethodReturnsDescription
anyarray_agg(anyarray)anyarrayConcatenates arrays into a single array when aggregating.

operators

Coming Soon!

examples

QueryReturn Data-TypeResult
SELECT anyarray_concat(
    ARRAY[1, 2],
    ARRAY[2, 3]
)
integer[]
{1,2,2,3}
SELECT anyarray_concat(
    ARRAY['one', 'two'],
    ARRAY['two', 'three']
)
text[]
{one,two,two,three}
SELECT anyarray_concat(
    ARRAY[1, 2],
    2
)
integer[]
{1,2,2}
SELECT anyarray_concat(
    ARRAY['one', 'two'],
    'two'::text
)
text[]
{one,two,two}
SELECT anyarray_concat_uniq(
    ARRAY[1, 2],
    ARRAY[2, 3]
)
integer[]
{1,2,3}
SELECT anyarray_concat_uniq(
    ARRAY['one', 'two'],
    ARRAY['two', 'three']
)
text[]
{one,two,three}
SELECT anyarray_concat_uniq(
    ARRAY[1, 2],
    2
)
integer[]
{1,2}
SELECT anyarray_concat_uniq(
    ARRAY[1, 2],
    3
)
integer[]
{1,2,3}
SELECT anyarray_concat_uniq(
    ARRAY['one', 'two'],
    'two'::text
)
text[]
{one,two}
SELECT anyarray_concat_uniq(
    ARRAY['one', 'two'],
    'three'::text
)
text[]
{one,two,three}
SELECT anyarray_diff(
    ARRAY[1, 1, 2],
    ARRAY[2, 3, 4, 4]
)
integer[]
{1,1,3,4,4}
SELECT anyarray_diff(
    ARRAY['one', 'one', 'two'],
    ARRAY['two', 'three', 'four', 'four']
)
text[]
{one,one,three,four,four}
SELECT anyarray_diff_uniq(
    ARRAY[1, 1, 2],
    ARRAY[2, 3, 4, 4]
)
integer[]
{1,3,4}
SELECT 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}
SELECT anyarray_is_array(ARRAY[1, 2])
boolean[]
TRUE
SELECT anyarray_is_array(ARRAY['one', 'two'])
boolean[]
TRUE
SELECT anyarray_is_array(1)
boolean[]
FALSE
SELECT anyarray_is_array('one'::text)
boolean[]
FALSE
SELECT anyarray_ranges(ARRAY[1, 2, 4, 5, 6, 9])
text[]
{1-2,4-6,9}
SELECT anyarray_ranges(ARRAY[1.1, 1.2, 2, 3, 5])
text[]
{1.1,1.2,2-3,5}
SELECT anyarray_remove(
    ARRAY[1, 2],
    ARRAY[2, 3]
)
integer[]
{1}
SELECT anyarray_remove(
    ARRAY['one', 'two'],
    ARRAY['two', 'three']
)
text[]
{one}
SELECT anyarray_remove(
    ARRAY[1, 2],
    2
)
integer[]
{1}
SELECT anyarray_remove(
    ARRAY['one', 'two'],
    'two'::text
)
text[]
{one}
SELECT anyarray_remove_null(ARRAY[1, 2, NULL, 4])
integer[]
{1,2,4}
SELECT anyarray_remove_null(ARRAY['one', 'two', NULL, 'four'])
text[]
{one,two,four}
SELECT anyarray_sort(ARRAY[1, 46, 15, 3])
integer[]
{1,3,15,46}
SELECT anyarray_sort(ARRAY['1', '46', '15', '3'])
integer[]
{1,15,3,46}
SELECT anyarray_sort(ARRAY['one', 'forty-six', 'fifteen', 'three'])
text[]
{fifteen,forty-six,one,three}
SELECT anyarray_uniq(ARRAY[1, 2, 3, 2, 1])
integer[]
{1,2,3}
SELECT 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}}
'b', {{5,6},{7,8}}

Donations

AnyArray is free software, but donations help the developer spend more time maintaining this project and others like it.