Database.PostgreSQL.Simple.FromField

The FromField typeclass, for converting a single value in a row returned by a SQL query into a more useful Haskell representation. Note that each instance of FromField is documented by a list of compatible postgresql types.

A Haskell numeric type is considered to be compatible with all PostgreSQL numeric types that are less accurate than it. For instance, the Haskell Double type is compatible with the PostgreSQL's 32-bit int type because it can represent a int exactly. On the other hand, since a Double might lose precision if representing PostgreSQL's 64-bit bigint, the two are not considered compatible.

Note that the Float and Double instances use attoparsec's double conversion routine, which sacrifices some accuracy for speed. If you need accuracy, consider first converting data to a Scientific or Rational type, and then converting to a floating-point type. If you are defining your own FromRow instances, this can be achieved simply by fromRational <$> field, although this idiom is additionally compatible with PostgreSQL's int8 and numeric types. If this is unacceptable, you may find fieldWith useful.

Also note that while converting to a Double through the Scientific type is likely somewhat faster than converting through the Rational type, the Scientific type has no way to represent NaN and ±Infinity values. Thus, if you need precision conversion of regular floating point values and the possibility of receiving these special values from the backend, stick with Rational.

Because FromField is a typeclass, one may provide conversions to additional Haskell types without modifying pg. This is particularly useful for supporting PostgreSQL types that pg does not support out-of-box. Here's an example of what such an instance might look like for a UUID type that implements the Read class:

import Data.UUID ( UUID )
import Database.PostgreSQL.Simple.FromField
       ( FromField (fromField) , typeOid, returnError, ResultError (..) )
import Database.PostgreSQL.Simple.TypeInfo.Static (typoid, uuid)
import qualified Data.ByteString.Char8 as B

instance FromField UUID where
   fromField f mdata =
      if typeOid f /= typoid uuid
        then returnError Incompatible f ""
        else case B.unpack `fmap` mdata of
               Nothing  -> returnError UnexpectedNull f ""
               Just dat ->
                  case [ x | (x,t) <- reads dat, ("","") <- lex t ] of
                    [x] -> return x
                    _   -> returnError ConversionFailed f dat

Note that because PostgreSQL's uuid type is built into postgres and is not provided by an extension, the typeOid of uuid does not change and thus we can examine it directly. One could hard-code the type oid, or obtain it by other means, but in this case we simply pull it out of the static table provided by pg.

On the other hand if the type is provided by an extension, such as PostGIS or hstore, then the typeOid is not stable and can vary from database to database. In this case it is recommended that FromField instances use typename instead.

Documentation

class FromField a where

A type that may be converted from a SQL type.

Minimal complete definition

fromField

Methods

fromField :: FieldParser a

Convert a SQL value to a Haskell value.

Returns a list of exceptions if the conversion fails. In the case of library instances, this will usually be a single ResultError, but may be a UnicodeException.

Note that retaining any reference to the Field argument causes the entire LibPQ.Result to be retained. Thus, implementations of fromField should return results that do not refer to this value after the result have been evaluated to WHNF.

Note that as of postgresql-simple-0.4.0.0, the ByteString value has already been copied out of the LibPQ.Result before it has been passed to fromField. This is because for short strings, it's cheaper to copy the string than to set up a finalizer.

Instances

FromField Bool

bool

Methods

fromField :: FieldParser Bool

FromField Char

"char"

Methods

fromField :: FieldParser Char

FromField Double

