Database.Esqueleto.PostgreSQL

This module contain PostgreSQL-specific functions.

Since: 2.2.8

Documentation

data AggMode

Aggregate mode

Constructors

AggModeAll

ALL

AggModeDistinct

DISTINCT

Instances

Show AggMode

Defined in Database.Esqueleto.PostgreSQL

Methods

showsPrec :: Int -> AggMode -> ShowS

show :: AggMode -> String

showList :: [AggMode] -> ShowS

arrayAggDistinct :: (PersistField a, PersistField [a]) => SqlExpr (Value a) -> SqlExpr (Value (Maybe [a]))

(array_agg) Concatenate distinct input values, including NULLs, into an array.

Since: 2.5.3

arrayAgg :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value (Maybe [a]))

arrayAggWith :: AggMode -> SqlExpr (Value a) -> [OrderByClause] -> SqlExpr (Value (Maybe [a]))

arrayRemove :: SqlExpr (Value [a]) -> SqlExpr (Value a) -> SqlExpr (Value [a])

(array_remove) Remove all elements equal to the given value from the array.

Since: 2.5.3

arrayRemoveNull :: SqlExpr (Value [Maybe a]) -> SqlExpr (Value [a])

Remove NULL values from an array

stringAgg

Arguments

:: SqlString s

=> SqlExpr (Value s)

Input values.

-> SqlExpr (Value s)

Delimiter.

-> SqlExpr (Value (Maybe s))

Concatenation.

(string_agg) Concatenate input values separated by a delimiter.

Since: 2.2.8

stringAggWith

Arguments

:: SqlString s

=> AggMode

Aggregate mode (ALL or DISTINCT)

-> SqlExpr (Value s)

Input values.

-> SqlExpr (Value s)

Delimiter.

-> [OrderByClause]

ORDER BY clauses

-> SqlExpr (Value (Maybe s))

Concatenation.

(string_agg) Concatenate input values separated by a delimiter.

maybeArray :: (PersistField a, PersistField [a]) => SqlExpr (Value (Maybe [a])) -> SqlExpr (Value [a])

Coalesce an array with an empty default value

chr :: SqlString s => SqlExpr (Value Int) -> SqlExpr (Value s)

(chr) Translate the given integer to a character. (Note the result will depend on the character set of your database.)

Since: 2.2.11

now_ :: SqlExpr (Value UTCTime)

random_ :: (PersistField a, Num a) => SqlExpr (Value a)

(random()) Split out into database specific modules because MySQL uses `rand()`.

Since: 2.6.0

upsert

Arguments

:: (MonadIO m, PersistEntity record, OnlyOneUniqueKey record, PersistRecordBackend record SqlBackend, IsPersistBackend (PersistEntityBackend record))

=> record

new record to insert

-> [SqlExpr (Update record)]

updates to perform if the record already exists

-> ReaderT SqlBackend m (Entity record)

the record in the database after the operation

upsertBy

Arguments

:: (MonadIO m, PersistEntity record, IsPersistBackend (PersistEntityBackend record))

=> Unique record

uniqueness constraint to find by

-> record

new record to insert

-> [SqlExpr (Update record)]

updates to perform if the record already exists

-> ReaderT SqlBackend m (Entity record)

the record in the database after the operation

insertSelectWithConflict

Arguments

:: (FinalResult a, KnowResult a ~ Unique val, MonadIO m, PersistEntity val)

=> a

Unique constructor or a unique, this is used just to get the name of the postgres constraint, the value(s) is(are) never used, so if you have a unique "MyUnique 0", "MyUnique undefined" would work as well.

-> SqlQuery (SqlExpr (Insertion val))

Insert query.

-> (SqlExpr (Entity val) -> SqlExpr (Entity val) -> [SqlExpr (Update val)])

A list of updates to be applied in case of the constraint being violated. The expression takes the current and excluded value to produce the updates.

-> SqlWriteT m ()

Inserts into a table the results of a query similar to insertSelect but allows to update values that violate a constraint during insertions.

Example of usage:

share [ mkPersist sqlSettings
      , mkDeleteCascade sqlSettings
      , mkMigrate "migrate"
      ] [persistLowerCase|
  Bar
    num Int
    deriving Eq Show
  Foo
    num Int
    UniqueFoo num
    deriving Eq Show
|]

insertSelectWithConflict
  UniqueFoo -- (UniqueFoo undefined) or (UniqueFoo anyNumber) would also work
  (from $ b ->
    return $ Foo <# (b ^. BarNum)
  )
  (current excluded ->
    [FooNum =. (current ^. FooNum) +. (excluded ^. FooNum)]
  )

Inserts to table Foo all Bar.num values and in case of conflict SomeFooUnique, the conflicting value is updated to the current plus the excluded.

Since: 3.1.3

insertSelectWithConflictCount :: forall a val m. (FinalResult a, KnowResult a ~ Unique val, MonadIO m, PersistEntity val) => a -> SqlQuery (SqlExpr (Insertion val)) -> (SqlExpr (Entity val) -> SqlExpr (Entity val) -> [SqlExpr (Update val)]) -> SqlWriteT m Int64

Same as insertSelectWithConflict but returns the number of rows affected.

Since: 3.1.3

Internal

unsafeSqlAggregateFunction :: UnsafeSqlFunctionArgument a => Builder -> AggMode -> a -> [OrderByClause] -> SqlExpr (Value b)

(Internal) Create a custom aggregate functions with aggregate mode

Do not use this function directly, instead define a new function and give it a type (see unsafeSqlBinOp)