1{-# LANGUAGE BangPatterns, DeriveDataTypeable, OverloadedStrings #-}
2{-# LANGUAGE DoAndIfThenElse, RecordWildCards, NamedFieldPuns #-}
3------------------------------------------------------------------------------
5-- Module: Database.PostgreSQL.Simple
6-- Copyright: (c) 2011 MailRank, Inc.
7-- (c) 2011 Leon P Smith
9-- Maintainer: Leon P Smith <leon@melding-monads.com>
10-- Stability: experimental
11-- Portability: portable
13-- A mid-level client library for the PostgreSQL database, aimed at ease of
14-- use and high performance.
16------------------------------------------------------------------------------
18module Database.PostgreSQL.Simple
26 -- ** Parameter substitution
32 -- ** Substituting a single parameter
35 -- ** Representing a list of values
38 -- ** Modifying multiple rows at once
41 -- * Extracting results
44 -- ** Handling null values
47 -- ** Type conversions
59 , FormatError(fmtMessage, fmtQuery, fmtParams)
60 , QueryError(qeMessage, qeQuery)
61 , ResultError(errSQLType, errHaskellType, errMessage)
62 -- * Connection management
64 , Base.connectPostgreSQL
65 , Base.postgreSQLConnectionString
66 , Base.defaultConnectInfo
68 -- * Queries that return results
71 -- * Queries that stream results
81 -- * Statements that do not return results
86 -- * Transaction handling
91 , defaultTransactionMode
92 , defaultIsolationLevel
93 , defaultReadWriteMode
94 , withTransactionLevel
102 -- * Helper functions
107import Blaze.ByteString.Builder (Builder, fromByteString, toByteString)
108import Blaze.ByteString.Builder.Char8 (fromChar)
109import Control.Applicative ((<$>), pure)
110import Control.Concurrent.MVar
111import Control.Exception (Exception, onException, throw, throwIO, finally)
112import Control.Monad (foldM)
113import Control.Monad.Fix (fix)
114import Data.ByteString (ByteString)
116import Data.Int (Int64)
117import qualified Data.IntMap as IntMap
118import Data.List (intersperse)
119import Data.Monoid (mappend, mconcat)
120import Data.Typeable (Typeable)
121import Database.PostgreSQL.Simple.BuiltinTypes (oid2builtin, builtin2typname)
122import Database.PostgreSQL.Simple.Compat (mask)
123import Database.PostgreSQL.Simple.Param (Action(..), inQuotes)
124import Database.PostgreSQL.Simple.QueryParams (QueryParams(..))
125import Database.PostgreSQL.Simple.Result (ResultError(..))
126import Database.PostgreSQL.Simple.QueryResults (QueryResults(..))
127import Database.PostgreSQL.Simple.Types (Binary(..), In(..), Only(..), Query(..))
128import Database.PostgreSQL.Simple.Internal as Base
129import qualified Database.PostgreSQL.LibPQ as PQ
130import Text.Regex.PCRE.Light (compile, caseless, match)
131import qualified Data.ByteString.Char8 as B
133-- | Exception thrown if a 'Query' could not be formatted correctly.
134-- This may occur if the number of \'@?@\' characters in the query
135-- string does not match the number of parameters provided.
136data FormatError = FormatError {
139 , fmtParams :: [ByteString]
140 } deriving (Eq, Show, Typeable)
142instance Exception FormatError
144-- | Exception thrown if 'query' is used to perform an @INSERT@-like
145-- operation, or 'execute' is used to perform a @SELECT@-like operation.
146data QueryError = QueryError {
149 } deriving (Eq, Show, Typeable)
151instance Exception QueryError
153-- | Format a query string.
155-- This function is exposed to help with debugging and logging. Do not
156-- use it to prepare queries for execution.
158-- String parameters are escaped according to the character set in use
159-- on the 'Connection'.
161-- Throws 'FormatError' if the query string could not be formatted
163formatQuery :: QueryParams q => Connection -> Query -> q -> IO ByteString
164formatQuery conn q@(Query template) qs
165 | null xs && '?' `B.notElem` template = return template
166 | otherwise = toByteString <$> buildQuery conn q template xs
167 where xs = renderParams qs
169-- | Format a query string with a variable number of rows.
171-- This function is exposed to help with debugging and logging. Do not
172-- use it to prepare queries for execution.
174-- The query string must contain exactly one substitution group,
175-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
176-- followed by an \"@(@\" character, a series of one or more \"@?@\"
177-- characters separated by commas, and a \"@)@\" character. White
178-- space in a substitution group is permitted.
180-- Throws 'FormatError' if the query string could not be formatted
182formatMany :: (QueryParams q) => Connection -> Query -> [q] -> IO ByteString
183formatMany _ q [] = fmtError "no rows supplied" q []
184formatMany conn q@(Query template) qs = do
185 case match re template [] of
186 Just [_,before,qbits,after] -> do
187 bs <- mapM (buildQuery conn q qbits . renderParams) qs
188 return . toByteString . mconcat $ fromByteString before :
189 intersperse (fromChar ',') bs ++
190 [fromByteString after]
193 re = compile "^([^?]+\\bvalues\\s*)\
194 \(\\(\\s*[?](?:\\s*,\\s*[?])*\\s*\\))\
198escapeStringConn conn s = withConnection conn $ \c -> do
199 PQ.escapeStringConn c s
201buildQuery :: Connection -> Query -> ByteString -> [Action] -> IO Builder
202buildQuery conn q template xs = zipParams (split template) <$> mapM sub xs
203 where quote = inQuotes . fromByteString . maybe undefined id
204 sub (Plain b) = pure b
205 sub (Escape s) = quote <$> escapeStringConn conn s
206 sub (Many ys) = mconcat <$> mapM sub ys
207 split s = fromByteString h : if B.null t then [] else split (B.tail t)
208 where (h,t) = B.break (=='?') s
209 zipParams (t:ts) (p:ps) = t `mappend` p `mappend` zipParams ts ps
211 zipParams _ _ = fmtError (show (B.count '?' template) ++
212 " '?' characters, but " ++
213 show (length xs) ++ " parameters") q xs
215-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not
216-- expected to return results.
218-- Returns the number of rows affected.
220-- Throws 'FormatError' if the query could not be formatted correctly.
221execute :: (QueryParams q) => Connection -> Query -> q -> IO Int64
222execute conn template qs = do
223 result <- exec conn =<< formatQuery conn template qs
224 finishExecute conn template result
226-- | A version of 'execute' that does not perform query substitution.
227execute_ :: Connection -> Query -> IO Int64
228execute_ conn q@(Query stmt) = do
229 result <- exec conn stmt
230 finishExecute conn q result
232-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not
233-- expected to return results.
235-- Returns the number of rows affected.
237-- Throws 'FormatError' if the query could not be formatted correctly.
238executeMany :: (QueryParams q) => Connection -> Query -> [q] -> IO Int64
239executeMany _ _ [] = return 0
240executeMany conn q qs = do
241 result <- exec conn =<< formatMany conn q qs
242 finishExecute conn q result
244finishExecute :: Connection -> Query -> PQ.Result -> IO Int64
245finishExecute conn q result = do
246 status <- PQ.resultStatus result
249 ncols <- PQ.nfields result
251 then throwIO $ QueryError ("execute resulted in " ++ show ncols ++
254 nstr <- PQ.cmdTuples result
255 return $ case nstr of
256 Nothing -> 0 -- is this appropriate?
257 Just str -> toInteger str
259 ncols <- PQ.nfields result
260 throwIO $ QueryError ("execute resulted in " ++ show ncols ++
262 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
263 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
265 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
266 statusmsg <- PQ.resStatus status
267 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
268 throwIO $ SqlError { sqlState = state
269 , sqlNativeError = fromEnum status
270 , sqlErrorMsg = B.concat [ "execute: ", statusmsg
273 toInteger str = B.foldl' delta 0 str
276 if '0' <= c && c <= '9'
277 then 10 * acc + fromIntegral (ord c - ord '0')
278 else error ("finishExecute: not an int: " ++ B.unpack str)
282-- | Perform a @SELECT@ or other SQL query that is expected to return
283-- results. All results are retrieved and converted before this
286-- When processing large results, this function will consume a lot of
287-- client-side memory. Consider using 'fold' instead.
289-- Exceptions that may be thrown:
291-- * 'FormatError': the query string could not be formatted correctly.
293-- * 'QueryError': the result contains no columns (i.e. you should be
294-- using 'execute' instead of 'query').
296-- * 'ResultError': result conversion failed.
297query :: (QueryParams q, QueryResults r)
298 => Connection -> Query -> q -> IO [r]
299query conn template qs = do
300 result <- exec conn =<< formatQuery conn template qs
301 finishQuery conn template result
303-- | A version of 'query' that does not perform query substitution.
304query_ :: (QueryResults r) => Connection -> Query -> IO [r]
305query_ conn q@(Query que) = do
306 result <- exec conn que
307 finishQuery conn q result
309-- | Perform a @SELECT@ or other SQL query that is expected to return
310-- results. Results are streamed incrementally from the server, and
311-- consumed via a left fold.
313-- When dealing with small results, it may be simpler (and perhaps
314-- faster) to use 'query' instead.
316-- This fold is /not/ strict. The stream consumer is responsible for
317-- forcing the evaluation of its result to avoid space leaks.
319-- Exceptions that may be thrown:
321-- * 'FormatError': the query string could not be formatted correctly.
323-- * 'QueryError': the result contains no columns (i.e. you should be
324-- using 'execute' instead of 'query').
326-- * 'ResultError': result conversion failed.
328fold :: ( QueryResults row, QueryParams params )
333 -> (a -> row -> IO a)
335fold = foldWithOptions defaultFoldOptions
343 fetchQuantity :: !FetchQuantity,
344 transactionMode :: !TransactionMode
347defaultFoldOptions = FoldOptions {
348 fetchQuantity = Automatic,
349 transactionMode = TransactionMode ReadCommitted ReadOnly
352foldWithOptions :: ( QueryResults row, QueryParams params )
358 -> (a -> row -> IO a)
360foldWithOptions opts conn template qs a f = do
361 q <- formatQuery conn template qs
362 doFold opts conn template (Query q) a f
364-- | A version of 'fold' that does not perform query substitution.
365fold_ :: (QueryResults r) =>
368 -> a -- ^ Initial state for result consumer.
369 -> (a -> r -> IO a) -- ^ Result consumer.
371fold_ = foldWithOptions_ defaultFoldOptions
374foldWithOptions_ :: (QueryResults r) =>
378 -> a -- ^ Initial state for result consumer.
379 -> (a -> r -> IO a) -- ^ Result consumer.
381foldWithOptions_ opts conn query a f = doFold opts conn query query a f
384doFold :: ( QueryResults row )
390 -> (a -> row -> IO a)
392doFold FoldOptions{..} conn _template q a f = do
393 stat <- withConnection conn PQ.transactionStatus
395 PQ.TransIdle -> withTransactionMode transactionMode conn go
396 PQ.TransInTrans -> go
397 PQ.TransActive -> fail "foldWithOpts FIXME: PQ.TransActive"
398 -- This _shouldn't_ occur in the current incarnation of
399 -- the library, as we aren't using libpq asynchronously.
400 -- However, it could occur in future incarnations of
401 -- this library or if client code uses the Internal module
402 -- to use raw libpq commands on postgresql-simple connections.
403 PQ.TransInError -> fail "foldWithOpts FIXME: PQ.TransInError"
404 -- This should be turned into a better error message.
405 -- It is probably a bad idea to automatically roll
406 -- back the transaction and start another.
407 PQ.TransUnknown -> fail "foldWithOpts FIXME: PQ.TransUnknown"
408 -- Not sure what this means.
411 -- FIXME: what about name clashes with already-declared cursors?
412 _ <- execute_ conn ("DECLARE fold NO SCROLL CURSOR FOR "
414 loop a `finally` execute_ conn "CLOSE fold"
416-- FIXME: choose the Automatic chunkSize more intelligently
417-- One possibility is to use the type of the results, although this
418-- still isn't a perfect solution, given that common types (e.g. text)
419-- are of highly variable size.
420-- A refinement of this technique is to pick this number adaptively
421-- as results are read in from the database.
422 chunkSize = case fetchQuantity of
426 rs <- query conn "FETCH FORWARD ? FROM fold" (Only chunkSize)
427 if null rs then return a else foldM f a rs >>= loop
429-- | A version of 'fold' that does not transform a state value.
430forEach :: (QueryParams q, QueryResults r) =>
432 -> Query -- ^ Query template.
433 -> q -- ^ Query parameters.
434 -> (r -> IO ()) -- ^ Result consumer.
436forEach conn template qs = fold conn template qs () . const
437{-# INLINE forEach #-}
439-- | A version of 'forEach' that does not perform query substitution.
440forEach_ :: (QueryResults r) =>
442 -> Query -- ^ Query template.
443 -> (r -> IO ()) -- ^ Result consumer.
445forEach_ conn template = fold_ conn template () . const
446{-# INLINE forEach_ #-}
448forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
449forM' lo hi m = loop hi []
457finishQuery :: (QueryResults r) => Connection -> Query -> PQ.Result -> IO [r]
458finishQuery conn q result = do
459 status <- PQ.resultStatus result
462 throwIO $ QueryError "query resulted in a command response" q
464 ncols <- PQ.nfields result
465 fields <- forM' 0 (ncols-1) $ \column -> do
466 type_oid <- PQ.ftype result column
467 typename <- getTypename conn type_oid
469 nrows <- PQ.ntuples result
470 forM' 0 (nrows-1) $ \row -> do
471 values <- forM' 0 (ncols-1) (\col -> PQ.getvalue result row col)
472 case convertResults fields values of
473 Left err -> throwIO err
475 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
476 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
478 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
479 statusmsg <- PQ.resStatus status
480 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
481 throwIO $ SqlError { sqlState = state
482 , sqlNativeError = fromEnum status
483 , sqlErrorMsg = B.concat [ "query: ", statusmsg
486-- | Of the four isolation levels defined by the SQL standard,
487-- these are the three levels distinguished by PostgreSQL as of version 9.0.
488-- See <http://www.postgresql.org/docs/9.1/static/transaction-iso.html>
489-- for more information.
495 deriving (Show, Eq, Ord, Enum, Bounded)
500 deriving (Show, Eq, Ord, Enum, Bounded)
502data TransactionMode = TransactionMode {
503 isolationLevel :: !IsolationLevel,
504 readWriteMode :: !ReadWriteMode
505 } deriving (Show, Eq)
507defaultTransactionMode :: TransactionMode
508defaultTransactionMode = TransactionMode ReadCommitted ReadWrite
510defaultIsolationLevel :: IsolationLevel
511defaultIsolationLevel = ReadCommitted
513defaultReadWriteMode :: ReadWriteMode
514defaultReadWriteMode = ReadWrite
516-- | Execute an action inside a SQL transaction.
518-- This function initiates a transaction with a \"@begin
519-- transaction@\" statement, then executes the supplied action. If
520-- the action succeeds, the transaction will be completed with
521-- 'Base.commit' before this function returns.
523-- If the action throws /any/ kind of exception (not just a
524-- PostgreSQL-related exception), the transaction will be rolled back using
525-- 'rollback', then the exception will be rethrown.
526withTransaction :: Connection -> IO a -> IO a
527withTransaction = withTransactionMode defaultTransactionMode
529-- | Execute an action inside a SQL transaction with a given isolation level.
530withTransactionLevel :: IsolationLevel -> Connection -> IO a -> IO a
531withTransactionLevel lvl
532 = withTransactionMode defaultTransactionMode { isolationLevel = lvl }
534-- | Execute an action inside a SQL transaction with a given transaction mode.
535withTransactionMode :: TransactionMode -> Connection -> IO a -> IO a
536withTransactionMode mode conn act =
537 mask $ \restore -> do
539 r <- restore act `onException` rollback conn
543-- | Rollback a transaction.
544rollback :: Connection -> IO ()
545rollback conn = execute_ conn "ABORT" >> return ()
547-- | Commit a transaction.
548commit :: Connection -> IO ()
549commit conn = execute_ conn "COMMIT" >> return ()
551-- | Begin a transaction.
552begin :: Connection -> IO ()
553begin = beginMode defaultTransactionMode
555-- | Begin a transaction with a given isolation level
556beginLevel :: IsolationLevel -> Connection -> IO ()
557beginLevel lvl = beginMode defaultTransactionMode { isolationLevel = lvl }
559-- | Begin a transaction with a given transaction mode
560beginMode :: TransactionMode -> Connection -> IO ()
561beginMode mode conn = do
562 execute_ conn $! case mode of
563 TransactionMode ReadCommitted ReadWrite ->
565 TransactionMode ReadCommitted ReadOnly ->
567 TransactionMode RepeatableRead ReadWrite ->
568 "BEGIN ISOLATION LEVEL REPEATABLE READ"
569 TransactionMode RepeatableRead ReadOnly ->
570 "BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY"
571 TransactionMode Serializable ReadWrite ->
572 "BEGIN ISOLATION LEVEL SERIALIZABLE"
573 TransactionMode Serializable ReadOnly ->
574 "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY"
577fmtError :: String -> Query -> [Action] -> a
578fmtError msg q xs = throw FormatError {
581 , fmtParams = map twiddle xs
583 where twiddle (Plain b) = toByteString b
584 twiddle (Escape s) = s
585 twiddle (Many ys) = B.concat (map twiddle ys)
589-- SQL-based applications are somewhat notorious for their
590-- susceptibility to attacks through the injection of maliciously
591-- crafted data. The primary reason for widespread vulnerability to
592-- SQL injections is that many applications are sloppy in handling
593-- user data when constructing SQL queries.
595-- This library provides a 'Query' type and a parameter substitution
596-- facility to address both ease of use and security.
600-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
601-- exposes a tiny API that is not compatible with the 'ByteString'
602-- API; this makes it difficult to construct queries from fragments of
603-- strings. The 'query' and 'execute' functions require queries to be
606-- To most easily construct a query, enable GHC's @OverloadedStrings@
607-- language extension and write your query as a normal literal string.
609-- > {-# LANGUAGE OverloadedStrings #-}
611-- > import Database.PostgreSQL.Simple
614-- > conn <- connect defaultConnectInfo
615-- > query conn "select 2 + 2"
617-- A 'Query' value does not represent the actual query that will be
618-- executed, but is a template for constructing the final query.
622-- Since applications need to be able to construct queries with
623-- parameters that change, this library provides a query substitution
626-- The 'Query' template accepted by 'query' and 'execute' can contain
627-- any number of \"@?@\" characters. Both 'query' and 'execute'
628-- accept a third argument, typically a tuple. When constructing the
629-- real query to execute, these functions replace the first \"@?@\" in
630-- the template with the first element of the tuple, the second
631-- \"@?@\" with the second element, and so on. If necessary, each
632-- tuple element will be quoted and escaped prior to substitution;
633-- this defeats the single most common injection vector for malicious
636-- For example, given the following 'Query' template:
638-- > select * from user where first_name = ? and age > ?
640-- And a tuple of this form:
642-- > ("Boris" :: String, 37 :: Int)
644-- The query to be executed will look like this after substitution:
646-- > select * from user where first_name = 'Boris' and age > 37
648-- If there is a mismatch between the number of \"@?@\" characters in
649-- your template and the number of elements in your tuple, a
650-- 'FormatError' will be thrown.
652-- Note that the substitution functions do not attempt to parse or
653-- validate your query. It's up to you to write syntactically valid
654-- SQL, and to ensure that each \"@?@\" in your query template is
655-- matched with the right tuple element.
659-- Automated type inference means that you will often be able to avoid
660-- supplying explicit type signatures for the elements of a tuple.
661-- However, sometimes the compiler will not be able to infer your
662-- types. Consider a care where you write a numeric literal in a
665-- > query conn "select ? + ?" (40,2)
667-- The above query will be rejected by the compiler, because it does
668-- not know the specific numeric types of the literals @40@ and @2@.
669-- This is easily fixed:
671-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
673-- The same kind of problem can arise with string literals if you have
674-- the @OverloadedStrings@ language extension enabled. Again, just
675-- use an explicit type signature if this happens.
679-- Haskell lacks a single-element tuple type, so if you have just one
680-- value you want substituted into a query, what should you do?
682-- The obvious approach would appear to be something like this:
684-- > instance (Param a) => QueryParam a where
687-- Unfortunately, this wreaks havoc with type inference, so we take a
688-- different tack. To represent a single value @val@ as a parameter, write
689-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
691-- Here's an example using a singleton list:
693-- > execute conn "insert into users (first_name) values (?)"
698-- Suppose you want to write a query using an @IN@ clause:
700-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
702-- In such cases, it's common for both the elements and length of the
703-- list after the @IN@ keyword to vary from query to query.
705-- To address this case, use the 'In' type wrapper, and use a single
706-- \"@?@\" character to represent the list. Omit the parentheses
707-- around the list; these will be added for you.
711-- > query conn "select * from users where first_name in ?" $
712-- > In ["Anna", "Boris", "Carla"]
714-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
715-- substituted instead, to ensure that your clause remains
716-- syntactically valid.
720-- If you know that you have many rows of data to insert into a table,
721-- it is much more efficient to perform all the insertions in a single
722-- multi-row @INSERT@ statement than individually.
724-- The 'executeMany' function is intended specifically for helping
725-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
726-- query substitution are different than those for 'execute'.
728-- What 'executeMany' searches for in your 'Query' template is a
729-- single substring of the form:
733-- The rules are as follows:
735-- * The keyword @VALUES@ is matched case insensitively.
737-- * There must be no other \"@?@\" characters anywhere in your
740-- * There must one or more \"@?@\" in the parentheses.
742-- * Extra white space is fine.
744-- The last argument to 'executeMany' is a list of parameter
745-- tuples. These will be substituted into the query where the @(?,?)@
746-- string appears, in a form suitable for use in a multi-row @INSERT@
749-- Here is an example:
752-- > "insert into users (first_name,last_name) values (?,?)"
753-- > [("Boris","Karloff"),("Ed","Wood")]
755-- The query that will be executed here will look like this
756-- (reformatted for tidiness):
758-- > insert into users (first_name,last_name) values
759-- > ('Boris','Karloff'),('Ed','Wood')
763-- The 'query' and 'query_' functions return a list of values in the
764-- 'QueryResults' typeclass. This class performs automatic extraction
765-- and type conversion of rows from a query result.
767-- Here is a simple example of how to extract results:
769-- > import qualified Data.Text as Text
771-- > xs <- query_ conn "select name,age from users"
772-- > forM_ xs $ \(name,age) ->
773-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
775-- Notice two important details about this code:
777-- * The number of columns we ask for in the query template must
778-- exactly match the number of elements we specify in a row of the
779-- result tuple. If they do not match, a 'ResultError' exception
782-- * Sometimes, the compiler needs our help in specifying types. It
783-- can infer that @name@ must be a 'Text', due to our use of the
784-- @unpack@ function. However, we have to tell it the type of @age@,
785-- as it has no other information to determine the exact type.
789-- The type of a result tuple will look something like this:
793-- Although SQL can accommodate @NULL@ as a value for any of these
794-- types, Haskell cannot. If your result contains columns that may be
795-- @NULL@, be sure that you use 'Maybe' in those positions of of your
798-- > (Text, Maybe Int, Int)
800-- If 'query' encounters a @NULL@ in a row where the corresponding
801-- Haskell type is not 'Maybe', it will throw a 'ResultError'
806-- To specify that a query returns a single-column result, use the
809-- > xs <- query_ conn "select id from users"
810-- > forM_ xs $ \(Only dbid) -> {- ... -}
814-- Conversion of SQL values to Haskell values is somewhat
815-- permissive. Here are the rules.
817-- * For numeric types, any Haskell type that can accurately represent
818-- all values of the given PostgreSQL type is considered \"compatible\".
819-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
820-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
821-- represent a @SMALLINT@, so it is considered compatble with those types.
823-- * A numeric compatibility check is based only on the type of a
824-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
825-- @BIGINT@ column will be considered incompatible with a Haskell
826-- 'Int16', even if it contains the value @1@.
828-- * If a numeric incompatibility is found, 'query' will throw a
831-- * The 'String' and 'Text' types are assumed to be encoded as
832-- UTF-8. If you use some other encoding, decoding may fail or give
833-- wrong results. In such cases, write a @newtype@ wrapper and a
834-- custom 'Result' instance to handle your encoding.
836getTypename :: Connection -> PQ.Oid -> IO ByteString
837getTypename conn@Connection{..} oid =
838 case oid2builtin oid of
839 Just builtin -> return $! builtin2typname builtin
840 Nothing -> modifyMVar connectionObjects $ \oidmap -> do
841 case IntMap.lookup (oid2int oid) oidmap of
842 Just name -> return (oidmap, name)
844 names <- query conn "SELECT typname FROM pg_type WHERE oid=?"
846 name <- case names of
847 [] -> return $ throw SqlError {
849 sqlErrorMsg = "invalid type oid",
853 _ -> fail "typename query returned more than one result"
854 -- oid is a primary key, so the query should
855 -- never return more than one result
856 return (IntMap.insert (oid2int oid) name oidmap, name)
keysesc to close
- j k
- move down, up
- enter
- open
- esc
- back, or up a level
- 1 2 3 4
- readme, files, commits, refs
- /
- filter
- y
- copy the clone command
- c
- compare the chosen commit with another (or shift-click both)
- ?
- show or hide this list