int2, int4, float4, float8 (Uses attoparsec's double routine, for better accuracy convert to Scientific or Rational first)

Methods

fromField :: FieldParser Double

FromField Float

int2, float4 (Uses attoparsec's double routine, for better accuracy convert to Scientific or Rational first)

Methods

fromField :: FieldParser Float

FromField Int

int2, int4, and if compiled as 64-bit code, int8 as well. This library was compiled as 64-bit code.

Methods

fromField :: FieldParser Int

FromField Int16

int2

Methods

fromField :: FieldParser Int16

FromField Int32

int2, int4

Methods

fromField :: FieldParser Int32

FromField Int64

int2, int4, int8

Methods

fromField :: FieldParser Int64

FromField Integer

int2, int4, int8

Methods

fromField :: FieldParser Integer

FromField ()

void

Methods

fromField :: FieldParser ()

FromField ByteString

bytea, name, text, "char", bpchar, varchar, unknown

Methods

fromField :: FieldParser ByteString

FromField ByteString

bytea, name, text, "char", bpchar, varchar, unknown

Methods

fromField :: FieldParser ByteString

FromField Scientific

int2, int4, int8, float4, float8, numeric

Methods

fromField :: FieldParser Scientific

FromField Text

name, text, "char", bpchar, varchar

Methods

fromField :: FieldParser Text

FromField UTCTime

timestamptz

Methods

fromField :: FieldParser UTCTime

FromField Value

json

Methods

fromField :: FieldParser Value

FromField Text

name, text, "char", bpchar, varchar

Methods

fromField :: FieldParser Text

FromField Oid

oid

Methods

fromField :: FieldParser Oid

FromField LocalTime

timestamp

Methods

fromField :: FieldParser LocalTime

FromField ZonedTime

timestamptz

Methods

fromField :: FieldParser ZonedTime

FromField TimeOfDay

time

Methods

fromField :: FieldParser TimeOfDay

FromField Day

date

Methods

fromField :: FieldParser Day

FromField UUID

uuid

Methods

fromField :: FieldParser UUID

FromField Date

date

Methods

fromField :: FieldParser Date

FromField ZonedTimestamp

timestamptz

Methods

fromField :: FieldParser ZonedTimestamp

FromField UTCTimestamp

timestamptz

Methods

fromField :: FieldParser UTCTimestamp

FromField LocalTimestamp

timestamp

Methods

fromField :: FieldParser LocalTimestamp

FromField Null

compatible with any data type, but the value must be null

Methods

fromField :: FieldParser Null

FromField HStoreMap

Methods

fromField :: FieldParser HStoreMap

FromField HStoreList

hstore

Methods

fromField :: FieldParser HStoreList

FromField [Char]

name, text, "char", bpchar, varchar

Methods

fromField :: FieldParser [Char]

FromField a => FromField (Maybe a)

For dealing with null values. Compatible with any postgresql type compatible with type a. Note that the type is not checked if the value is null, although it is inadvisable to rely on this behavior.

Methods

fromField :: FieldParser (Maybe a)

FromField (Ratio Integer)

int2, int4, int8, float4, float8, numeric

Methods

fromField :: FieldParser (Ratio Integer)

FromField a => FromField (IORef a)

Compatible with the same set of types as a. Note that modifying the IORef does not have any effects outside the local process on the local machine.

Methods

fromField :: FieldParser (IORef a)

FromField a => FromField (MVar a)

Compatible with the same set of types as a. Note that modifying the MVar does not have any effects outside the local process on the local machine.

Methods

fromField :: FieldParser (MVar a)

FromField (CI Text)

citext

Methods

fromField :: FieldParser (CI Text)

FromField (CI Text)

citext

Methods

fromField :: FieldParser (CI Text)

(FromField a, Typeable * a) => FromField (Vector a)

Methods

fromField :: FieldParser (Vector a)

(FromField a, Typeable * a) => FromField (IOVector a)

Methods

fromField :: FieldParser (IOVector a)

(FromField a, Typeable * a) => FromField (PGArray a)

any postgresql array whose elements are compatible with type a

Methods

fromField :: FieldParser (PGArray a)

FromField (Binary ByteString)

bytea

Methods

fromField :: FieldParser (Binary ByteString)

FromField (Binary ByteString)

bytea

Methods

fromField :: FieldParser (Binary ByteString)

(FromField a, Typeable * a) => FromField (PGRange a)

Methods

fromField :: FieldParser (PGRange a)

(FromField a, FromField b) => FromField (Either a b)

Compatible with both types. Conversions to type b are preferred, the conversion to type a will be tried after the Right conversion fails.

Methods

fromField :: FieldParser (Either a b)

type FieldParser a = Field -> Maybe ByteString -> Conversion a

data Conversion a

Instances

Monad Conversion

Methods

(>>=) :: Conversion a -> (a -> Conversion b) -> Conversion b

(>>) :: Conversion a -> Conversion b -> Conversion b

return :: a -> Conversion a

fail :: String -> Conversion a

Functor Conversion

Methods

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

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

Applicative Conversion

Methods

pure :: a -> Conversion a

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

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

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

Alternative Conversion

Methods

empty :: Conversion a

(<|>) :: Conversion a -> Conversion a -> Conversion a

some :: Conversion a -> Conversion [a]

many :: Conversion a -> Conversion [a]

MonadPlus Conversion

Methods

mzero :: Conversion a

mplus :: Conversion a -> Conversion a -> Conversion a

runConversion :: Conversion a -> Connection -> IO (Ok a)

conversionMap :: (Ok a -> Ok b) -> Conversion a -> Conversion b

conversionError :: Exception err => err -> Conversion a

data ResultError

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

Eq ResultError

Methods

(==) :: ResultError -> ResultError -> Bool

(/=) :: ResultError -> ResultError -> Bool

Show ResultError

Methods

showsPrec :: Int -> ResultError -> ShowS

show :: ResultError -> String

showList :: [ResultError] -> ShowS

Exception ResultError

Methods

toException :: ResultError -> SomeException

fromException :: SomeException -> Maybe ResultError

displayException :: ResultError -> String

returnError :: forall a err. (Typeable a, Exception err) => (String -> Maybe Oid -> String -> String -> String -> err) -> Field -> String -> Conversion a

Given one of the constructors from ResultError, the field, and an errMessage, this fills in the other fields in the exception value and returns it in a 'Left . SomeException' constructor.

data Field

A Field represents metadata about a particular field

You don't particularly want to retain these structures for a long period of time, as they will retain the entire query result, not just the field metadata

typename :: Field -> Conversion ByteString

Returns the data type name. This is the preferred way of identifying types that do not have a stable type oid, such as types provided by extensions to PostgreSQL.

More concretely, it returns the typname column associated with the type oid in the pg_type table. First, pg will check the built-in, static table. If the type oid is not there, pg will check a per-connection cache, and then finally query the database's meta-schema.

data TypeInfo

A structure representing some of the metadata regarding a PostgreSQL type, mostly taken from the pg_type table.

Constructors

Basic

Fields

• typoid :: !Oid
• typcategory :: !Char
• typdelim :: !Char
• typname :: !ByteString

Array

Fields

• typoid :: !Oid
• typcategory :: !Char
• typdelim :: !Char
• typname :: !ByteString
• typelem :: !TypeInfo

Range

Fields

• typoid :: !Oid
• typcategory :: !Char
• typdelim :: !Char
• typname :: !ByteString
• rngsubtype :: !TypeInfo

Composite

Fields

• typoid :: !Oid
• typcategory :: !Char
• typdelim :: !Char
• typname :: !ByteString
• typrelid :: !Oid
• attributes :: !(Vector Attribute)

Instances

Show TypeInfo

Methods

showsPrec :: Int -> TypeInfo -> ShowS

show :: TypeInfo -> String

showList :: [TypeInfo] -> ShowS

data Attribute

Constructors

Attribute

Fields

• attname :: !ByteString
• atttype :: !TypeInfo

Instances

Show Attribute

Methods

showsPrec :: Int -> Attribute -> ShowS

show :: Attribute -> String

showList :: [Attribute] -> ShowS

typeInfo :: Field -> Conversion TypeInfo

typeInfoByOid :: Oid -> Conversion TypeInfo

name :: Field -> Maybe ByteString

Returns the name of the column. This is often determined by a table definition, but it can be set using an as clause.

tableOid :: Field -> Maybe Oid

Returns the name of the object id of the table associated with the column, if any. Returns Nothing when there is no such table; for example a computed column does not have a table associated with it. Analogous to libpq's PQftable.

tableColumn :: Field -> Int

If the column has a table associated with it, this returns the number off the associated table column. Numbering starts from 0. Analogous to libpq's PQftablecol.

format :: Field -> Format

This returns whether the data was returned in a binary or textual format. Analogous to libpq's PQfformat.

typeOid :: Field -> Oid

This returns the type oid associated with the column. Analogous to libpq's PQftype.

newtype Oid :: *

Constructors

Oid CUInt

Instances

Eq Oid

Methods

(==) :: Oid -> Oid -> Bool

(/=) :: Oid -> Oid -> Bool

Ord Oid

Methods

compare :: Oid -> Oid -> Ordering

(<) :: Oid -> Oid -> Bool

(<=) :: Oid -> Oid -> Bool

(>) :: Oid -> Oid -> Bool

(>=) :: Oid -> Oid -> Bool

max :: Oid -> Oid -> Oid

min :: Oid -> Oid -> Oid

Read Oid

Methods

readsPrec :: Int -> ReadS Oid

readList :: ReadS [Oid]

readPrec :: ReadPrec Oid

readListPrec :: ReadPrec [Oid]

Show Oid

Methods

showsPrec :: Int -> Oid -> ShowS

show :: Oid -> String

showList :: [Oid] -> ShowS

Storable Oid

Methods

sizeOf :: Oid -> Int

alignment :: Oid -> Int

peekElemOff :: Ptr Oid -> Int -> IO Oid

pokeElemOff :: Ptr Oid -> Int -> Oid -> IO ()

peekByteOff :: Ptr b -> Int -> IO Oid

pokeByteOff :: Ptr b -> Int -> Oid -> IO ()

peek :: Ptr Oid -> IO Oid

poke :: Ptr Oid -> Oid -> IO ()

FromField Oid

oid

Methods

fromField :: FieldParser Oid

ToField Oid

Methods

toField :: Oid -> Action

data Format :: *

Constructors

Text

Binary

Instances

Enum Format

Methods

succ :: Format -> Format

pred :: Format -> Format

toEnum :: Int -> Format

fromEnum :: Format -> Int

enumFrom :: Format -> [Format]

enumFromThen :: Format -> Format -> [Format]

enumFromTo :: Format -> Format -> [Format]

enumFromThenTo :: Format -> Format -> Format -> [Format]

Eq Format

Methods

(==) :: Format -> Format -> Bool

(/=) :: Format -> Format -> Bool

Ord Format

Methods

compare :: Format -> Format -> Ordering

(<) :: Format -> Format -> Bool

(<=) :: Format -> Format -> Bool

(>) :: Format -> Format -> Bool

(>=) :: Format -> Format -> Bool

max :: Format -> Format -> Format

min :: Format -> Format -> Format

Show Format

Methods

showsPrec :: Int -> Format -> ShowS

show :: Format -> String

showList :: [Format] -> ShowS

pgArrayFieldParser :: Typeable a => FieldParser a -> FieldParser (PGArray a)

optionalField :: FieldParser a -> FieldParser (Maybe a)

For dealing with SQL null values outside of the FromField class. Alternatively, one could use optional, but that also turns type and conversion errors into Nothing, whereas this is more specific and turns only null values into Nothing.

fromJSONField :: (FromJSON a, Typeable a) => FieldParser a

Parse a field to a JSON Value and convert that into a Haskell value using fromJSON.

This can be used as the default implementation for the fromField method for Haskell types that have a JSON representation in PostgreSQL.

The Typeable constraint is required to show more informative error messages when parsing fails.

Note that fromJSONField :: FieldParser (Maybe Foo) will return Nothing on the json null value, and return an exception on SQL null value. Alternatively, one could write optionalField fromJSONField that will return Nothing on SQL null, and otherwise will call fromJSONField :: FieldParser Foo and then return Just the result value, or return its exception. If one would like to return Nothing on both the SQL null and json null values, one way to do it would be to write \f mv -> join <$> optionalField fromJSONField f mv