Opaleye.Manipulation

Inserts, updates and deletes

Please note that Opaleye currently only supports INSERT or UPDATE with constant values, not the result of SELECTs. That is, you can generate SQL of the form

INSERT INTO thetable (John, 1);

but not

INSERT INTO thetable
   SELECT John,
   (SELECT num FROM thetable ORDER BY num DESC LIMIT 1) + 1;

Documentation

data Delete haskells

Constructors

Delete

Fields

• dTable :: Table fieldsW fieldsR
• dWhere :: fieldsR -> Field SqlBool
• dReturning :: Returning fieldsR haskells

data Update haskells

Constructors

Update

Fields

• uTable :: Table fieldsW fieldsR
• uUpdateWith :: fieldsR -> fieldsW

  Be careful: providing Nothing to a field created by optional updates the field to its default value. Many users have been confused by this because they assume it means that the field is to be left unchanged. For an easier time wrap your update function in updateEasy.

• uWhere :: fieldsR -> Field SqlBool
• uReturning :: Returning fieldsR haskells

data Insert haskells

Constructors

Insert

Fields

• iTable :: Table fieldsW fieldsR
• iRows :: [fieldsW]
• iReturning :: Returning fieldsR haskells
• iOnConflict :: Maybe OnConflict

  NB There is a clash of terminology between Haskell and Postgres.

  • iOnConflict = Nothing means omit ON CONFLICT statement
  • iOnConflict = Just DoNothing means ON CONFLICT DO NOTHING

runInsert_

Arguments

:: Connection

-> Insert haskells

-> IO haskells

Returns a type that depends on the Returning that you provided when creating the Insert.

Run the Insert. To create an Insert use the Insert constructor.

runUpdate_

Arguments

:: Connection

-> Update haskells

-> IO haskells

Returns a type that depends on the Returning that you provided when creating the Update.

Run the Update. To create an Update use the Update constructor.

runDelete_

Arguments

:: Connection

-> Delete haskells

-> IO haskells

Returns a type that depends on the Returning that you provided when creating the Delete.

Run the Delete. To create an Delete use the Delete constructor.

updateEasy

Arguments

:: Default Updater fieldsR fieldsW

=> (fieldsR -> fieldsR)

-> fieldsR -> fieldsW

A convenient wrapper for writing your update function

uUpdateWith = updateEasy (\... -> ...)

rCount :: Returning fieldsR Int64

Return the number of rows inserted or updated

rReturning

Arguments

:: Default FromFields fields haskells

=> (fieldsR -> fields)

-> Returning fieldsR [haskells]

Return a function of the inserted or updated rows

rReturning's use of the Default FromFields typeclass means that the compiler will have trouble inferring types. It is strongly recommended that you provide full type signatures when using rReturning.

rReturningExplicit

Arguments

:: FromFields fields haskells

-> (fieldsR -> fields)

-> Returning fieldsR [haskells]

Return a function of the inserted or updated rows. Explicit version. You probably just want to use rReturning instead.

runInsert :: Connection -> Table fields fields' -> fields -> IO Int64

Deprecated: runInsert will be removed in version 0.7. Use runInsertMany instead.

Returns the number of rows inserted

runInsertReturning :: Default QueryRunner fieldsReturned haskells => Connection -> Table fieldsW fieldsR -> fieldsW -> (fieldsR -> fieldsReturned) -> IO [haskells]

Deprecated: runInsertReturning will be removed in version 0.7. Use runInsertManyReturning instead.

runInsertReturning's use of the Default typeclass means that the compiler will have trouble inferring types. It is strongly recommended that you provide full type signatures when using runInsertReturning.

arrangeInsert :: Table columns a -> columns -> SqlInsert

Deprecated: You probably want runInsertMany instead. Will be removed in version 0.7.

arrangeInsertSql :: Table columns a -> columns -> String

Deprecated: You probably want runInsertMany instead. Will be removed in version 0.7.

arrangeInsertMany :: Table columns a -> NonEmpty columns -> SqlInsert

Deprecated: You probably want runInsertMany instead. Will be removed in version 0.7.

arrangeInsertManySql :: Table columns a -> NonEmpty columns -> String

Deprecated: You probably want runInsertMany instead. Will be removed in version 0.7.

arrangeUpdate :: Table columnsW columnsR -> (columnsR -> columnsW) -> (columnsR -> Column SqlBool) -> SqlUpdate

Deprecated: You probably want runUpdate instead. Will be removed in version 0.7.

