postgresql-simple-0.7.0.1: Mid-Level PostgreSQL client library

Copyright | (c) 2011 MailRank Inc. (c) 2011-2012 Leon P Smith
License | BSD3
Maintainer | Leon P Smith <leon@melding-monads.com>
Stability | experimental
Safe Haskell | None
Language | Haskell2010

Database.PostgreSQL.Simple

Contents

- Writing queries
  - The Query type
  - Parameter substitution
    - Type inference
  - Substituting a single parameter
  - Representing a list of values
  - Modifying multiple rows at once
  - `RETURNING`: modifications that return results
- Extracting results
  - Handling null values
  - Type conversions
- Types
  - Exceptions
- Connection management
- Queries that return results
  - Queries taking parser as argument
- Queries that stream results
  - Queries that stream results taking a parser as an argument
- Statements that do not return results
- Transaction handling
- Helper functions

Description

A mid-level client library for the PostgreSQL database, aimed at ease of use and high performance.

Synopsis

- data Connection
- data Query
- class ToRow a
- class FromRow a
- newtype In a = In a
- newtype Binary a = Binary {
  - fromBinary :: a
  }
- newtype Only a = Only {
  - fromOnly :: a
  }
- data h :. t = h :. t
- data SomePostgreSqlException = Exception e => SomePostgreSqlException e
- data SqlError = SqlError {
  - sqlState :: ByteString
  - sqlExecStatus :: ExecStatus
  - sqlErrorMsg :: ByteString
  - sqlErrorDetail :: ByteString
  - sqlErrorHint :: ByteString
  }
- data ExecStatus
  - = EmptyQuery
  - | CommandOk
  - | TuplesOk
  - | CopyOut
  - | CopyIn
  - | CopyBoth
  - | BadResponse
  - | NonfatalError
  - | FatalError
  - | SingleTuple
  - | PipelineSync
  - | PipelineAbort
- data FormatError = FormatError {
  - fmtMessage :: String
  - fmtQuery :: Query
  - fmtParams :: [ByteString]
  }
- data QueryError = QueryError {
  - qeMessage :: String
  - qeQuery :: Query
  }
- data ResultError
  - = Incompatible {
    - errSQLType :: String
    - errSQLTableOid :: Maybe Oid
    - errSQLField :: String
    - errHaskellType :: String
    - errMessage :: String
    }
  - | UnexpectedNull {
    - errSQLType :: String
    - errSQLTableOid :: Maybe Oid
    - errSQLField :: String
    - errHaskellType :: String
    - errMessage :: String
    }
  - | ConversionFailed {
    - errSQLType :: String
    - errSQLTableOid :: Maybe Oid
    - errSQLField :: String
    - errHaskellType :: String
    - errMessage :: String
    }
- connectPostgreSQL :: ByteString -> IO Connection
- close :: Connection -> IO ()
- connect :: ConnectInfo -> IO Connection
- withConnect :: ConnectInfo -> (Connection -> IO c) -> IO c
- data ConnectInfo = ConnectInfo {
  - connectHost :: String
  - connectPort :: Word16
  - connectUser :: String
  - connectPassword :: String
  - connectDatabase :: String
  }
- defaultConnectInfo :: ConnectInfo
- postgreSQLConnectionString :: ConnectInfo -> ByteString
- query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]
- query_ :: FromRow r => Connection -> Query -> IO [r]
- queryWith :: ToRow q => RowParser r -> Connection -> Query -> q -> IO [r]
- queryWith_ :: RowParser r -> Connection -> Query -> IO [r]
- data FoldOptions = FoldOptions {
  - fetchQuantity :: !FetchQuantity
  - transactionMode :: !TransactionMode
  }
- data FetchQuantity
  - = Automatic
  - | Fixed !Int
