Database.Beam.Postgres.Full

Module providing (almost) full support for Postgres query and data manipulation statements. These functions shadow the functions in Database.Beam.Query and provide a strict superset of functionality. They map 1-to-1 with the underlying Postgres support.

Additional SELECT features

SELECT Locking clause

data PgWithLocking s a

Combines the result of a query along with a set of locked tables. Used as a return value for the lockingFor_ function.

Instances

ProjectibleWithPredicate c be res a => ProjectibleWithPredicate c be res (PgWithLocking s a)

Defined in Database.Beam.Postgres.Full

Methods

project' :: Monad m => Proxy c -> Proxy (be, res) -> (forall context. c context => Proxy context -> Proxy be -> res -> m res) -> PgWithLocking s a -> m (PgWithLocking s a)

projectSkeleton' :: Monad m => Proxy c -> Proxy (be, res) -> (forall context. c context => Proxy context -> Proxy be -> m res) -> m (PgWithLocking s a)

data PgLockedTables s

An explicit lock against some tables. You can create a value of this type using the locked_ function. You can combine these values monoidally to combine multiple locks for use with the withLocks_ function.

Instances

Semigroup (PgLockedTables s)

Defined in Database.Beam.Postgres.Full

Methods

(<>) :: PgLockedTables s -> PgLockedTables s -> PgLockedTables s

sconcat :: NonEmpty (PgLockedTables s) -> PgLockedTables s

stimes :: Integral b => b -> PgLockedTables s -> PgLockedTables s

Monoid (PgLockedTables s)

Defined in Database.Beam.Postgres.Full

Methods

mempty :: PgLockedTables s

mappend :: PgLockedTables s -> PgLockedTables s -> PgLockedTables s

mconcat :: [PgLockedTables s] -> PgLockedTables s

data PgSelectLockingStrength

Specifies the level of lock that will be taken against a row. See the manual section for more information.

Constructors

PgSelectLockingStrengthUpdate

UPDATE

PgSelectLockingStrengthNoKeyUpdate

NO KEY UPDATE

PgSelectLockingStrengthShare

SHARE

PgSelectLockingStrengthKeyShare

KEY SHARE

Instances

Eq PgSelectLockingStrength

Defined in Database.Beam.Postgres.Syntax

Methods

(==) :: PgSelectLockingStrength -> PgSelectLockingStrength -> Bool

(/=) :: PgSelectLockingStrength -> PgSelectLockingStrength -> Bool

Show PgSelectLockingStrength

Defined in Database.Beam.Postgres.Syntax

Methods

showsPrec :: Int -> PgSelectLockingStrength -> ShowS

show :: PgSelectLockingStrength -> String

showList :: [PgSelectLockingStrength] -> ShowS

Generic PgSelectLockingStrength

Defined in Database.Beam.Postgres.Syntax

Associated Types

type Rep PgSelectLockingStrength :: Type -> Type

Methods

from :: PgSelectLockingStrength -> Rep PgSelectLockingStrength x

to :: Rep PgSelectLockingStrength x -> PgSelectLockingStrength

type Rep PgSelectLockingStrength

Defined in Database.Beam.Postgres.Syntax