arrangeUpdateSql :: Table columnsW columnsR -> (columnsR -> columnsW) -> (columnsR -> Column SqlBool) -> String

Deprecated: You probably want runUpdate instead. Will be removed in version 0.7.

arrangeDelete :: Table a columnsR -> (columnsR -> Column SqlBool) -> SqlDelete

Deprecated: You probably want runDelete instead. Will be removed in version 0.7.

arrangeDeleteSql :: Table a columnsR -> (columnsR -> Column SqlBool) -> String

Deprecated: You probably want runDelete instead. Will be removed in version 0.7.

arrangeInsertManyReturning :: Unpackspec columnsReturned ignored -> Table columnsW columnsR -> NonEmpty columnsW -> (columnsR -> columnsReturned) -> Returning SqlInsert

Deprecated: You probably want runInsertMany instead. Will be removed in version 0.7.

arrangeInsertManyReturningSql :: Unpackspec columnsReturned ignored -> Table columnsW columnsR -> NonEmpty columnsW -> (columnsR -> columnsReturned) -> String

Deprecated: You probably want runInsertManyReturning instead. Will be removed in version 0.7.

arrangeUpdateReturning :: Unpackspec columnsReturned ignored -> Table columnsW columnsR -> (columnsR -> columnsW) -> (columnsR -> Column SqlBool) -> (columnsR -> columnsReturned) -> Returning SqlUpdate

Deprecated: You probably want runUpdateReturning instead. Will be removed in version 0.7.

arrangeUpdateReturningSql :: Unpackspec columnsReturned ignored -> Table columnsW columnsR -> (columnsR -> columnsW) -> (columnsR -> Column SqlBool) -> (columnsR -> columnsReturned) -> String

Deprecated: You probably want runUpdateReturning instead. Will be removed in version 0.7.

runInsertManyOnConflictDoNothing

Arguments

:: Connection

-> Table columns columns'

Table to insert into

-> [columns]

Rows to insert

-> IO Int64

Number of rows inserted

Deprecated: Use runInsert_. Will be removed in version 0.8.

Insert rows into a table with ON CONFLICT DO NOTHING

runInsertManyReturningOnConflictDoNothing

Arguments

:: Default QueryRunner columnsReturned haskells

=> Connection

-> Table columnsW columnsR

Table to insert into

-> [columnsW]

Rows to insert

-> (columnsR -> columnsReturned)

Function f to apply to the inserted rows

-> IO [haskells]

Returned rows after f has been applied

Deprecated: Use runInsert_. Will be removed in version 0.8.

Insert rows into a table with ON CONFLICT DO NOTHING and return a function of the inserted rows

runInsertManyReturningOnConflictDoNothing's use of the Default typeclass means that the compiler will have trouble inferring types. It is strongly recommended that you provide full type signatures when using it.

runInsertMany

Arguments

:: Connection

-> Table columns columns'

Table to insert into

-> [columns]

Rows to insert

-> IO Int64

Number of rows inserted

Use runInsert_ instead. Will be deprecated in version 0.7.

runInsertManyReturning

Arguments

:: Default QueryRunner columnsReturned haskells

=> Connection

-> Table columnsW columnsR

Table to insert into

-> [columnsW]

Rows to insert

-> (columnsR -> columnsReturned)

Function f to apply to the inserted rows

-> IO [haskells]

Returned rows after f has been applied

Use runInsert_ instead. Will be deprecated in version 0.7.

runInsertReturningExplicit :: QueryRunner columnsReturned haskells -> Connection -> Table columnsW columnsR -> columnsW -> (columnsR -> columnsReturned) -> IO [haskells]

Use runInsert_ instead. Will be deprecated in version 0.7.

runInsertManyReturningExplicit :: QueryRunner columnsReturned haskells -> Connection -> Table columnsW columnsR -> [columnsW] -> (columnsR -> columnsReturned) -> IO [haskells]

Use runInsert_ instead. Will be deprecated in version 0.7.

runInsertManyReturningOnConflictDoNothingExplicit :: QueryRunner columnsReturned haskells -> Connection -> Table columnsW columnsR -> [columnsW] -> (columnsR -> columnsReturned) -> IO [haskells]

Use runInsert_ instead. Will be deprecated in version 0.7.

runUpdateEasy

Arguments

:: Default Updater columnsR columnsW

=> Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsR)

Update function to apply to chosen rows

-> (columnsR -> Column SqlBool)

