Array Functions

Construct an array.

>>> printSQL $ array [null_, false, true]
ARRAY[NULL, FALSE, TRUE]

Safely construct an empty array.

>>> printSQL $ array0 text
(ARRAY[] :: text[])

Construct a fixed length array.

>>> printSQL $ array1 (null_ :* false *: true)
ARRAY[NULL, FALSE, TRUE]

>>> :type array1 (null_ :* false *: true)
array1 (null_ :* false *: true)
  :: Expression
       grp
       lat
       with
       db
       params
       from
       (null ('PGfixarray '[3] ('Null 'PGbool)))

Construct a fixed size matrix.

>>> printSQL $ array2 ((null_ :* false *: true) *: (false :* null_ *: true))
ARRAY[[NULL, FALSE, TRUE], [FALSE, NULL, TRUE]]

>>> :type array2 ((null_ :* false *: true) *: (false :* null_ *: true))
array2 ((null_ :* false *: true) *: (false :* null_ *: true))
  :: Expression
       grp
       lat
       with
       db
       params
       from
       (null ('PGfixarray '[2, 3] ('Null 'PGbool)))

>>> printSQL $ cardinality (array [null_, false, true])
cardinality(ARRAY[NULL, FALSE, TRUE])

>>> printSQL $ array [null_, false, true] & index 2
(ARRAY[NULL, FALSE, TRUE])[2]

Typesafe indexing of fixed length arrays.

>>> printSQL $ array1 (true *: false) & index1 @1
(ARRAY[TRUE, FALSE])[1]

Typesafe indexing of fixed size matrices.

>>> printSQL $ array2 ((true *: false) *: (false *: true)) & index2 @1 @2
(ARRAY[[TRUE, FALSE], [FALSE, TRUE]])[1][2]

Expand an array to a set of rows

>>> printSQL $ unnest (array [null_, false, true])
unnest(ARRAY[NULL, FALSE, TRUE])

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given Operator, which must yield a Boolean result. The result of arrAny is true if any true result is obtained. The result is false if no true result is found (including the case where the array has zero elements).

If the array expression yields a null array, the result of arrAny will be null. If the left-hand expression yields null, the result of arrAny is ordinarily null (though a non-strict comparison Operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of arrAny will be null, not false (again, assuming a strict comparison Operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

>>> printSQL $ arrAny true (.==) (array [true, false, null_])
(TRUE = ANY (ARRAY[TRUE, FALSE, NULL]))
>>> printSQL $ arrAny "hi" like (array ["bi","hi"])
((E'hi' :: text) LIKE ANY (ARRAY[(E'bi' :: text), (E'hi' :: text)]))

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given Operator, which must yield a Boolean result. The result of arrAll is true if all comparisons yield true (including the case where the array has zero elements). The result is false if any false result is found.

If the array expression yields a null array, the result of arrAll will be null. If the left-hand expression yields null, the result of arrAll is ordinarily null (though a non-strict comparison Operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no false comparison result is obtained, the result of arrAll will be null, not true (again, assuming a strict comparison Operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

>>> printSQL $ arrAll true (.==) (array [true, false, null_])
(TRUE = ALL (ARRAY[TRUE, FALSE, NULL]))
>>> printSQL $ arrAll "hi" like (array ["bi","hi"])
((E'hi' :: text) LIKE ALL (ARRAY[(E'bi' :: text), (E'hi' :: text)]))