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.

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 FromFields 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

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

runInsertManyReturning

Arguments

:: Default FromFields 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_ instead. Will be removed in version 0.8.

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

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

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

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

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

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

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

Deprecated: Use runUpdate_ instead. Will be removed in version 0.8.

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

Deprecated: Use runUpdate_ instead. Will be removed in version 0.8.

runUpdateReturning

Arguments

:: Default FromFields 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

Deprecated: Use runUpdate_ instead. Will be removed in version 0.8.

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

Deprecated: Use runUpdate_ instead. Will be removed in version 0.8.

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

Deprecated: Use runDelete_ instead. Will be removed in version 0.8.

Currently DoNothing is the only conflict action supported by Opaleye.

data OnConflict

Constructors

DoNothing

ON CONFLICT DO NOTHING