Database.PostgreSQL.Query.Functions

Raw query execution

pgQuery :: (MonadPostgres m, ToSqlBuilder q, FromRow r, HasCallStack) => q -> m [r]

Execute query generated by SqlBuilder. Typical use case:

let userName = "Vovka Erohin" :: Text
pgQuery [sqlExp| SELECT id, name FROM users WHERE name = #{userName}|]

Or

let userName = "Vovka Erohin" :: Text
pgQuery $ Qp "SELECT id, name FROM users WHERE name = ?" [userName]

Which is almost the same. In both cases proper value escaping is performed so you stay protected from sql injections.

pgQueryWithMasker :: (MonadPostgres m, ToSqlBuilder q, FromRow r, HasCallStack) => LogMasker -> q -> m [r]

pgExecute :: (MonadPostgres m, ToSqlBuilder q, HasCallStack) => q -> m Int64

Execute arbitrary query and return count of affected rows

pgExecuteWithMasker :: (MonadPostgres m, ToSqlBuilder q, HasCallStack) => LogMasker -> q -> m Int64

Transactions

pgWithTransaction :: (HasPostgres m, MonadBaseControl IO m, TransactionSafe m, HasCallStack) => (HasCallStack => m a) -> m a

Execute all queries inside one transaction. Rollback transaction on exceptions

pgWithSavepoint :: (HasPostgres m, MonadBaseControl IO m, TransactionSafe m, HasCallStack) => (HasCallStack => m a) -> m a

Same as pgWithTransaction but executes queries inside savepoint

pgWithTransactionMode :: (HasPostgres m, MonadBaseControl IO m, TransactionSafe m, HasCallStack) => TransactionMode -> (HasCallStack => m a) -> m a

Wrapper for withTransactionMode: Execute an action inside a SQL transaction with a given transaction mode.

pgWithTransactionModeRetry :: (HasPostgres m, MonadBaseControl IO m, TransactionSafe m, HasCallStack) => TransactionMode -> (SqlError -> Bool) -> (HasCallStack => m a) -> m a

Wrapper for withTransactionModeRetry: Like pgWithTransactionMode, but also takes a custom callback to determine if a transaction should be retried if an SqlError occurs. If the callback returns True, then the transaction will be retried. If the callback returns False, or an exception other than an SqlError occurs then the transaction will be rolled back and the exception rethrown.

pgWithTransactionSerializable :: (HasPostgres m, MonadBaseControl IO m, TransactionSafe m) => (HasCallStack => m a) -> m a

Wrapper for withTransactionSerializable: Execute an action inside of a Serializable transaction. If a serialization failure occurs, roll back the transaction and try again. Be warned that this may execute the IO action multiple times.

A Serializable transaction creates the illusion that your program has exclusive access to the database. This means that, even in a concurrent setting, you can perform queries in sequence without having to worry about what might happen between one statement and the next.

Auxiliary

pgRepsertRow

Arguments

:: (MonadPostgres m, MonadLogger m, ToMarkedRow wrow, ToMarkedRow urow, HasCallStack)

=> FN

Table name

-> wrow

where condition

-> urow

update row

-> m ()

Perform repsert of the same row, first trying "update where" then "insert" with concatenated fields. Which means that if you run

pgRepsertRow "emails" (MR [("user_id", mkValue uid)]) (MR [("email", mkValue email)])

Then firstly will be performed

UPDATE "emails" SET email = '[e-mail]' WHERE "user_id" = 1234

And if no one row is affected (which is returned by pgExecute), then

INSERT INTO "emails" ("user_id", "email") VALUES (1234, '[e-mail]')

will be performed