type Rep PgSelectLockingStrength = D1 ('MetaData "PgSelectLockingStrength" "Database.Beam.Postgres.Syntax" "beam-postgres-0.5.1.0-7v9Z2BuUTTm5OBEuMV8Ty6" 'False) ((C1 ('MetaCons "PgSelectLockingStrengthUpdate" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "PgSelectLockingStrengthNoKeyUpdate" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "PgSelectLockingStrengthShare" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "PgSelectLockingStrengthKeyShare" 'PrefixI 'False) (U1 :: Type -> Type)))

data PgSelectLockingOptions

Specifies how we should handle lock conflicts.

See the manual section for more information

Constructors

PgSelectLockingOptionsNoWait

NOWAIT. Report an error rather than waiting for the lock

PgSelectLockingOptionsSkipLocked

SKIP LOCKED. Rather than wait for a lock, skip the row instead

Instances

Eq PgSelectLockingOptions

Defined in Database.Beam.Postgres.Syntax

Methods

(==) :: PgSelectLockingOptions -> PgSelectLockingOptions -> Bool

(/=) :: PgSelectLockingOptions -> PgSelectLockingOptions -> Bool

Show PgSelectLockingOptions

Defined in Database.Beam.Postgres.Syntax

Methods

showsPrec :: Int -> PgSelectLockingOptions -> ShowS

show :: PgSelectLockingOptions -> String

showList :: [PgSelectLockingOptions] -> ShowS

Generic PgSelectLockingOptions

Defined in Database.Beam.Postgres.Syntax

Associated Types

type Rep PgSelectLockingOptions :: Type -> Type

Methods

from :: PgSelectLockingOptions -> Rep PgSelectLockingOptions x

to :: Rep PgSelectLockingOptions x -> PgSelectLockingOptions

type Rep PgSelectLockingOptions

Defined in Database.Beam.Postgres.Syntax

type Rep PgSelectLockingOptions = D1 ('MetaData "PgSelectLockingOptions" "Database.Beam.Postgres.Syntax" "beam-postgres-0.5.1.0-7v9Z2BuUTTm5OBEuMV8Ty6" 'False) (C1 ('MetaCons "PgSelectLockingOptionsNoWait" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "PgSelectLockingOptionsSkipLocked" 'PrefixI 'False) (U1 :: Type -> Type))

lockingAllTablesFor_ :: (Database Postgres db, Projectible Postgres a, ThreadRewritable (QNested s) a) => PgSelectLockingStrength -> Maybe PgSelectLockingOptions -> Q Postgres db (QNested s) a -> Q Postgres db s (WithRewrittenThread (QNested s) s a)

Like lockingFor_, but does not require an explicit set of locked tables. This produces an empty FOR .. OF clause.

lockingFor_ :: forall a db s. (Database Postgres db, Projectible Postgres a, ThreadRewritable (QNested s) a) => PgSelectLockingStrength -> Maybe PgSelectLockingOptions -> Q Postgres db (QNested s) (PgWithLocking (QNested s) a) -> Q Postgres db s (WithRewrittenThread (QNested s) s a)

Lock some tables during the execution of a query. This is rather complicated, and there are several usage examples in the user guide

The Postgres locking clause is rather complex, and beam currently does not check several pre-conditions. It is assumed you kinda know what you're doing.

Things which postgres doesn't like, but beam will do

• Using aggregates within a query that has a locking clause
• Using UNION, INTERSECT, or EXCEPT

See here for more details.

This function accepts a locking strength (UPDATE, SHARE, KEY SHARE, etc), an optional locking option (NOWAIT or SKIP LOCKED), and a query whose rows to lock. The query should return its result wrapped in PgWithLocking, via the withLocks_ or lockAll_ function.

If you want to use the most common behavior (lock all rows in every table mentioned), the lockingAllTablesFor_ function may be what you're after.

locked_ :: (Beamable tbl, Database Postgres db) => DatabaseEntity Postgres db (TableEntity tbl) -> Q Postgres db s (PgLockedTables s, tbl (QExpr Postgres s))

Join with a table while locking it explicitly. Provides a PgLockedTables value that can be used with withLocks_ to explicitly lock a table during a SELECT statement

lockAll_ :: a -> PgWithLocking s a

Use with lockingFor_ to lock all tables mentioned in the query

withLocks_ :: a -> PgLockedTables s -> PgWithLocking s a

Return and lock the given tables. Typically used as an infix operator. See the the user guide for usage examples

Lateral joins

lateral_ :: forall s a b db. (ThreadRewritable s a, ThreadRewritable (QNested s) b, Projectible Postgres b) => a -> (WithRewrittenThread s (QNested s) a -> Q Postgres db (QNested s) b) -> Q Postgres db s (WithRewrittenThread (QNested s) s b)

Postgres LATERAL JOIN support

Allows the use of variables introduced on the left side of a JOIN to be used on the right hand side.

Because of the default scoping rules, we can't use the typical monadic bind (>>=) operator to create this join.

Instead, lateral_ takes two arguments. The first is the left hand side of the JOIN. The second is a function that takes the result of the first join and uses those variables to create the right hand side.

For example, to join table A with a subquery that returns the first three rows in B which matches a column in A, ordered by another column in B:

lateral_ (_tableA database) $ \tblA ->
  limit_ 3 $
  ordering_ (\(_, b) -> asc_ (_bField2 b)) $ do
    b <- _tableB database
    guard_ (_bField1 b ==. _aField1 a)
    pure (a, b0

INSERT and INSERT RETURNING

insert :: DatabaseEntity Postgres db (TableEntity table) -> SqlInsertValues Postgres (table (QExpr Postgres s)) -> PgInsertOnConflict table -> SqlInsert Postgres table

A beam-postgres-specific version of insert, which provides fuller support for the much richer Postgres INSERT syntax. This allows you to specify ON CONFLICT actions. For even more complete support, see insertReturning.

insertReturning :: Projectible Postgres a => DatabaseEntity Postgres be (TableEntity table) -> SqlInsertValues Postgres (table (QExpr Postgres s)) -> PgInsertOnConflict table -> Maybe (table (QExpr Postgres PostgresInaccessible) -> a) -> PgInsertReturning (QExprToIdentity a)

The full Postgres INSERT syntax, supporting conflict actions and the RETURNING CLAUSE. See PgInsertOnConflict for how to specify a conflict action or provide onConflictDefault to preserve the behavior without any ON CONFLICT clause. The last argument takes a newly inserted row and returns the expression to be returned as part of the RETURNING clause. For a backend-agnostic version of this functionality see MonadBeamInsertReturning. Use runInsertReturning to get the results.

insertDefaults :: SqlInsertValues Postgres tbl

The Postgres DEFAULT VALUES clause for the INSERT command.

runPgInsertReturningList :: (MonadBeam be m, BeamSqlBackendSyntax be ~ PgCommandSyntax, FromBackendRow be a) => PgInsertReturning a -> m [a]

data PgInsertReturning a

The most general kind of INSERT that postgres can perform

Constructors

PgInsertReturning PgSyntax

PgInsertReturningEmpty

Instances

PgDebugStmt (PgInsertReturning a)

Defined in Database.Beam.Postgres.Debug

Methods

pgStmtSyntax :: PgInsertReturning a -> Maybe PgSyntax

Specifying conflict actions

newtype PgInsertOnConflict (tbl :: (* -> *) -> *)

What to do when an INSERT statement inserts a row into the table tbl that violates a constraint.

Constructors

PgInsertOnConflict (tbl (QField QInternal) -> PgInsertOnConflictSyntax)

onConflictDefault :: PgInsertOnConflict tbl

By default, Postgres will throw an error when a conflict is detected. This preserves that functionality.

onConflict :: Beamable tbl => SqlConflictTarget Postgres tbl -> SqlConflictAction Postgres tbl -> PgInsertOnConflict tbl

Tells postgres what to do on an INSERT conflict. The first argument is the type of conflict to provide an action for. For example, to only provide an action for certain fields, use conflictingFields. Or to only provide an action over certain fields where a particular condition is met, use conflictingFields. If you have a particular constraint violation in mind, use conflictingConstraint. To perform an action on any conflict, use anyConflict.

See the Postgres documentation.

conflictingConstraint :: Text -> SqlConflictTarget Postgres tbl

Perform the action only if the given named constraint is violated

class BeamSqlBackend be => BeamHasInsertOnConflict be where

Associated Types

data SqlConflictTarget be (table :: (Type -> Type) -> Type)

Specifies the kind of constraint that must be violated for the action to occur

data SqlConflictAction be (table :: (Type -> Type) -> Type)

What to do when an INSERT statement inserts a row into the table tbl that violates a constraint.

Methods

insertOnConflict :: forall table (db :: (Type -> Type) -> Type) s. Beamable table => DatabaseEntity be db (TableEntity table) -> SqlInsertValues be (table (QExpr be s)) -> SqlConflictTarget be table -> SqlConflictAction be table -> SqlInsert be table

anyConflict :: forall (table :: (Type -> Type) -> Type). SqlConflictTarget be table

conflictingFields :: Projectible be proj => (table (QExpr be QInternal) -> proj) -> SqlConflictTarget be table

conflictingFieldsWhere :: Projectible be proj => (table (QExpr be QInternal) -> proj) -> (forall s. table (QExpr be s) -> QExpr be s Bool) -> SqlConflictTarget be table

onConflictDoNothing :: forall (table :: (Type -> Type) -> Type). SqlConflictAction be table

onConflictUpdateSet :: Beamable table => (forall s. table (QField s) -> table (QExpr be s) -> QAssignment be s) -> SqlConflictAction be table

onConflictUpdateSetWhere :: Beamable table => (forall s. table (QField s) -> table (QExpr be s) -> QAssignment be s) -> (forall s. table (QField s) -> table (QExpr be s) -> QExpr be s Bool) -> SqlConflictAction be table

Instances

BeamHasInsertOnConflict Postgres

Defined in Database.Beam.Postgres.Full

Associated Types

data SqlConflictTarget Postgres table

data SqlConflictAction Postgres table

Methods

insertOnConflict :: forall table (db :: (Type -> Type) -> Type) s. Beamable table => DatabaseEntity Postgres db (TableEntity table) -> SqlInsertValues Postgres (table (QExpr Postgres s)) -> SqlConflictTarget Postgres table -> SqlConflictAction Postgres table -> SqlInsert Postgres table

anyConflict :: forall (table :: (Type -> Type) -> Type). SqlConflictTarget Postgres table

conflictingFields :: Projectible Postgres proj => (table (QExpr Postgres QInternal) -> proj) -> SqlConflictTarget Postgres table

conflictingFieldsWhere :: Projectible Postgres proj => (table (QExpr Postgres QInternal) -> proj) -> (forall s. table (QExpr Postgres s) -> QExpr Postgres s Bool) -> SqlConflictTarget Postgres table

onConflictDoNothing :: forall (table :: (Type -> Type) -> Type). SqlConflictAction Postgres table

onConflictUpdateSet :: Beamable table => (forall s. table (QField s) -> table (QExpr Postgres s) -> QAssignment Postgres s) -> SqlConflictAction Postgres table

onConflictUpdateSetWhere :: Beamable table => (forall s. table (QField s) -> table (QExpr Postgres s) -> QAssignment Postgres s) -> (forall s. table (QField s) -> table (QExpr Postgres s) -> QExpr Postgres s Bool) -> SqlConflictAction Postgres table

onConflictUpdateAll :: forall be (table :: (Type -> Type) -> Type). (BeamHasInsertOnConflict be, Beamable table) => SqlConflictAction be table

onConflictUpdateInstead :: (BeamHasInsertOnConflict be, Beamable table, ProjectibleWithPredicate AnyType () (InaccessibleQAssignment be) proj) => (table (Const (InaccessibleQAssignment be) :: Type -> Type) -> proj) -> SqlConflictAction be table

UPDATE RETURNING

data PgUpdateReturning a

The most general kind of UPDATE that postgres can perform

You can build this from a SqlUpdate by using returning

update tbl where `returning` projection

Run the result with runPgUpdateReturningList

Constructors

PgUpdateReturning PgSyntax

PgUpdateReturningEmpty

Instances

PgDebugStmt (PgUpdateReturning a)

Defined in Database.Beam.Postgres.Debug

Methods

pgStmtSyntax :: PgUpdateReturning a -> Maybe PgSyntax

runPgUpdateReturningList :: (MonadBeam be m, BeamSqlBackendSyntax be ~ PgCommandSyntax, FromBackendRow be a) => PgUpdateReturning a -> m [a]

updateReturning :: Projectible Postgres a => DatabaseEntity Postgres be (TableEntity table) -> (forall s. table (QField s) -> QAssignment Postgres s) -> (forall s. table (QExpr Postgres s) -> QExpr Postgres s Bool) -> (table (QExpr Postgres PostgresInaccessible) -> a) -> PgUpdateReturning (QExprToIdentity a)

Postgres UPDATE ... RETURNING statement support. The last argument takes the newly inserted row and returns the values to be returned. Use runUpdateReturning to get the results.

DELETE RETURNING

newtype PgDeleteReturning a

The most general kind of DELETE that postgres can perform

You can build this from a SqlDelete by using returning

delete tbl where `returning` projection

Run the result with runPgDeleteReturningList

Constructors

PgDeleteReturning PgSyntax

Instances

PgDebugStmt (PgDeleteReturning a)

Defined in Database.Beam.Postgres.Debug

Methods

pgStmtSyntax :: PgDeleteReturning a -> Maybe PgSyntax

runPgDeleteReturningList :: (MonadBeam be m, BeamSqlBackendSyntax be ~ PgCommandSyntax, FromBackendRow be a) => PgDeleteReturning a -> m [a]

deleteReturning :: Projectible Postgres a => DatabaseEntity Postgres be (TableEntity table) -> (forall s. table (QExpr Postgres s) -> QExpr Postgres s Bool) -> (table (QExpr Postgres PostgresInaccessible) -> a) -> PgDeleteReturning (QExprToIdentity a)

Postgres DELETE ... RETURNING statement support. The last argument takes the newly inserted row and returns the values to be returned. Use runDeleteReturning to get the results.

Generalized RETURNING

class PgReturning cmd where

Associated Types

type PgReturningType cmd :: * -> *

Methods

returning :: (Beamable tbl, Projectible Postgres a) => cmd Postgres tbl -> (tbl (QExpr Postgres PostgresInaccessible) -> a) -> PgReturningType cmd (QExprToIdentity a)

Instances

PgReturning SqlInsert

Defined in Database.Beam.Postgres.Full

Associated Types

type PgReturningType SqlInsert :: Type -> Type

Methods

returning :: (Beamable tbl, Projectible Postgres a) => SqlInsert Postgres tbl -> (tbl (QExpr Postgres PostgresInaccessible) -> a) -> PgReturningType SqlInsert (QExprToIdentity a)

PgReturning SqlUpdate

Defined in Database.Beam.Postgres.Full

Associated Types

type PgReturningType SqlUpdate :: Type -> Type

Methods

returning :: (Beamable tbl, Projectible Postgres a) => SqlUpdate Postgres tbl -> (tbl (QExpr Postgres PostgresInaccessible) -> a) -> PgReturningType SqlUpdate (QExprToIdentity a)

PgReturning SqlDelete

Defined in Database.Beam.Postgres.Full

Associated Types

type PgReturningType SqlDelete :: Type -> Type

Methods

returning :: (Beamable tbl, Projectible Postgres a) => SqlDelete Postgres tbl -> (tbl (QExpr Postgres PostgresInaccessible) -> a) -> PgReturningType SqlDelete (QExprToIdentity a)

Orphan instances

BeamHasInsertOnConflict Postgres

Associated Types

data SqlConflictTarget Postgres table

data SqlConflictAction Postgres table

Methods

insertOnConflict :: forall table (db :: (Type -> Type) -> Type) s. Beamable table => DatabaseEntity Postgres db (TableEntity table) -> SqlInsertValues Postgres (table (QExpr Postgres s)) -> SqlConflictTarget Postgres table -> SqlConflictAction Postgres table -> SqlInsert Postgres table

anyConflict :: forall (table :: (Type -> Type) -> Type). SqlConflictTarget Postgres table

conflictingFields :: Projectible Postgres proj => (table (QExpr Postgres QInternal) -> proj) -> SqlConflictTarget Postgres table

conflictingFieldsWhere :: Projectible Postgres proj => (table (QExpr Postgres QInternal) -> proj) -> (forall s. table (QExpr Postgres s) -> QExpr Postgres s Bool) -> SqlConflictTarget Postgres table

onConflictDoNothing :: forall (table :: (Type -> Type) -> Type). SqlConflictAction Postgres table

onConflictUpdateSet :: Beamable table => (forall s. table (QField s) -> table (QExpr Postgres s) -> QAssignment Postgres s) -> SqlConflictAction Postgres table

onConflictUpdateSetWhere :: Beamable table => (forall s. table (QField s) -> table (QExpr Postgres s) -> QAssignment Postgres s) -> (forall s. table (QField s) -> table (QExpr Postgres s) -> QExpr Postgres s Bool) -> SqlConflictAction Postgres table