Opaleye.Operators

Operators on Columns. Please note that numeric Column types are instances of Num, so you can use *, /, +, - on them.

Restriction operators

restrict :: SelectArr (Field SqlBool) ()

Keep only the rows of a query satisfying a given condition, using an SQL WHERE clause.

You would typically use restrict if you want to write your query using Arrow notation. If you want to use a "point free" style then keepWhen will suit you better.

(If you are familiar with MonadPlus or Alternative it may help you to know that restrict corresponds to the guard function.)

restrictExists :: SelectArr a b -> SelectArr a ()

Add a WHERE EXISTS clause to the current query.

restrictNotExists :: SelectArr a b -> SelectArr a ()

Add a WHERE NOT EXISTS clause to the current query.

keepWhen :: (a -> Field SqlBool) -> SelectArr a a

Keep only the rows of a query satisfying a given condition, using an SQL WHERE clause.

You would typically use keepWhen if you want to write your query using a "point free" style. If you want to use Arrow notation then restrict will suit you better.

This is the SelectArr equivalent of filter from the Prelude.

Equality operators

(.==) :: Column a -> Column a -> Field SqlBool infix 4

(./=) :: Column a -> Column a -> Field SqlBool infix 4

(.===) :: Default EqPP fields fields => fields -> fields -> Field SqlBool infix 4

A polymorphic equality operator that works for all types that you have run makeAdaptorAndInstance on. This may be unified with .== in a future version.

(./==) :: Default EqPP fields fields => fields -> fields -> Field SqlBool infix 4

A polymorphic inequality operator that works for all types that you have run makeAdaptorAndInstance on. This may be unified with ./= in a future version.

Comparison operators

(.>) :: SqlOrd a => Column a -> Column a -> Field SqlBool infix 4

(.<) :: SqlOrd a => Column a -> Column a -> Field SqlBool infix 4

(.<=) :: SqlOrd a => Column a -> Column a -> Field SqlBool infix 4

(.>=) :: SqlOrd a => Column a -> Column a -> Field SqlBool infix 4

Numerical operators

quot_ :: SqlIntegral a => Column a -> Column a -> Column a

Integral division, named after quot. It maps to the / operator in Postgres.

rem_ :: SqlIntegral a => Column a -> Column a -> Column a

The remainder of integral division, named after rem. It maps to MOD (%) in Postgres, confusingly described as "modulo (remainder)".

Conditional operators

case_ :: [(Field SqlBool, Column a)] -> Column a -> Column a

Select the first case for which the condition is true.

ifThenElse :: Field SqlBool -> Column a -> Column a -> Column a

Monomorphic if/then/else.

This may be replaced by ifThenElseMany in a future version.

ifThenElseMany :: Default IfPP fields fields => Field SqlBool -> fields -> fields -> fields

Polymorphic if/then/else.

Logical operators

(.||) :: Field SqlBool -> Field SqlBool -> Field SqlBool infixr 2

Boolean or

(.&&) :: Field SqlBool -> Field SqlBool -> Field SqlBool infixr 3

Boolean and

not :: Field SqlBool -> Field SqlBool

Boolean not

ors :: Foldable f => f (Field SqlBool) -> Field SqlBool

True when any element of the container is true

Text operators

(.++) :: Field SqlText -> Field SqlText -> Field SqlText

Concatenate Field SqlText

lower :: Field SqlText -> Field SqlText

To lowercase

upper :: Field SqlText -> Field SqlText

To uppercase

like :: Field SqlText -> Field SqlText -> Field SqlBool

Postgres LIKE operator

ilike :: Field SqlText -> Field SqlText -> Field SqlBool

Postgres ILIKE operator

charLength :: PGString a => Column a -> Column Int

Containment operators

in_ :: (Functor f, Foldable f) => f (Column a) -> Column a -> Field SqlBool

in_ is designed to be used in prefix form.

in_ validProducts product checks whether product is a valid product. in_ validProducts is a function which checks whether a product is a valid product.

inSelect :: Default EqPP fields fields => fields -> Select fields -> Select (Field SqlBool)

True if the first argument occurs amongst the rows of the second, false otherwise.

This operation is equivalent to Postgres's IN operator but, for expediency, is currently implemented using a LEFT JOIN. Please file a bug if this causes any issues in practice.

JSON operators

class SqlIsJson a

