Opaleye.Operators

Restriction operators

where_ :: Field SqlBool -> Select ()

Keep only the rows of a query satisfying a given condition, using an SQL WHERE clause. It is equivalent to the Haskell function

where_ :: Bool -> [()]
where_ True  = [()]
where_ False = []

restrict :: SelectArr (Field SqlBool) ()

You would typically use restrict if you want to write your query using Arrow notation. If you want to use monadic style then where_ will suit you better.

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.

Numerical operators

Numeric Column / Field types are instances of Num and Fractional, so you can use the standard Haskell numerical operators (e.g.. *, /, +, -) on them and you can create them with numerical literals such as 3.14 :: Field SqlFloat8.

(+) :: Num a => a -> a -> a infixl 6

(-) :: Num a => a -> a -> a infixl 6

(*) :: Num a => a -> a -> a infixl 7

(/) :: Fractional a => a -> a -> a infixl 7

Fractional division.

fromInteger :: Num a => Integer -> a

Conversion from an Integer. An integer literal represents the application of the function fromInteger to the appropriate value of type Integer, so such literals have type (Num a) => a.

abs :: Num a => a -> a

Absolute value.

negate :: Num a => a -> a

Unary negation.

signum :: Num a => a -> a

Sign of a number. The functions abs and signum should satisfy the law:

abs x * signum x == x

For real numbers, the signum is either -1 (negative), 0 (zero) or 1 (positive).

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

sqlLength :: PGString a => Field a -> Field SqlInt4

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.

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

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?

jsonBuildObject :: JSONBuildObjectFields -> Column SqlJson

Create an SqlJson object from a collection of fields.

Note: This is implemented as a variadic function in postgres, and as such, is limited to 50 arguments, or 25 key-value pairs.

jsonBuildObjectField

Arguments

:: String

Field name

-> Column a

Field value

-> JSONBuildObjectFields

Given a label and a column, generates a pair for use with jsonBuildObject

data JSONBuildObjectFields

Combine JSONBuildObjectFields using (<>)

Instances

Semigroup JSONBuildObjectFields

Defined in Opaleye.Internal.JSONBuildObjectFields

Methods

(<>) :: JSONBuildObjectFields -> JSONBuildObjectFields -> JSONBuildObjectFields

sconcat :: NonEmpty JSONBuildObjectFields -> JSONBuildObjectFields

stimes :: Integral b => b -> JSONBuildObjectFields -> JSONBuildObjectFields

Monoid JSONBuildObjectFields

Defined in Opaleye.Internal.JSONBuildObjectFields

Methods

mempty :: JSONBuildObjectFields

mappend :: JSONBuildObjectFields -> JSONBuildObjectFields -> JSONBuildObjectFields

mconcat :: [JSONBuildObjectFields] -> JSONBuildObjectFields

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)

arrayPosition

Arguments

:: Field (SqlArray a)

Haystack

-> Field a

Needle

-> Field (Nullable SqlInt4)

Postgres's array_position

sqlElem

Arguments

:: Field a

Needle

-> Field (SqlArray a)

Haystack

-> Field SqlBool

Whether the element (needle) exists in the array (haystack). N.B. this is implemented hackily using array_position. If you need it to be implemented using = any then please open an issue.

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

dateOfTimestamp :: Field SqlTimestamp -> Field SqlDate

now :: Field SqlTimestamptz

Current date and time (start of current transaction)

class IntervalNum from to | from -> to

IntervalNum from to determines from which date or time types an interval can be added (addInterval) or subtracted (minusInterval) and which is the resulting type.

The instances should correspond to the interval + and - operations listed in:

https://www.postgresql.org/docs/current/functions-datetime.html#OPERATORS-DATETIME-TABLE

Instances

IntervalNum SqlTimestamptz SqlTimestamptz

Defined in Opaleye.Operators

IntervalNum SqlTimestamp SqlTimestamp

Defined in Opaleye.Operators

IntervalNum SqlTime SqlTime

Defined in Opaleye.Operators

IntervalNum SqlInterval SqlInterval

Defined in Opaleye.Operators

IntervalNum SqlDate SqlTimestamp

Defined in Opaleye.Operators

addInterval :: IntervalNum from to => Field from -> Field SqlInterval -> Field to

minusInterval :: IntervalNum from to => Field from -> Field SqlInterval -> Field to

Deprecated

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

Deprecated: Use where_ or restrict instead. Will be removed in version 0.9.