Predicate function f to choose which rows to update. runUpdate will update rows for which f returns TRUE and leave unchanged rows for which f returns FALSE.

-> IO Int64

The number of rows updated

Use runUpdate_ instead. Will be deprecated in version 0.7.

runUpdate

Arguments

:: Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsW)

Update function to apply to chosen rows

-> (columnsR -> Column SqlBool)

Predicate function f to choose which rows to update. runUpdate will update rows for which f returns TRUE and leave unchanged rows for which f returns FALSE.

-> IO Int64

The number of rows updated

Use runUpdate_ instead. Will be deprecated in version 0.7.

runUpdateReturning

Arguments

:: Default QueryRunner columnsReturned haskells

=> Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsW)

Update function to apply to chosen rows

-> (columnsR -> Column SqlBool)

Predicate function f to choose which rows to update. runUpdate will update rows for which f returns TRUE and leave unchanged rows for which f returns FALSE.

-> (columnsR -> columnsReturned)

Functon g to apply to the updated rows

-> IO [haskells]

Returned rows after g has been applied

Use runUpdate_ instead. Will be deprecated in version 0.7.

runUpdateReturningExplicit :: QueryRunner columnsReturned haskells -> Connection -> Table columnsW columnsR -> (columnsR -> columnsW) -> (columnsR -> Column SqlBool) -> (columnsR -> columnsReturned) -> IO [haskells]

Use runUpdate_ instead. Will be deprecated in version 0.7.

runDelete

Arguments

:: Connection

-> Table a columnsR

Table to delete rows from

-> (columnsR -> Column SqlBool)

Predicate function f to choose which rows to delete. runDelete will delete rows for which f returns TRUE and leave unchanged rows for which f returns FALSE.

-> IO Int64

The number of rows deleted

Use runDelete_ instead. Will be deprecated in 0.7.

Other

Do not use the export of Unpackspec. It will not be exported in version 0.7.

data Unpackspec columns columns'

Instances

Profunctor Unpackspec

Defined in Opaleye.Internal.Unpackspec

Methods

dimap :: (a -> b) -> (c -> d) -> Unpackspec b c -> Unpackspec a d

lmap :: (a -> b) -> Unpackspec b c -> Unpackspec a c

rmap :: (b -> c) -> Unpackspec a b -> Unpackspec a c

(#.) :: Coercible c b => q b c -> Unpackspec a b -> Unpackspec a c

(.#) :: Coercible b a => Unpackspec b c -> q a b -> Unpackspec a c

ProductProfunctor Unpackspec

Defined in Opaleye.Internal.Unpackspec

Methods

purePP :: b -> Unpackspec a b

(****) :: Unpackspec a (b -> c) -> Unpackspec a b -> Unpackspec a c

empty :: Unpackspec () ()

(***!) :: Unpackspec a b -> Unpackspec a' b' -> Unpackspec (a, a') (b, b')

SumProfunctor Unpackspec

Defined in Opaleye.Internal.Unpackspec

Methods

(+++!) :: Unpackspec a b -> Unpackspec a' b' -> Unpackspec (Either a a') (Either b b')

Default Unpackspec (Column a) (Column a)

Defined in Opaleye.Internal.Unpackspec

Methods

def :: Unpackspec (Column a) (Column a)

Default Unpackspec a b => Default Unpackspec (MaybeFields a) (MaybeFields b)

Defined in Opaleye.Internal.MaybeFields

Methods

def :: Unpackspec (MaybeFields a) (MaybeFields b)

Functor (Unpackspec a)

Defined in Opaleye.Internal.Unpackspec

Methods

fmap :: (a0 -> b) -> Unpackspec a a0 -> Unpackspec a b

(<$) :: a0 -> Unpackspec a b -> Unpackspec a a0

Applicative (Unpackspec a)

Defined in Opaleye.Internal.Unpackspec

Methods

pure :: a0 -> Unpackspec a a0

(<*>) :: Unpackspec a (a0 -> b) -> Unpackspec a a0 -> Unpackspec a b

liftA2 :: (a0 -> b -> c) -> Unpackspec a a0 -> Unpackspec a b -> Unpackspec a c

(*>) :: Unpackspec a a0 -> Unpackspec a b -> Unpackspec a b

(<*) :: Unpackspec a a0 -> Unpackspec a b -> Unpackspec a a0

Currently DoNothing is the only conflict action supported by Opaleye.

data OnConflict

Constructors

DoNothing

ON CONFLICT DO NOTHING