Aggregate mode

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

Since: 2.5.3

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

Since: 2.5.3

Remove NULL values from an array

(string_agg) Concatenate input values separated by a delimiter.

Since: 2.2.8

(string_agg) Concatenate input values separated by a delimiter.

Coalesce an array with an empty default value

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

Since: 2.2.11

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

Since: 2.6.0

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

Same as insertSelectWithConflict but returns the number of rows affected.

Since: 3.1.3

`NO WAIT` syntax for postgres locking error will be thrown if locked rows are attempted to be selected

Since: 3.5.9.0

default behaviour of postgres locks. will attempt to wait for locks to expire

Since: 3.5.9.0

`SKIP LOCKED` syntax for postgres locking locked rows will be skipped

Since: 3.5.9.0

`FOR UPDATE OF` syntax for postgres locking allows locking of specific tables with an update lock in a view or join

Since: 3.5.9.0

`FOR SHARE OF` syntax for postgres locking allows locking of specific tables with a share lock in a view or join

Since: 3.5.9.0

Allow aggregate functions to take a filter clause.

Example of usage:

share [mkPersist sqlSettings] [persistLowerCase|
  User
    name Text
    deriving Eq Show
  Task
    userId UserId
    completed Bool
    deriving Eq Show
|]

select $ from $ (users InnerJoin tasks) -> do
  on $ users ^. UserId ==. tasks ^. TaskUserId
  groupBy $ users ^. UserId
  return
   ( users ^. UserId
   , count (tasks ^. TaskId) filterWhere (tasks ^. TaskCompleted ==. val True)
   , count (tasks ^. TaskId) filterWhere (tasks ^. TaskCompleted ==. val False)
   )

Since: 3.3.3.3

Allows to use `VALUES (..)` in-memory set of values in RHS of from expressions. Useful for JOIN's on known values which also can be additionally preprocessed somehow on db side with usage of inner PostgreSQL capabilities.

Example of usage:

share [mkPersist sqlSettings] [persistLowerCase|
  User
    name Text
    age Int
    deriving Eq Show

select $ do
 bound :& user <- from $
     values (   (val (10 :: Int), val ("ten" :: Text))
           :| [ (val 20, val "twenty")
              , (val 30, val "thirty") ]
           )
     InnerJoin table User
     on (((bound, _boundName) :& user) -> user^.UserAge >=. bound)
 groupBy bound
 pure (bound, count @Int $ user^.UserName)

Since: 3.5.2.3

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