- defaultFoldOptions :: FoldOptions
- fold :: (FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWithOptions :: (FromRow row, ToRow params) => FoldOptions -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- fold_ :: FromRow r => Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- foldWithOptions_ :: FromRow r => FoldOptions -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- forEach :: (ToRow q, FromRow r) => Connection -> Query -> q -> (r -> IO ()) -> IO ()
- forEach_ :: FromRow r => Connection -> Query -> (r -> IO ()) -> IO ()
- returning :: (ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r]
- foldWith :: ToRow params => RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWithOptionsAndParser :: ToRow params => FoldOptions -> RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- foldWith_ :: RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- foldWithOptionsAndParser_ :: FoldOptions -> RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a
- forEachWith :: ToRow q => RowParser r -> Connection -> Query -> q -> (r -> IO ()) -> IO ()
- forEachWith_ :: RowParser r -> Connection -> Query -> (r -> IO ()) -> IO ()
- returningWith :: ToRow q => RowParser r -> Connection -> Query -> [q] -> IO [r]
- execute :: ToRow q => Connection -> Query -> q -> IO Int64
- execute_ :: Connection -> Query -> IO Int64
- executeMany :: ToRow q => Connection -> Query -> [q] -> IO Int64
- withTransaction :: Connection -> IO a -> IO a
- withSavepoint :: Connection -> IO a -> IO a
- begin :: Connection -> IO ()
- commit :: Connection -> IO ()
- rollback :: Connection -> IO ()
- formatMany :: ToRow q => Connection -> Query -> [q] -> IO ByteString
- formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString

# Writing queries

SQL-based applications are somewhat notorious for their susceptibility to attacks through the injection of maliciously crafted data. The primary reason for widespread vulnerability to SQL injections is that many applications are sloppy in handling user data when constructing SQL queries.

This library provides a `Query` type and a parameter substitution facility to address both ease of use and security.

## The Query type

A `Query` is a `newtype`-wrapped `ByteString`. It intentionally exposes a tiny API that is not compatible with the `ByteString` API; this makes it difficult to construct queries from fragments of strings. The `query` and `execute` functions require queries to be of type `Query`.

To most easily construct a query, enable GHC's `OverloadedStrings` language extension and write your query as a normal literal string.

    {-# LANGUAGE OverloadedStrings #-}

    import Database.PostgreSQL.Simple

    hello :: IO Int
    hello = do
      conn <- connectPostgreSQL ""
      [Only i] <- query_ conn "select 2 + 2"
      return i

A `Query` value does not represent the actual query that will be executed, but is a template for constructing the final query.

## Parameter substitution

Since applications need to be able to construct queries with parameters that change, this library provides a query substitution capability.

The `Query` template accepted by `query` and `execute` can contain any number of "`?`" characters. Both `query` and `execute` accept a third argument, typically a tuple. When constructing the real query to execute, these functions replace the first "`?`" in the template with the first element of the tuple, the second "`?`" with the second element, and so on. If necessary, each tuple element will be quoted and escaped prior to substitution; this defeats the single most common injection vector for malicious data.

For example, given the following `Query` template:

    select * from user where first_name = ? and age > ?

And a tuple of this form:

    ("Boris" :: String, 37 :: Int)

The query to be executed will look like this after substitution:

    select * from user where first_name = 'Boris' and age > 37

If there is a mismatch between the number of "`?`" characters in your template and the number of elements in your tuple, a `FormatError` will be thrown.

Note that the substitution functions do not attempt to parse or validate your query. It's up to you to write syntactically valid SQL, and to ensure that each "`?`" in your query template is matched with the right tuple element.

### Type inference

Automated type inference means that you will often be able to avoid supplying explicit type signatures for the elements of a tuple. However, sometimes the compiler will not be able to infer your types. Consider a case where you write a numeric literal in a parameter tuple:

    query conn "select ? + ?" (40,2)

The above query will be rejected by the compiler, because it does not know the specific numeric types of the literals `40` and `2`. This is easily fixed:

    query conn "select ? + ?" (40 :: Double, 2 :: Double)

The same kind of problem can arise with string literals if you have the `OverloadedStrings` language extension enabled. Again, just use an explicit type signature if this happens.

Finally, remember that the compiler must be able to infer the type of a query's results as well as its parameters. We might like the following example to work:

    print =<< query_ conn "select 2 + 2"

Unfortunately, while a quick glance tells us that the result type should be a single row containing a single numeric column, the compiler has no way to infer what the types are. We can easily fix this by providing an explicit type annotation:

    xs <- query_ conn "select 2 + 2"
    print (xs :: [Only Int])

## Substituting a single parameter

Haskell lacks a single-element tuple type, so if you have just one value you want substituted into a query or a single-column result, what should you do?

The obvious approach would appear to be something like this:

    instance (ToField a) => ToRow a where
        ...

Unfortunately, this wreaks havoc with type inference, so we take a different tack. To represent a single value `val` as a parameter, write a singleton list `[val]`, use `Just` `val`, or use `Only` `val`.

Here's an example using a singleton list:

    execute conn "insert into users (first_name) values (?)"
                 ["Nuala"]

A row of n query results is represented using an n-tuple, so you should use `Only` to represent a single-column result.

## Representing a list of values

Suppose you want to write a query using an `IN` clause:

    select * from users where first_name in ('Anna', 'Boris', 'Carla')

In such cases, it's common for both the elements and length of the list after the `IN` keyword to vary from query to query.

To address this case, use the `In` type wrapper, and use a single "`?`" character to represent the list. Omit the parentheses around the list; these will be added for you.

Here's an example:

    query conn "select * from users where first_name in ?" $
          Only $ In ["Anna", "Boris", "Carla"]

If your `In`-wrapped list is empty, the string `"(null)"` will be substituted instead, to ensure that your clause remains syntactically valid.

## Modifying multiple rows at once

If you know that you have many rows of data to insert into a table, it is much more efficient to perform all the insertions in a single multi-row `INSERT` statement than individually.

The `executeMany` function is intended specifically for helping with multi-row `INSERT` and `UPDATE` statements. Its rules for query substitution are different than those for `execute`.

What `executeMany` searches for in your `Query` template is a single substring of the form:

    values (?,?,?)

The rules are as follows:

- The keyword `VALUES` is matched case insensitively.
- There must be no other "`?`" characters anywhere in your template.
- There must be one or more "`?`" in the parentheses.
- Extra white space is fine.

The last argument to `executeMany` is a list of parameter tuples. These will be substituted into the query where the `(?,?)` string appears, in a form suitable for use in a multi-row `INSERT` or `UPDATE`.

Here is an example:

    executeMany conn
      "insert into users (first_name,last_name) values (?,?)"
      [("Boris","Karloff"),("Ed","Wood")]

The query that will be executed here will look like this (reformatted for tidiness):

    insert into users (first_name,last_name) values
      ('Boris','Karloff'),('Ed','Wood')

## `RETURNING`: modifications that return results

PostgreSQL supports returning values from data manipulation statements such as `INSERT` and `UPDATE`. You can use these statements by using `query` instead of `execute`. For multi-tuple inserts, use `returning` instead of `executeMany`.

For example, were there an auto-incrementing `id` column and timestamp column `t` that defaulted to the present time for the `sales` table, then the following query would insert two new sales records and also return their new `id`s and timestamps.

    let q = "insert into sales (amount, label) values (?,?) returning id, t"
    xs :: [(Int, UTCTime)] <- query conn q (15,"Sawdust")
    ys :: [(Int, UTCTime)] <- returning conn q [(20,"Chips"),(300,"Wood")]

# Extracting results

The `query` and `query_` functions return a list of values in the `FromRow` typeclass. This class performs automatic extraction and type conversion of rows from a query result.

Here is a simple example of how to extract results:

    import qualified Data.Text as Text

    xs <- query_ conn "select name,age from users"
    forM_ xs $ \(name,age) ->
      putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)

Notice two important details about this code:

- The number of columns we ask for in the query template must exactly match the number of elements we specify in a row of the result tuple. If they do not match, a `ResultError` exception will be thrown.
- Sometimes, the compiler needs our help in specifying types. It can infer that `name` must be a `Text`, due to our use of the `unpack` function. However, we have to tell it the type of `age`, as it has no other information to determine the exact type.

## Handling null values

The type of a result tuple will look something like this:

    (Text, Int, Int)

Although SQL can accommodate `NULL` as a value for any of these types, Haskell cannot. If your result contains columns that may be `NULL`, be sure that you use `Maybe` in those positions of your tuple.

    (Text, Maybe Int, Int)

If `query` encounters a `NULL` in a row where the corresponding Haskell type is not `Maybe`, it will throw a `ResultError` exception.

## Type conversions

Conversion of SQL values to Haskell values is somewhat permissive. Here are the rules.

- For numeric types, any Haskell type that can accurately represent all values of the given PostgreSQL type is considered "compatible". For instance, you can always extract a PostgreSQL 16-bit `SMALLINT` column to a Haskell `Int`. The Haskell `Float` type can accurately represent a `SMALLINT`, so it is considered compatible with those types.
- A numeric compatibility check is based only on the type of a column, not on its values. For instance, a PostgreSQL 64-bit `BIGINT` column will be considered incompatible with a Haskell `Int16`, even if it contains the value `1`.
- If a numeric incompatibility is found, `query` will throw a `ResultError`.
- The `String` and `Text` types are assumed to be encoded as UTF-8. If you use some other encoding, decoding may fail or give wrong results. In such cases, write a `newtype` wrapper and a custom `Result` instance to handle your encoding.

# Types

data Connection Source #

#### Instances

Instances details

Eq Connection Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: Connection -> Connection -> Bool Source # (/=) :: Connection -> Connection -> Bool Source #

data Query Source #

A query string. This type is intended to make it difficult to construct a SQL query by concatenating string fragments, as that is an extremely common way to accidentally introduce SQL injection vulnerabilities into an application.

This type is an instance of `IsString`, so the easiest way to construct a query is to enable the `OverloadedStrings` language extension and then simply write the query in double quotes.

    {-# LANGUAGE OverloadedStrings #-}

    import Database.PostgreSQL.Simple

    q :: Query
    q = "select ?"

The underlying type is a `ByteString`, and literal Haskell strings that contain Unicode characters will be correctly transformed to UTF-8.

#### Instances

Instances details

Monoid Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods mempty :: Query Source # mappend :: Query -> Query -> Query Source # mconcat :: [Query] -> Query Source #
Semigroup Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods (<>) :: Query -> Query -> Query Source # sconcat :: NonEmpty Query -> Query Source # stimes :: Integral b => b -> Query -> Query Source #
IsString Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods fromString :: String -> Query Source #
Read Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods readsPrec :: Int -> ReadS Query Source # readList :: ReadS [Query] Source # readPrec :: ReadPrec Query Source # readListPrec :: ReadPrec [Query] Source #
Show Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods showsPrec :: Int -> Query -> ShowS Source # show :: Query -> String Source # showList :: [Query] -> ShowS Source #
Eq Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods (==) :: Query -> Query -> Bool Source # (/=) :: Query -> Query -> Bool Source #
Ord Query Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods compare :: Query -> Query -> Ordering Source # (<) :: Query -> Query -> Bool Source # (<=) :: Query -> Query -> Bool Source # (>) :: Query -> Query -> Bool Source # (>=) :: Query -> Query -> Bool Source # max :: Query -> Query -> Query Source # min :: Query -> Query -> Query Source #

class ToRow a Source #

A collection type that can be turned into a list of rendering `Action`s.

Instances should use the `toField` method of the `ToField` class to perform conversion of each element of the collection.

You can derive `ToRow` for your data type using GHC generics, like this:

    {-# LANGUAGE DeriveAnyClass #-}
    {-# LANGUAGE DeriveGeneric  #-}

    import GHC.Generics (Generic)
    import Database.PostgreSQL.Simple (ToRow)

    data User = User { name :: String, fileQuota :: Int }
      deriving (Generic, ToRow)

Note that this only works for product types (e.g. records) and does not support sum types or recursive types.

#### Instances

Instances details

ToRow () Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: () -> [Action] Source #
ToField a => ToRow (Only a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: Only a -> [Action] Source #
ToField a => ToRow [a] Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: [a] -> [Action] Source #
(ToRow a, ToRow b) => ToRow (a :. b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a :. b) -> [Action] Source #
(ToField a, ToField b) => ToRow (a, b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b) -> [Action] Source #
(ToField a, ToField b, ToField c) => ToRow (a, b, c) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d) => ToRow (a, b, c, d) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e) => ToRow (a, b, c, d, e) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f) => ToRow (a, b, c, d, e, f) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g) => ToRow (a, b, c, d, e, f, g) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h) => ToRow (a, b, c, d, e, f, g, h) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i) => ToRow (a, b, c, d, e, f, g, h, i) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j) => ToRow (a, b, c, d, e, f, g, h, i, j) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k) => ToRow (a, b, c, d, e, f, g, h, i, j, k) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o, ToField p) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o, ToField p, ToField q) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o, ToField p, ToField q, ToField r) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o, ToField p, ToField q, ToField r, ToField s) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s) -> [Action] Source #
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j, ToField k, ToField l, ToField m, ToField n, ToField o, ToField p, ToField q, ToField r, ToField s, ToField t) => ToRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t) -> [Action] Source #

