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;

Insert

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.

data Insert haskells

Constructors

forall fieldsW fieldsR. 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

Update

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.

data Update haskells

Constructors

forall fieldsW fieldsR. Update

Fields

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

  Be careful: providing Nothing to a field created by optionalTableField 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

updateEasy :: Default Updater fieldsR fieldsW => (fieldsR -> fieldsR) -> fieldsR -> fieldsW

A convenient wrapper for writing your update function. updateEasy protects you from accidentally updating an optionalTableField with Nothing (i.e. SQL DEFAULT). See uUpdateWith.

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

Delete

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.

data Delete haskells

Constructors

forall fieldsW fieldsR. Delete

Fields

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

Returning

data Returning fields haskells

Represents a RETURNING statement for a manipulation query.

rCount :: Returning fieldsR Int64

Return the number of rows inserted or updated

rReturning :: 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.

rReturningI :: Default (Inferrable FromFields) fields haskells => (fieldsR -> fields) -> Returning fieldsR [haskells]

Like rReturning but with better inference properties. On the other hand the mapping from SQL fields to Haskell types is less flexible.

rReturningExplicit :: 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.

On conflict

Currently doNothing is the only conflict action supported by Opaleye.

data OnConflict

doNothing :: OnConflict

Deprecated

runInsert_ :: Connection -> Insert haskells -> IO haskells

Deprecated: Use runInsert instead. Will be removed in 0.11.

runUpdate_ :: Connection -> Update haskells -> IO haskells

Deprecated: Use runUpdate instead. Will be removed in 0.11.

runDelete_ :: Connection -> Delete haskells -> IO haskells

Deprecated: Use runDelete instead. Will be removed in 0.11.

DoNothing

Use doNothing instead. DoNothing will be removed in version 0.11.

data OnConflict

Constructors

DoNothing

Deprecated: Use doNothing instead. DoNothing will be removed in version 0.11

ON CONFLICT DO NOTHING