Opaleye.Operators

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

Documentation

type SqlJsonIndex = PGJsonIndex

class PGJsonIndex 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

PGJsonIndex SqlText

Defined in Opaleye.Operators

PGJsonIndex SqlInt4

Defined in Opaleye.Operators

PGJsonIndex SqlInt8

Defined in Opaleye.Operators

type SqlIsJson = PGIsJson

class PGIsJson 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

PGIsJson SqlJsonb

Defined in Opaleye.Operators

PGIsJson SqlJson

Defined in Opaleye.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.

(.==) :: 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.

(.>) :: 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

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)".

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.

(.||) :: 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

(.++) :: 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

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.

inQuery :: Default EqPP fields fields => fields -> Query 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.

(.->) 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?

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)

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

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

doubleOfInt :: Field SqlInt4 -> Field SqlFloat8

Deprecated: Use unsafeCast instead. Will be removed in version 0.7.

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

Identical to restrictExists. Will be deprecated in version 0.7.

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

Identical to restrictNotExists. Will be deprecated in version 0.7.