class FromRow a Source #

A collection type that can be converted from a sequence of fields. Instances are provided for tuples up to 10 elements and lists of any length.

Note that instances can be defined outside of postgresql-simple, which is often useful. For example, here's an instance for a user-defined pair:

    data User = User { name :: String, fileQuota :: Int }

    instance FromRow User where
        fromRow = User <$> field <*> field

The number of calls to `field` must match the number of fields returned in a single row of the query result. Otherwise, a `ConversionFailed` exception will be thrown.

You can also derive `FromRow` for your data type using GHC generics, like this:

    {-# LANGUAGE DeriveAnyClass #-}
    {-# LANGUAGE DeriveGeneric  #-}

    import GHC.Generics (Generic)
    import Database.PostgreSQL.Simple (FromRow)

    data User = User { name :: String, fileQuota :: Int }
      deriving (Generic, FromRow)

Note that this only works for product types (e.g. records) and does not support sum types or recursive types.

Note that `field` evaluates its result to WHNF, so the caveats listed in mysql-simple and very early versions of postgresql-simple no longer apply. Instead, look at the caveats associated with user-defined implementations of `fromField`.

#### Instances

Instances details

FromField a => FromRow (Only a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Only a) Source #
FromField a => FromRow (Vector a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Vector a) Source #
FromField a => FromRow (Maybe (Only a)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (Only a)) Source #
FromField a => FromRow (Maybe (Vector a)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (Vector a)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r, FromField s) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s)) Source #
(FromField a, FromField b) => FromRow (Maybe (a, b)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r, FromField s, FromField t) => FromRow (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)) Source #
(FromField a, FromField b, FromField c) => FromRow (Maybe (a, b, c)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c)) Source #
(FromField a, FromField b, FromField c, FromField d) => FromRow (Maybe (a, b, c, d)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e) => FromRow (Maybe (a, b, c, d, e)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f) => FromRow (Maybe (a, b, c, d, e, f)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g) => FromRow (Maybe (a, b, c, d, e, f, g)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h) => FromRow (Maybe (a, b, c, d, e, f, g, h)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h)) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i) => FromRow (Maybe (a, b, c, d, e, f, g, h, i)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (a, b, c, d, e, f, g, h, i)) Source #
FromField a => FromRow (Maybe [a]) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe [a]) Source #
FromField a => FromRow [a] Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser [a] Source #
(FromRow a, FromRow b) => FromRow (a :. b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a :. b) Source #
(FromField a, FromField b) => FromRow (a, b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b) Source #
(FromField a, FromField b, FromField c) => FromRow (a, b, c) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c) Source #
(FromField a, FromField b, FromField c, FromField d) => FromRow (a, b, c, d) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e) => FromRow (a, b, c, d, e) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f) => FromRow (a, b, c, d, e, f) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g) => FromRow (a, b, c, d, e, f, g) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h) => FromRow (a, b, c, d, e, f, g, h) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i) => FromRow (a, b, c, d, e, f, g, h, i) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j) => FromRow (a, b, c, d, e, f, g, h, i, j) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k) => FromRow (a, b, c, d, e, f, g, h, i, j, k) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r, FromField s) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s) Source #
(FromField a, FromField b, FromField c, FromField d, FromField e, FromField f, FromField g, FromField h, FromField i, FromField j, FromField k, FromField l, FromField m, FromField n, FromField o, FromField p, FromField q, FromField r, FromField s, FromField t) => FromRow (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t) Source #

