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

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

Do not use. Will be deprecated in 0.8. You probably want to use sqlLength instead.

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

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?

jsonBuildObject :: JSONBuildObjectFields -> Column SqlJson

Create an SqlJson object from a collection of fields

jsonBuildObjectField

Arguments

:: String

Field name

-> Column a

Field value

-> JSONBuildObjectFields

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 :: Column SqlTimestamptz

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.

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

This function is probably not useful and is likely to be deprecated in the future.

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.