Class of Postgres types that represent json values. Used to overload functions and operators that work on both SqlJson and SqlJsonb.

Warning: making additional instances of this class can lead to broken code!

Instances

SqlIsJson SqlJsonb

Defined in Opaleye.Operators

SqlIsJson SqlJson

Defined in Opaleye.Operators

type PGIsJson = SqlIsJson

Deprecated: Use SqlIsJson instead

class SqlJsonIndex a

Class of Postgres types that can be used to index json values.

Warning: making additional instances of this class can lead to broken code!

Instances

SqlJsonIndex SqlText

Defined in Opaleye.Operators

SqlJsonIndex SqlInt4

Defined in Opaleye.Operators

SqlJsonIndex SqlInt8

Defined in Opaleye.Operators

type PGJsonIndex = SqlJsonIndex

(.->) infixl 8

Arguments

:: (SqlIsJson a, SqlJsonIndex k)

=> FieldNullable a

-> Field k

key or index

-> FieldNullable a

Get JSON object field by key.

(.->>) infixl 8

Arguments

:: (SqlIsJson a, SqlJsonIndex k)

=> FieldNullable a

-> Field k

key or index

-> FieldNullable SqlText

Get JSON object field as text.

(.#>) infixl 8

Arguments

:: SqlIsJson a

=> FieldNullable a

-> Column (SqlArray SqlText)

path

-> FieldNullable a

Get JSON object at specified path.

(.#>>) infixl 8

Arguments

:: SqlIsJson a

=> FieldNullable a

-> Column (SqlArray SqlText)

path

-> FieldNullable SqlText

Get JSON object at specified path as text.

(.@>) :: Field SqlJsonb -> Field SqlJsonb -> Field SqlBool infix 4

Does the left JSON value contain within it the right value?

(.<@) :: Field SqlJsonb -> Field SqlJsonb -> Field SqlBool infix 4

Is the left JSON value contained within the right value?

(.?) :: Field SqlJsonb -> Field SqlText -> Field SqlBool infix 4

Does the key/element string exist within the JSON value?

(.?|) :: Field SqlJsonb -> Column (SqlArray SqlText) -> Field SqlBool infix 4

Do any of these key/element strings exist?

(.?&) :: Field SqlJsonb -> Column (SqlArray SqlText) -> Field SqlBool infix 4

Do all of these key/element strings exist?

SqlArray operators

emptyArray :: IsSqlType a => Column (SqlArray a)

arrayAppend :: Field (SqlArray a) -> Field (SqlArray a) -> Field (SqlArray a)

Append two SqlArrays

arrayPrepend :: Column a -> Column (SqlArray a) -> Column (SqlArray a)

Prepend an element to a SqlArray

arrayRemove :: Column a -> Column (SqlArray a) -> Column (SqlArray a)

Remove all instances of an element from a SqlArray

arrayRemoveNulls :: Column (SqlArray (Nullable a)) -> Column (SqlArray a)

Remove all NULL values from a SqlArray

singletonArray :: IsSqlType a => Column a -> Column (SqlArray a)

index :: SqlIntegral n => Column (SqlArray a) -> Column n -> Column (Nullable a)

Range operators

overlap :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool

liesWithin :: IsRangeType a => Column a -> Column (SqlRange a) -> Field SqlBool

upperBound :: IsRangeType a => Column (SqlRange a) -> Column (Nullable a)

Access the upper bound of a range. For discrete range types it is the exclusive bound.

lowerBound :: IsRangeType a => Column (SqlRange a) -> Column (Nullable a)

Access the lower bound of a range. For discrete range types it is the inclusive bound.

(.<<) :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool infix 4

(.>>) :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool infix 4

(.&<) :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool infix 4

(.&>) :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool infix 4

(.-|-) :: Column (SqlRange a) -> Column (SqlRange a) -> Field SqlBool infix 4

Other operators

timestamptzAtTimeZone :: Field SqlTimestamptz -> Field SqlText -> Field SqlTimestamp

Deprecated

exists :: QueryArr a b -> QueryArr a ()

Deprecated: Identical to restrictExists. Will be removed in version 0.8.

notExists :: QueryArr a b -> QueryArr a ()

Deprecated: Identical to restrictNotExists. Will be removed in version 0.8.

inQuery :: Default EqPP fields fields => fields -> Query fields -> Select (Field SqlBool)

Deprecated: Identical to inSelect. Will be removed in version 0.8.