newtype In a Source #

Wrap a list of values for use in an `IN` clause. Replaces a single "`?`" character with a parenthesized list of rendered values.

Example:

    query c "select * from whatever where id in ?" (Only (In [3,4,5]))

Note that `In []` expands to `(null)`, which works as expected in the query above, but evaluates to the logical null value on every row instead of `TRUE`. This means that changing the query above to `... id NOT in ?` and supplying the empty list as the parameter returns zero rows, instead of all of them as one would expect.

Since postgresql doesn't seem to provide a syntax for actually specifying an empty list, which could solve this completely, there are two workarounds particularly worth mentioning, namely:

1. Use postgresql-simple's `Values` type instead, which can handle the empty case correctly. Note however that while specifying the postgresql type `"int4"` is mandatory in the empty case, specifying the haskell type `Values (Only Int)` would not normally be needed in realistic use cases.

       query c "select * from whatever where id not in ?"
               (Only (Values ["int4"] [] :: Values (Only Int)))

2. Use sql's `COALESCE` operator to turn a logical `null` into the correct boolean. Note however that the correct boolean depends on the use case:

       query c "select * from whatever where coalesce(id NOT in ?, TRUE)"
               (Only (In [] :: In [Int]))

       query c "select * from whatever where coalesce(id IN ?, FALSE)"
               (Only (In [] :: In [Int]))

   Note that at as of PostgreSQL 9.4, the query planner cannot see inside the `COALESCE` operator, so if you have an index on `id` then you probably don't want to write the last example with `COALESCE`, which would result in a table scan. There are further caveats if `id` can be null or you want null treated sensibly as a component of `IN` or `NOT IN`.

Constructors

In a |

#### Instances

Instances details

