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;

Manipulation functions

runInsertMany

Arguments

:: Connection

-> Table columns columns'

Table to insert into

-> [columns]

Rows to insert

-> IO Int64

Number of rows inserted

Insert rows into a table

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

Insert rows into a table and return a function of the inserted rows

runInsertManyReturning'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 runInsertManyReturning.

runUpdateEasy

Arguments

:: Default Updater columnsR columnsW

=> Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsR)

Update function to apply to chosen rows

-> (columnsR -> Column PGBool)

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

Update rows in a table.

(N.B. runUpdateEasy's "returning" counterpart "runUpdateEasyReturning" hasn't been implemented. File an issue if you want it!)

runUpdate

Arguments

:: Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsW)

Update function to apply to chosen rows

-> (columnsR -> Column PGBool)

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

Update rows in a table. You'll probably find it more convenient to use runUpdateEasy (although runUpdate provides more fine-grained control if you need it).

Be careful: providing Nothing to a column created by optional updates the column to its default value. Many users have been confused by this because they assume it means that the column is to be left unchanged.

runUpdateReturning

Arguments

:: Default QueryRunner columnsReturned haskells

=> Connection

-> Table columnsW columnsR

Table to update

-> (columnsR -> columnsW)

Update function to apply to chosen rows

-> (columnsR -> Column PGBool)

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

Update rows in a table and return a function of the updated rows

Be careful: providing Nothing to a column created by optional updates the column to its default value. Many users have been confused by this because they assume it means that the column is to be left unchanged.

runUpdateReturning'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.

runDelete

Arguments

:: Connection

-> Table a columnsR

Table to delete rows from

-> (columnsR -> Column PGBool)

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

Delete rows from a table

Explicit versions

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

You probably don't need this, but can just use runInsertReturning instead. You only need it if you want to run an INSERT RETURNING statement but need to be explicit about the QueryRunner.

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

You probably don't need this, but can just use runInsertManyReturning instead. You only need it if you want to run an UPDATE RETURNING statement but need to be explicit about the QueryRunner.

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

You probably don't need this, but can just use runUpdateReturning instead. You only need it if you want to run an UPDATE RETURNING statement but need to be explicit about the QueryRunner.

Deprecated versions

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

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

Returns the number of rows inserted

runInsertReturning :: Default QueryRunner columnsReturned haskells => Connection -> Table columnsW columnsR -> columnsW -> (columnsR -> columnsReturned) -> 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 PGBool) -> SqlUpdate

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

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

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

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

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

arrangeDeleteSql :: Table a columnsR -> (columnsR -> Column PGBool) -> 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 PGBool) -> (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 PGBool) -> (columnsR -> columnsReturned) -> String

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

Other

data Unpackspec columns columns'

Instances

Profunctor 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 => (b -> c) -> Unpackspec a b -> Unpackspec a c

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

ProductProfunctor 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

Methods

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

Default Unpackspec (Column a) (Column a)

Methods

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

Functor (Unpackspec a)

Methods

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

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

Applicative (Unpackspec a)

Methods

pure :: a -> Unpackspec a a

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

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

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

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