Functor In Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods fmap :: (a -> b) -> In a -> In b Source # (<$) :: a -> In b -> In a Source #
Read a => Read (In a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods readsPrec :: Int -> ReadS (In a) Source # readList :: ReadS [In a] Source # readPrec :: ReadPrec (In a) Source # readListPrec :: ReadPrec [In a] Source #
Show a => Show (In a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods showsPrec :: Int -> In a -> ShowS Source # show :: In a -> String Source # showList :: [In a] -> ShowS Source #
Eq a => Eq (In a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods (==) :: In a -> In a -> Bool Source # (/=) :: In a -> In a -> Bool Source #
Ord a => Ord (In a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods compare :: In a -> In a -> Ordering Source # (<) :: In a -> In a -> Bool Source # (<=) :: In a -> In a -> Bool Source # (>) :: In a -> In a -> Bool Source # (>=) :: In a -> In a -> Bool Source # max :: In a -> In a -> In a Source # min :: In a -> In a -> In a Source #
ToField a => ToField (In [a]) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToField Methods toField :: In [a] -> Action Source #

newtype Binary a Source #

Wrap binary data for use as a `bytea` value.

Constructors

Binary |

Fields

- fromBinary :: a

#### Instances

Instances details

Functor Binary Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods fmap :: (a -> b) -> Binary a -> Binary b Source # (<$) :: a -> Binary b -> Binary a Source #
Read a => Read (Binary a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods readsPrec :: Int -> ReadS (Binary a) Source # readList :: ReadS [Binary a] Source # readPrec :: ReadPrec (Binary a) Source # readListPrec :: ReadPrec [Binary a] Source #
Show a => Show (Binary a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods showsPrec :: Int -> Binary a -> ShowS Source # show :: Binary a -> String Source # showList :: [Binary a] -> ShowS Source #
Eq a => Eq (Binary a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods (==) :: Binary a -> Binary a -> Bool Source # (/=) :: Binary a -> Binary a -> Bool Source #
Ord a => Ord (Binary a) Source # |

Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

compare :: Binary a -> Binary a -> Ordering Source #

(<) :: Binary a -> Binary a -> Bool Source #

(<=) :: Binary a -> Binary a -> Bool Source #

(>) :: Binary a -> Binary a -> Bool Source #

(>=) :: Binary a -> Binary a -> Bool Source #

max :: Binary a -> Binary a -> Binary a Source #

min :: Binary a -> Binary a -> Binary a Source #

FromField (Binary ByteString) Source # | bytea
Instance details Defined in Database.PostgreSQL.Simple.FromField Methods fromField :: FieldParser (Binary ByteString) Source #
FromField (Binary ByteString) Source # | bytea
Instance details Defined in Database.PostgreSQL.Simple.FromField Methods fromField :: FieldParser (Binary ByteString) Source #
ToField (Binary ByteString) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToField Methods toField :: Binary ByteString -> Action Source #
ToField (Binary ByteString) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToField Methods toField :: Binary ByteString -> Action Source #

newtype Only a Source #

The 1-tuple type or single-value "collection".

This type is structurally equivalent to the `Identity` type, but its intent is more about serving as the anonymous 1-tuple type missing from Haskell for attaching typeclass instances.

Parameter usage example:

    encodeSomething (Only (42::Int))

Result usage example:

    xs <- decodeSomething
    forM_ xs $ \(Only id) -> {- ... -}

Constructors

Only |

Fields

- fromOnly :: a

#### Instances

Instances details

Functor Only |
Instance details Defined in Data.Tuple.Only Methods fmap :: (a -> b) -> Only a -> Only b Source # (<$) :: a -> Only b -> Only a Source #
NFData a => NFData (Only a) |
Instance details Defined in Data.Tuple.Only Methods rnf :: Only a -> () Source #
Data a => Data (Only a) |

Instance details

Defined in Data.Tuple.Only

Methods

gfoldl :: (forall d b. Data d => c (d -> b) -> d -> c b) -> (forall g. g -> c g) -> Only a -> c (Only a) Source #

gunfold :: (forall b r. Data b => c (b -> r) -> c r) -> (forall r. r -> c r) -> Constr -> c (Only a) Source #

toConstr :: Only a -> Constr Source #

dataTypeOf :: Only a -> DataType Source #

dataCast1 :: Typeable t => (forall d. Data d => c (t d)) -> Maybe (c (Only a)) Source #

dataCast2 :: Typeable t => (forall d e. (Data d, Data e) => c (t d e)) -> Maybe (c (Only a)) Source #

gmapT :: (forall b. Data b => b -> b) -> Only a -> Only a Source #

gmapQl :: (r -> r' -> r) -> r -> (forall d. Data d => d -> r') -> Only a -> r Source #

gmapQr :: forall r r'. (r' -> r -> r) -> r -> (forall d. Data d => d -> r') -> Only a -> r Source #

gmapQ :: (forall d. Data d => d -> u) -> Only a -> [u] Source #

gmapQi :: Int -> (forall d. Data d => d -> u) -> Only a -> u Source #

gmapM :: Monad m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) Source #

gmapMp :: MonadPlus m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) Source #

gmapMo :: MonadPlus m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) Source #

Generic (Only a) |

Instance details

Defined in Data.Tuple.Only

Associated Types

type Rep (Only a) |
Instance details Defined in Data.Tuple.Only type Rep (Only a) = D1 ('MetaData "Only" "Data.Tuple.Only" "Only-0.1-3GozSrXszIj1AVdRsJpLBH" 'True) (C1 ('MetaCons "Only" 'PrefixI 'True) (S1 ('MetaSel ('Just "fromOnly") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 a)))

Methods

from :: Only a -> Rep (Only a) x Source #

to :: Rep (Only a) x -> Only a Source #

Read a => Read (Only a) |
Instance details Defined in Data.Tuple.Only Methods readsPrec :: Int -> ReadS (Only a) Source # readList :: ReadS [Only a] Source # readPrec :: ReadPrec (Only a) Source # readListPrec :: ReadPrec [Only a] Source #
Show a => Show (Only a) |
Instance details Defined in Data.Tuple.Only Methods showsPrec :: Int -> Only a -> ShowS Source # show :: Only a -> String Source # showList :: [Only a] -> ShowS Source #
Eq a => Eq (Only a) |
Instance details Defined in Data.Tuple.Only Methods (==) :: Only a -> Only a -> Bool Source # (/=) :: Only a -> Only a -> Bool Source #
Ord a => Ord (Only a) |
Instance details Defined in Data.Tuple.Only Methods compare :: Only a -> Only a -> Ordering Source # (<) :: Only a -> Only a -> Bool Source # (<=) :: Only a -> Only a -> Bool Source # (>) :: Only a -> Only a -> Bool Source # (>=) :: Only a -> Only a -> Bool Source # max :: Only a -> Only a -> Only a Source # min :: Only a -> Only a -> Only a Source #
FromField a => FromRow (Only a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Only a) Source #
FromField a => FromRow (Maybe (Only a)) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (Maybe (Only a)) Source #
ToField a => ToRow (Only a) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: Only a -> [Action] Source #
type Rep (Only a) |
Instance details Defined in Data.Tuple.Only type Rep (Only a) = D1 ('MetaData "Only" "Data.Tuple.Only" "Only-0.1-3GozSrXszIj1AVdRsJpLBH" 'True) (C1 ('MetaCons "Only" 'PrefixI 'True) (S1 ('MetaSel ('Just "fromOnly") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 a)))

data h :. t infixr 3 Source #

A composite type to parse your custom data structures without having to define dummy newtype wrappers every time.

    instance FromRow MyData where ...

    instance FromRow MyData2 where ...

then I can do the following for free:

    res <- query' c "..."
    forM res $ \(MyData{..} :. MyData2{..}) -> do
      ....

Constructors

h :. t infixr 3 |

#### Instances

Instances details

(Read h, Read t) => Read (h :. t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods readsPrec :: Int -> ReadS (h :. t) Source # readList :: ReadS [h :. t] Source # readPrec :: ReadPrec (h :. t) Source # readListPrec :: ReadPrec [h :. t] Source #
(Show h, Show t) => Show (h :. t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods showsPrec :: Int -> (h :. t) -> ShowS Source # show :: (h :. t) -> String Source # showList :: [h :. t] -> ShowS Source #
(Eq h, Eq t) => Eq (h :. t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods (==) :: (h :. t) -> (h :. t) -> Bool Source # (/=) :: (h :. t) -> (h :. t) -> Bool Source #
(Ord h, Ord t) => Ord (h :. t) Source # |
Instance details Defined in Database.PostgreSQL.Simple.Types Methods compare :: (h :. t) -> (h :. t) -> Ordering Source # (<) :: (h :. t) -> (h :. t) -> Bool Source # (<=) :: (h :. t) -> (h :. t) -> Bool Source # (>) :: (h :. t) -> (h :. t) -> Bool Source # (>=) :: (h :. t) -> (h :. t) -> Bool Source # max :: (h :. t) -> (h :. t) -> h :. t Source # min :: (h :. t) -> (h :. t) -> h :. t Source #
(FromRow a, FromRow b) => FromRow (a :. b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromRow Methods fromRow :: RowParser (a :. b) Source #
(ToRow a, ToRow b) => ToRow (a :. b) Source # |
Instance details Defined in Database.PostgreSQL.Simple.ToRow Methods toRow :: (a :. b) -> [Action] Source #

## Exceptions

data SomePostgreSqlException Source #

Superclass for postgresql exceptions

Constructors

Exception e => SomePostgreSqlException e |

#### Instances

Instances details

Exception SomePostgreSqlException Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods toException :: SomePostgreSqlException -> SomeException Source # fromException :: SomeException -> Maybe SomePostgreSqlException Source # displayException :: SomePostgreSqlException -> String Source # backtraceDesired :: SomePostgreSqlException -> Bool Source #
Show SomePostgreSqlException Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods showsPrec :: Int -> SomePostgreSqlException -> ShowS Source # show :: SomePostgreSqlException -> String Source # showList :: [SomePostgreSqlException] -> ShowS Source #

data SqlError Source #

Constructors

SqlError |

Fields

- sqlState :: ByteString
- sqlExecStatus :: ExecStatus
- sqlErrorMsg :: ByteString
- sqlErrorDetail :: ByteString
- sqlErrorHint :: ByteString

#### Instances

Instances details

Exception SqlError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods toException :: SqlError -> SomeException Source # fromException :: SomeException -> Maybe SqlError Source # displayException :: SqlError -> String Source # backtraceDesired :: SqlError -> Bool Source #
Show SqlError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods showsPrec :: Int -> SqlError -> ShowS Source # show :: SqlError -> String Source # showList :: [SqlError] -> ShowS Source #
Eq SqlError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: SqlError -> SqlError -> Bool Source # (/=) :: SqlError -> SqlError -> Bool Source #

data ExecStatus Source #

Constructors

EmptyQuery | The string sent to the server was empty.
CommandOk | Successful completion of a command returning no data.
TuplesOk | Successful completion of a command returning data (such as a SELECT or SHOW).
CopyOut | Copy Out (from server) data transfer started.
CopyIn | Copy In (to server) data transfer started.
CopyBoth | Copy In/Out data transfer started.
BadResponse | The server's response was not understood.
NonfatalError | A nonfatal error (a notice or warning) occurred.
FatalError | A fatal error occurred.
SingleTuple | The `Result` contains a single result tuple from the current command. This status occurs only when single-row mode has been selected for the query.
PipelineSync | The `Result` represents a synchronization point in pipeline mode, requested by `pipelineSync`. This status occurs only when pipeline mode has been selected. Since: postgresql-libpq-0.11.0.0
PipelineAbort | The `Result` represents a pipeline that has received an error from the server. `getResult` must be called repeatedly, and each time it will return this status code until the end of the current pipeline, at which point it will return `PipelineSync` and normal processing can resume. Since: postgresql-libpq-0.11.0.0

#### Instances

Instances details

Enum ExecStatus |

Instance details

Defined in Database.PostgreSQL.LibPQ.Enums

Methods

succ :: ExecStatus -> ExecStatus Source #

pred :: ExecStatus -> ExecStatus Source #

toEnum :: Int -> ExecStatus Source #

fromEnum :: ExecStatus -> Int Source #

enumFrom :: ExecStatus -> [ExecStatus] Source #

enumFromThen :: ExecStatus -> ExecStatus -> [ExecStatus] Source #

enumFromTo :: ExecStatus -> ExecStatus -> [ExecStatus] Source #

enumFromThenTo :: ExecStatus -> ExecStatus -> ExecStatus -> [ExecStatus] Source #

Show ExecStatus |
Instance details Defined in Database.PostgreSQL.LibPQ.Enums Methods showsPrec :: Int -> ExecStatus -> ShowS Source # show :: ExecStatus -> String Source # showList :: [ExecStatus] -> ShowS Source #
Eq ExecStatus |
Instance details Defined in Database.PostgreSQL.LibPQ.Enums Methods (==) :: ExecStatus -> ExecStatus -> Bool Source # (/=) :: ExecStatus -> ExecStatus -> Bool Source #
FromCInt ExecStatus |
Instance details Defined in Database.PostgreSQL.LibPQ.Enums Methods fromCInt :: CInt -> Maybe ExecStatus
ToCInt ExecStatus |
Instance details Defined in Database.PostgreSQL.LibPQ.Enums Methods toCInt :: ExecStatus -> CInt

data FormatError Source #

Exception thrown if a `Query` could not be formatted correctly. This may occur if the number of '`?`' characters in the query string does not match the number of parameters provided.

Constructors

FormatError |

Fields

- fmtMessage :: String
- fmtQuery :: Query
- fmtParams :: [ByteString]

#### Instances

Instances details

Exception FormatError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods toException :: FormatError -> SomeException Source # fromException :: SomeException -> Maybe FormatError Source # displayException :: FormatError -> String Source # backtraceDesired :: FormatError -> Bool Source #
Show FormatError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods showsPrec :: Int -> FormatError -> ShowS Source # show :: FormatError -> String Source # showList :: [FormatError] -> ShowS Source #
Eq FormatError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: FormatError -> FormatError -> Bool Source # (/=) :: FormatError -> FormatError -> Bool Source #

data QueryError Source #

Exception thrown if `query` is used to perform an `INSERT`-like operation, or `execute` is used to perform a `SELECT`-like operation.

Constructors

QueryError |

Fields

- qeMessage :: String
- qeQuery :: Query

#### Instances

Instances details

Exception QueryError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods toException :: QueryError -> SomeException Source # fromException :: SomeException -> Maybe QueryError Source # displayException :: QueryError -> String Source # backtraceDesired :: QueryError -> Bool Source #
Show QueryError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods showsPrec :: Int -> QueryError -> ShowS Source # show :: QueryError -> String Source # showList :: [QueryError] -> ShowS Source #
Eq QueryError Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: QueryError -> QueryError -> Bool Source # (/=) :: QueryError -> QueryError -> Bool Source #

data ResultError Source #

Exception thrown if conversion from a SQL value to a Haskell value fails.

Constructors

Incompatible | The SQL and Haskell types are not compatible.

Fields

- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String

UnexpectedNull | A SQL `NULL` was encountered when the Haskell type did not permit it.

Fields

- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String

ConversionFailed | The SQL value could not be parsed, or could not be represented as a valid Haskell value, or an unexpected low-level error occurred (e.g. mismatch between metadata and actual data in a row).

Fields

- errSQLType :: String
- errSQLTableOid :: Maybe Oid
- errSQLField :: String
- errHaskellType :: String
- errMessage :: String

#### Instances

Instances details

Exception ResultError Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromField Methods toException :: ResultError -> SomeException Source # fromException :: SomeException -> Maybe ResultError Source # displayException :: ResultError -> String Source # backtraceDesired :: ResultError -> Bool Source #
Show ResultError Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromField Methods showsPrec :: Int -> ResultError -> ShowS Source # show :: ResultError -> String Source # showList :: [ResultError] -> ShowS Source #
Eq ResultError Source # |
Instance details Defined in Database.PostgreSQL.Simple.FromField Methods (==) :: ResultError -> ResultError -> Bool Source # (/=) :: ResultError -> ResultError -> Bool Source #

# Connection management

connectPostgreSQL :: ByteString -> IO Connection Source #

Attempt to make a connection based on a libpq connection string. See https://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNSTRING for more information. Also note that environment variables also affect parameters not provided, parameters provided as the empty string, and a few other things; see https://www.postgresql.org/docs/9.5/static/libpq-envars.html for details. Here is an example with some of the most commonly used parameters:

    host='db.somedomain.com' port=5432 ...

This attempts to connect to `db.somedomain.com:5432`. Omitting the port will normally default to 5432.

On systems that provide unix domain sockets, omitting the host parameter will cause libpq to attempt to connect via unix domain sockets. The default filesystem path to the socket is constructed from the port number and the `DEFAULT_PGSOCKET_DIR` constant defined in the `pg_config_manual.h` header file. Connecting via unix sockets tends to use the `peer` authentication method, which is very secure and does not require a password.

On Windows and other systems without unix domain sockets, omitting the host will default to `localhost`.

    ... dbname='postgres' user='postgres' password='secret \' \\ pw'

This attempts to connect to a database named `postgres` with user `postgres` and password `secret ' \ pw`. Backslash characters will have to be double-quoted in literal Haskell strings, of course. Omitting `dbname` and `user` will both default to the system username that the client process is running as.

Omitting `password` will default to an appropriate password found in the `pgpass` file, or no password at all if a matching line is not found. The path of the `pgpass` file may be specified by setting the `PGPASSFILE` environment variable. See https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html for more information regarding this file.

As all parameters are optional and the defaults are sensible, the empty connection string can be useful for development and exploratory use, assuming your system is set up appropriately.

On Unix, such a setup would typically consist of a local postgresql server listening on port 5432, as well as a system user, database user, and database sharing a common name, with permissions granted to the user on the database.

On Windows, in addition you will either need `pg_hba.conf` to specify the use of the `trust` authentication method for the connection, which may not be appropriate for multiuser or production machines, or you will need to use a `pgpass` file with the `password` or `md5` authentication methods.

See https://www.postgresql.org/docs/9.5/static/client-authentication.html for more information regarding the authentication process.

SSL/TLS will typically "just work" if your postgresql server supports or requires it. However, note that libpq is trivially vulnerable to a MITM attack without setting additional SSL connection parameters. In particular, `sslmode` needs to be set to `require`, `verify-ca`, or `verify-full` in order to perform certificate validation. When `sslmode` is `require`, then you will also need to specify a `sslrootcert` file, otherwise no validation of the server's identity will be performed. Client authentication via certificates is also possible via the `sslcert` and `sslkey` parameters. See https://www.postgresql.org/docs/9.5/static/libpq-ssl.html for detailed information regarding libpq and SSL.

close :: Connection -> IO () Source #

connect :: ConnectInfo -> IO Connection Source #

Connect with the given username to the given database. Will throw an exception if it cannot connect.

withConnect :: ConnectInfo -> (Connection -> IO c) -> IO c Source #

Memory bracket around `connect` and `close`.

Since: 0.6.5

data ConnectInfo Source #

Constructors

ConnectInfo |

Fields

- connectHost :: String
- connectPort :: Word16
- connectUser :: String
- connectPassword :: String
- connectDatabase :: String

#### Instances

Instances details

Generic ConnectInfo Source # |

Instance details

Defined in Database.PostgreSQL.Simple.Internal

Associated Types

type Rep ConnectInfo |

Instance details

Defined in Database.PostgreSQL.Simple.Internal

type Rep ConnectInfo = D1 ('MetaData "ConnectInfo" "Database.PostgreSQL.Simple.Internal" "postgresql-simple-0.7.0.1-2UIwxyVGztGAfJcoklGg9" 'False) (C1 ('MetaCons "ConnectInfo" 'PrefixI 'True) ((S1 ('MetaSel ('Just "connectHost") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: S1 ('MetaSel ('Just "connectPort") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Word16)) :*: (S1 ('MetaSel ('Just "connectUser") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: (S1 ('MetaSel ('Just "connectPassword") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: S1 ('MetaSel ('Just "connectDatabase") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String)))))

Methods

from :: ConnectInfo -> Rep ConnectInfo x Source #

to :: Rep ConnectInfo x -> ConnectInfo Source #

Read ConnectInfo Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods readsPrec :: Int -> ReadS ConnectInfo Source # readList :: ReadS [ConnectInfo] Source # readPrec :: ReadPrec ConnectInfo Source # readListPrec :: ReadPrec [ConnectInfo] Source #
Show ConnectInfo Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods showsPrec :: Int -> ConnectInfo -> ShowS Source # show :: ConnectInfo -> String Source # showList :: [ConnectInfo] -> ShowS Source #
Eq ConnectInfo Source # |
Instance details Defined in Database.PostgreSQL.Simple.Internal Methods (==) :: ConnectInfo -> ConnectInfo -> Bool Source # (/=) :: ConnectInfo -> ConnectInfo -> Bool Source #
type Rep ConnectInfo Source # |

Instance details

Defined in Database.PostgreSQL.Simple.Internal

type Rep ConnectInfo = D1 ('MetaData "ConnectInfo" "Database.PostgreSQL.Simple.Internal" "postgresql-simple-0.7.0.1-2UIwxyVGztGAfJcoklGg9" 'False) (C1 ('MetaCons "ConnectInfo" 'PrefixI 'True) ((S1 ('MetaSel ('Just "connectHost") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: S1 ('MetaSel ('Just "connectPort") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Word16)) :*: (S1 ('MetaSel ('Just "connectUser") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: (S1 ('MetaSel ('Just "connectPassword") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String) :*: S1 ('MetaSel ('Just "connectDatabase") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 String)))))

defaultConnectInfo :: ConnectInfo Source #

Default information for setting up a connection.

Defaults are as follows:

- Server on `localhost`
- Port on `5432`
- User `postgres`
- No password
- Database `postgres`

Use as in the following example:

    connect defaultConnectInfo { connectHost = "db.example.com" }

postgreSQLConnectionString :: ConnectInfo -> ByteString Source #

Turns a `ConnectInfo` data structure into a libpq connection string.

# Queries that return results

query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r] Source #

Perform a `SELECT` or other SQL query that is expected to return results. All results are retrieved and converted before this function returns.

When processing large results, this function will consume a lot of client-side memory. Consider using `fold` instead.

Exceptions that may be thrown:

- `FormatError`: the query string could not be formatted correctly.
- `QueryError`: the result contains no columns (i.e. you should be using `execute` instead of `query`).
- `ResultError`: result conversion failed.
- `SqlError`: the postgresql backend returned an error, e.g. a syntax or type error, or an incorrect table or column name.

query_ :: FromRow r => Connection -> Query -> IO [r] Source #

A version of `query` that does not perform query substitution.

## Queries taking parser as argument

queryWith :: ToRow q => RowParser r -> Connection -> Query -> q -> IO [r] Source #

A version of `query` taking parser as argument

queryWith_ :: RowParser r -> Connection -> Query -> IO [r] Source #

A version of `query_` taking parser as argument

# Queries that stream results

data FoldOptions Source #

Constructors

FoldOptions |

Fields

- fetchQuantity :: !FetchQuantity
- transactionMode :: !TransactionMode

data FetchQuantity Source #

Number of rows to fetch at a time. `Automatic` currently defaults to 256 rows, although it might be nice to make this more intelligent based on e.g. the average size of the rows.

Constructors

Automatic |
Fixed !Int |

defaultFoldOptions :: FoldOptions Source #

defaults to `Automatic`, and `TransactionMode` `ReadCommitted` `ReadOnly`

fold :: (FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #

Perform a `SELECT` or other SQL query that is expected to return results. Results are streamed incrementally from the server, and consumed via a left fold.

When dealing with small results, it may be simpler (and perhaps faster) to use `query` instead.

This fold is not strict. The stream consumer is responsible for forcing the evaluation of its result to avoid space leaks.

This is implemented using a database cursor. As such, this requires a transaction. This function will detect whether or not there is a transaction in progress, and will create a `ReadCommitted` `ReadOnly` transaction if needed. The cursor is given a unique temporary name, so the consumer may itself call fold.

Exceptions that may be thrown:

- `FormatError`: the query string could not be formatted correctly.
- `QueryError`: the result contains no columns (i.e. you should be using `execute` instead of `query`).
- `ResultError`: result conversion failed.
- `SqlError`: the postgresql backend returned an error, e.g. a syntax or type error, or an incorrect table or column name.

foldWithOptions :: (FromRow row, ToRow params) => FoldOptions -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #

The same as `fold`, but this provides a bit more control over lower-level details. Currently, the number of rows fetched per round-trip to the server and the transaction mode may be adjusted accordingly. If the connection is already in a transaction, then the existing transaction is used and thus the `transactionMode` option is ignored.

fold_ Source #

Arguments

:: FromRow r |
=> Connection |
-> Query | Query.
-> a | Initial state for result consumer.
-> (a -> r -> IO a) | Result consumer.
-> IO a |

A version of `fold` that does not perform query substitution.

foldWithOptions_ Source #

Arguments

:: FromRow r |
=> FoldOptions |
-> Connection |
-> Query | Query.
-> a | Initial state for result consumer.
-> (a -> r -> IO a) | Result consumer.
-> IO a |

forEach Source #

Arguments

:: (ToRow q, FromRow r) |
=> Connection |
-> Query | Query template.
-> q | Query parameters.
-> (r -> IO ()) | Result consumer.
-> IO () |

A version of `fold` that does not transform a state value.

forEach_ Source #

Arguments

:: FromRow r |
=> Connection |
-> Query | Query template.
-> (r -> IO ()) | Result consumer.
-> IO () |

A version of `forEach` that does not perform query substitution.

returning :: (ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r] Source #

Execute `INSERT ... RETURNING`, `UPDATE ... RETURNING`, or other SQL query that accepts multi-row input and is expected to return results. Note that it is possible to write ``query` conn "INSERT ... RETURNING ..." ...` in cases where you are only inserting a single row, and do not need functionality analogous to `executeMany`.

If the list of parameters is empty, this function will simply return `[]` without issuing the query to the backend. If this is not desired, consider using the `Values` constructor instead.

Throws `FormatError` if the query could not be formatted correctly.

## Queries that stream results taking a parser as an argument

foldWith :: ToRow params => RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #

A version of `fold` taking a parser as an argument

foldWithOptionsAndParser :: ToRow params => FoldOptions -> RowParser row -> Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #

A version of `foldWithOptions` taking a parser as an argument

foldWith_ :: RowParser r -> Connection -> Query -> a -> (a -> r -> IO a) -> IO a Source #

A version of `fold_` taking a parser as an argument

foldWithOptionsAndParser_ Source #

Arguments

:: FoldOptions |
-> RowParser r |
-> Connection |
-> Query | Query.
-> a | Initial state for result consumer.
-> (a -> r -> IO a) | Result consumer.
-> IO a |

A version of `foldWithOptions_` taking a parser as an argument

forEachWith :: ToRow q => RowParser r -> Connection -> Query -> q -> (r -> IO ()) -> IO () Source #

A version of `forEach` taking a parser as an argument

forEachWith_ :: RowParser r -> Connection -> Query -> (r -> IO ()) -> IO () Source #

returningWith :: ToRow q => RowParser r -> Connection -> Query -> [q] -> IO [r] Source #

A version of `returning` taking parser as argument

# Statements that do not return results

execute :: ToRow q => Connection -> Query -> q -> IO Int64 Source #

Execute an `INSERT`, `UPDATE`, or other SQL query that is not expected to return results.

Returns the number of rows affected.

Throws `FormatError` if the query could not be formatted correctly, or a `SqlError` exception if the backend returns an error.

execute_ :: Connection -> Query -> IO Int64 Source #

A version of `execute` that does not perform query substitution.

executeMany :: ToRow q => Connection -> Query -> [q] -> IO Int64 Source #

Execute a multi-row `INSERT`, `UPDATE`, or other SQL query that is not expected to return results.

Returns the number of rows affected. If the list of parameters is empty, this function will simply return 0 without issuing the query to the backend. If this is not desired, consider using the `Values` constructor instead.

Throws `FormatError` if the query could not be formatted correctly, or a `SqlError` exception if the backend returns an error.

For example, here's a command that inserts two rows into a table with two columns:

    executeMany c [sql|
        INSERT INTO sometable VALUES (?,?)
     |] [(1, "hello"),(2, "world")]

Here's an canonical example of a multi-row update command:

    executeMany c [sql|
        UPDATE sometable
           SET y = upd.y
          FROM (VALUES (?,?)) as upd(x,y)
         WHERE sometable.x = upd.x
     |] [(1, "hello"),(2, "world")]

# Transaction handling

withTransaction :: Connection -> IO a -> IO a Source #

Execute an action inside a SQL transaction.

This function initiates a transaction with a "`begin transaction`" statement, then executes the supplied action. If the action succeeds, the transaction will be completed with `commit` before this function returns.

If the action throws any kind of exception (not just a PostgreSQL-related exception), the transaction will be rolled back using `rollback`, then the exception will be rethrown.

For nesting transactions, see `withSavepoint`.

withSavepoint :: Connection -> IO a -> IO a Source #

Create a savepoint, and roll back to it if an error occurs. This may only be used inside of a transaction, and provides a sort of "nested transaction".

See https://www.postgresql.org/docs/9.5/static/sql-savepoint.html

begin :: Connection -> IO () Source #

Begin a transaction.

commit :: Connection -> IO () Source #

Commit a transaction.

rollback :: Connection -> IO () Source #

Rollback a transaction.

# Helper functions

formatMany :: ToRow q => Connection -> Query -> [q] -> IO ByteString Source #

Format a query string with a variable number of rows.

This function is exposed to help with debugging and logging. Do not use it to prepare queries for execution.

The query string must contain exactly one substitution group, identified by the SQL keyword "`VALUES`" (case insensitive) followed by an "`(`" character, a series of one or more "`?`" characters separated by commas, and a "`)`" character. White space in a substitution group is permitted.

Throws `FormatError` if the query string could not be formatted correctly.

formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString Source #

Format a query string.

This function is exposed to help with debugging and logging. Do not use it to prepare queries for execution.

String parameters are escaped according to the character set in use on the `Connection`.

Throws `FormatError` if the query string could not be formatted correctly.