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, bracket, 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.Param (Action(..), inQuotes)
123import Database.PostgreSQL.Simple.QueryParams (QueryParams(..))
124import Database.PostgreSQL.Simple.Result (ResultError(..))
125import Database.PostgreSQL.Simple.QueryResults (QueryResults(..))
126import Database.PostgreSQL.Simple.Types (Binary(..), In(..), Only(..), Query(..))
127import Database.PostgreSQL.Simple.Internal as Base
128import qualified Database.PostgreSQL.LibPQ as PQ
129import Text.Regex.PCRE.Light (compile, caseless, match)
130import qualified Data.ByteString.Char8 as B
132-- | Exception thrown if a 'Query' could not be formatted correctly.
133-- This may occur if the number of \'@?@\' characters in the query
134-- string does not match the number of parameters provided.
135data FormatError = FormatError {
138 , fmtParams :: [ByteString]
139 } deriving (Eq, Show, Typeable)
141instance Exception FormatError
143-- | Exception thrown if 'query' is used to perform an @INSERT@-like
144-- operation, or 'execute' is used to perform a @SELECT@-like operation.
145data QueryError = QueryError {
148 } deriving (Eq, Show, Typeable)
150instance Exception QueryError
152-- | Format a query string.
154-- This function is exposed to help with debugging and logging. Do not
155-- use it to prepare queries for execution.
157-- String parameters are escaped according to the character set in use
158-- on the 'Connection'.
160-- Throws 'FormatError' if the query string could not be formatted
162formatQuery :: QueryParams q => Connection -> Query -> q -> IO ByteString
163formatQuery conn q@(Query template) qs
164 | null xs && '?' `B.notElem` template = return template
165 | otherwise = toByteString <$> buildQuery conn q template xs
166 where xs = renderParams qs
168-- | Format a query string with a variable number of rows.
170-- This function is exposed to help with debugging and logging. Do not
171-- use it to prepare queries for execution.
173-- The query string must contain exactly one substitution group,
174-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
175-- followed by an \"@(@\" character, a series of one or more \"@?@\"
176-- characters separated by commas, and a \"@)@\" character. White
177-- space in a substitution group is permitted.
179-- Throws 'FormatError' if the query string could not be formatted
181formatMany :: (QueryParams q) => Connection -> Query -> [q] -> IO ByteString
182formatMany _ q [] = fmtError "no rows supplied" q []
183formatMany conn q@(Query template) qs = do
184 case match re template [] of
185 Just [_,before,qbits,after] -> do
186 bs <- mapM (buildQuery conn q qbits . renderParams) qs
187 return . toByteString . mconcat $ fromByteString before :
188 intersperse (fromChar ',') bs ++
189 [fromByteString after]
192 re = compile "^([^?]+\\bvalues\\s*)\
193 \(\\(\\s*[?](?:\\s*,\\s*[?])*\\s*\\))\
197escapeStringConn conn s = withConnection conn $ \c -> do
198 PQ.escapeStringConn c s
200buildQuery :: Connection -> Query -> ByteString -> [Action] -> IO Builder
201buildQuery conn q template xs = zipParams (split template) <$> mapM sub xs
202 where quote = inQuotes . fromByteString . maybe undefined id
203 sub (Plain b) = pure b
204 sub (Escape s) = quote <$> escapeStringConn conn s
205 sub (Many ys) = mconcat <$> mapM sub ys
206 split s = fromByteString h : if B.null t then [] else split (B.tail t)
207 where (h,t) = B.break (=='?') s
208 zipParams (t:ts) (p:ps) = t `mappend` p `mappend` zipParams ts ps
210 zipParams _ _ = fmtError (show (B.count '?' template) ++
211 " '?' characters, but " ++
212 show (length xs) ++ " parameters") q xs
214-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not
215-- expected to return results.
217-- Returns the number of rows affected.
219-- Throws 'FormatError' if the query could not be formatted correctly.
220execute :: (QueryParams q) => Connection -> Query -> q -> IO Int64
221execute conn template qs = do
222 result <- exec conn =<< formatQuery conn template qs
223 finishExecute conn template result
225-- | A version of 'execute' that does not perform query substitution.
226execute_ :: Connection -> Query -> IO Int64
227execute_ conn q@(Query stmt) = do
228 result <- exec conn stmt
229 finishExecute conn q result
231-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not
232-- expected to return results.
234-- Returns the number of rows affected.
236-- Throws 'FormatError' if the query could not be formatted correctly.
237executeMany :: (QueryParams q) => Connection -> Query -> [q] -> IO Int64
238executeMany _ _ [] = return 0
239executeMany conn q qs = do
240 result <- exec conn =<< formatMany conn q qs
241 finishExecute conn q result
243finishExecute :: Connection -> Query -> PQ.Result -> IO Int64
244finishExecute conn q result = do
245 status <- PQ.resultStatus result
248 ncols <- PQ.nfields result
250 then throwIO $ QueryError ("execute resulted in " ++ show ncols ++
253 nstr <- PQ.cmdTuples result
254 return $ case nstr of
255 Nothing -> 0 -- is this appropriate?
256 Just str -> toInteger str
258 ncols <- PQ.nfields result
259 throwIO $ QueryError ("execute resulted in " ++ show ncols ++
261 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
262 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
264 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
265 statusmsg <- PQ.resStatus status
266 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
267 throwIO $ SqlError { sqlState = state
268 , sqlNativeError = fromEnum status
269 , sqlErrorMsg = B.concat [ "execute: ", statusmsg
272 toInteger str = B.foldl' delta 0 str
275 if '0' <= c && c <= '9'
276 then 10 * acc + fromIntegral (ord c - ord '0')
277 else error ("finishExecute: not an int: " ++ B.unpack str)
281-- | Perform a @SELECT@ or other SQL query that is expected to return
282-- results. All results are retrieved and converted before this
285-- When processing large results, this function will consume a lot of
286-- client-side memory. Consider using 'fold' instead.
288-- Exceptions that may be thrown:
290-- * 'FormatError': the query string could not be formatted correctly.
292-- * 'QueryError': the result contains no columns (i.e. you should be
293-- using 'execute' instead of 'query').
295-- * 'ResultError': result conversion failed.
296query :: (QueryParams q, QueryResults r)
297 => Connection -> Query -> q -> IO [r]
298query conn template qs = do
299 result <- exec conn =<< formatQuery conn template qs
300 finishQuery conn template result
302-- | A version of 'query' that does not perform query substitution.
303query_ :: (QueryResults r) => Connection -> Query -> IO [r]
304query_ conn q@(Query que) = do
305 result <- exec conn que
306 finishQuery conn q result
308-- | Perform a @SELECT@ or other SQL query that is expected to return
309-- results. Results are streamed incrementally from the server, and
310-- consumed via a left fold.
312-- When dealing with small results, it may be simpler (and perhaps
313-- faster) to use 'query' instead.
315-- This fold is /not/ strict. The stream consumer is responsible for
316-- forcing the evaluation of its result to avoid space leaks.
318-- Exceptions that may be thrown:
320-- * 'FormatError': the query string could not be formatted correctly.
322-- * 'QueryError': the result contains no columns (i.e. you should be
323-- using 'execute' instead of 'query').
325-- * 'ResultError': result conversion failed.
327fold :: ( QueryResults row, QueryParams params )
332 -> (a -> row -> IO a)
334fold = foldWithOptions defaultFoldOptions
342 fetchQuantity :: !FetchQuantity,
343 transactionMode :: !TransactionMode
346defaultFoldOptions = FoldOptions {
347 fetchQuantity = Automatic,
348 transactionMode = TransactionMode ReadCommitted ReadOnly
351foldWithOptions :: ( QueryResults row, QueryParams params )
357 -> (a -> row -> IO a)
359foldWithOptions opts conn template qs a f = do
360 q <- formatQuery conn template qs
361 doFold opts conn template (Query q) a f
363-- | A version of 'fold' that does not perform query substitution.
364fold_ :: (QueryResults r) =>
367 -> a -- ^ Initial state for result consumer.
368 -> (a -> r -> IO a) -- ^ Result consumer.
370fold_ = foldWithOptions_ defaultFoldOptions
373foldWithOptions_ :: (QueryResults r) =>
377 -> a -- ^ Initial state for result consumer.
378 -> (a -> r -> IO a) -- ^ Result consumer.
380foldWithOptions_ opts conn query a f = doFold opts conn query query a f
383doFold :: ( QueryResults row )
389 -> (a -> row -> IO a)
391doFold FoldOptions{..} conn _template q a f = do
392 stat <- withConnection conn PQ.transactionStatus
394 PQ.TransIdle -> withTransactionMode transactionMode conn go
395 PQ.TransInTrans -> go
396 PQ.TransActive -> fail "foldWithOpts FIXME: PQ.TransActive"
397 -- This _shouldn't_ occur in the current incarnation of
398 -- the library, as we aren't using libpq asynchronously.
399 -- However, it could occur in future incarnations of
400 -- this library or if client code uses the Internal module
401 -- to use raw libpq commands on postgresql-simple connections.
402 PQ.TransInError -> fail "foldWithOpts FIXME: PQ.TransInError"
403 -- This should be turned into a better error message.
404 -- It is probably a bad idea to automatically roll
405 -- back the transaction and start another.
406 PQ.TransUnknown -> fail "foldWithOpts FIXME: PQ.TransUnknown"
407 -- Not sure what this means.
410 -- FIXME: what about name clashes with already-declared cursors?
411 _ <- execute_ conn ("DECLARE fold NO SCROLL CURSOR FOR "
413 loop a `finally` execute_ conn "CLOSE fold"
415-- FIXME: choose the Automatic chunkSize more intelligently
416-- One possibility is to use the type of the results, although this
417-- still isn't a perfect solution, given that common types (e.g. text)
418-- are of highly variable size.
419-- A refinement of this technique is to pick this number adaptively
420-- as results are read in from the database.
421 chunkSize = case fetchQuantity of
425 rs <- query conn "FETCH FORWARD ? FROM fold" (Only chunkSize)
426 if null rs then return a else foldM f a rs >>= loop
428-- | A version of 'fold' that does not transform a state value.
429forEach :: (QueryParams q, QueryResults r) =>
431 -> Query -- ^ Query template.
432 -> q -- ^ Query parameters.
433 -> (r -> IO ()) -- ^ Result consumer.
435forEach conn template qs = fold conn template qs () . const
436{-# INLINE forEach #-}
438-- | A version of 'forEach' that does not perform query substitution.
439forEach_ :: (QueryResults r) =>
441 -> Query -- ^ Query template.
442 -> (r -> IO ()) -- ^ Result consumer.
444forEach_ conn template = fold_ conn template () . const
445{-# INLINE forEach_ #-}
447forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
448forM' lo hi m = loop hi []
456finishQuery :: (QueryResults r) => Connection -> Query -> PQ.Result -> IO [r]
457finishQuery conn q result = do
458 status <- PQ.resultStatus result
461 throwIO $ QueryError "query resulted in a command response" q
463 ncols <- PQ.nfields result
464 fields <- forM' 0 (ncols-1) $ \column -> do
465 type_oid <- PQ.ftype result column
466 typename <- getTypename conn type_oid
468 nrows <- PQ.ntuples result
469 forM' 0 (nrows-1) $ \row -> do
470 values <- forM' 0 (ncols-1) (\col -> PQ.getvalue result row col)
471 case convertResults fields values of
472 Left err -> throwIO err
474 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
475 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
477 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
478 statusmsg <- PQ.resStatus status
479 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
480 throwIO $ SqlError { sqlState = state
481 , sqlNativeError = fromEnum status
482 , sqlErrorMsg = B.concat [ "query: ", statusmsg
485-- | Of the four isolation levels defined by the SQL standard,
486-- these are the three levels distinguished by PostgreSQL as of version 9.0.
487-- See <http://www.postgresql.org/docs/9.1/static/transaction-iso.html>
488-- for more information.
494 deriving (Show, Eq, Ord, Enum, Bounded)
499 deriving (Show, Eq, Ord, Enum, Bounded)
501data TransactionMode = TransactionMode {
502 isolationLevel :: !IsolationLevel,
503 readWriteMode :: !ReadWriteMode
504 } deriving (Show, Eq)
506defaultTransactionMode :: TransactionMode
507defaultTransactionMode = TransactionMode ReadCommitted ReadWrite
509defaultIsolationLevel :: IsolationLevel
510defaultIsolationLevel = ReadCommitted
512defaultReadWriteMode :: ReadWriteMode
513defaultReadWriteMode = ReadWrite
515-- | Execute an action inside a SQL transaction.
517-- This function initiates a transaction with a \"@begin
518-- transaction@\" statement, then executes the supplied action. If
519-- the action succeeds, the transaction will be completed with
520-- 'Base.commit' before this function returns.
522-- If the action throws /any/ kind of exception (not just a
523-- PostgreSQL-related exception), the transaction will be rolled back using
524-- 'rollback', then the exception will be rethrown.
525withTransaction :: Connection -> IO a -> IO a
526withTransaction = withTransactionMode defaultTransactionMode
528-- | Execute an action inside a SQL transaction with a given isolation level.
529withTransactionLevel :: IsolationLevel -> Connection -> IO a -> IO a
530withTransactionLevel lvl
531 = withTransactionMode defaultTransactionMode { isolationLevel = lvl }
533-- | Execute an action inside a SQL transaction with a given transaction mode.
534withTransactionMode :: TransactionMode -> Connection -> IO a -> IO a
535withTransactionMode mode conn act = do
537 r <- act `onException` rollback conn
541-- | Rollback a transaction.
542rollback :: Connection -> IO ()
543rollback conn = execute_ conn "ABORT" >> return ()
545-- | Commit a transaction.
546commit :: Connection -> IO ()
547commit conn = execute_ conn "COMMIT" >> return ()
549-- | Begin a transaction.
550begin :: Connection -> IO ()
551begin = beginMode defaultTransactionMode
553-- | Begin a transaction with a given isolation level
554beginLevel :: IsolationLevel -> Connection -> IO ()
555beginLevel lvl = beginMode defaultTransactionMode { isolationLevel = lvl }
557-- | Begin a transaction with a given transaction mode
558beginMode :: TransactionMode -> Connection -> IO ()
559beginMode mode conn = do
560 execute_ conn $! case mode of
561 TransactionMode ReadCommitted ReadWrite ->
563 TransactionMode ReadCommitted ReadOnly ->
565 TransactionMode RepeatableRead ReadWrite ->
566 "BEGIN ISOLATION LEVEL REPEATABLE READ"
567 TransactionMode RepeatableRead ReadOnly ->
568 "BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY"
569 TransactionMode Serializable ReadWrite ->
570 "BEGIN ISOLATION LEVEL SERIALIZABLE"
571 TransactionMode Serializable ReadOnly ->
572 "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY"
575fmtError :: String -> Query -> [Action] -> a
576fmtError msg q xs = throw FormatError {
579 , fmtParams = map twiddle xs
581 where twiddle (Plain b) = toByteString b
582 twiddle (Escape s) = s
583 twiddle (Many ys) = B.concat (map twiddle ys)
587-- SQL-based applications are somewhat notorious for their
588-- susceptibility to attacks through the injection of maliciously
589-- crafted data. The primary reason for widespread vulnerability to
590-- SQL injections is that many applications are sloppy in handling
591-- user data when constructing SQL queries.
593-- This library provides a 'Query' type and a parameter substitution
594-- facility to address both ease of use and security.
598-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
599-- exposes a tiny API that is not compatible with the 'ByteString'
600-- API; this makes it difficult to construct queries from fragments of
601-- strings. The 'query' and 'execute' functions require queries to be
604-- To most easily construct a query, enable GHC's @OverloadedStrings@
605-- language extension and write your query as a normal literal string.
607-- > {-# LANGUAGE OverloadedStrings #-}
609-- > import Database.PostgreSQL.Simple
612-- > conn <- connect defaultConnectInfo
613-- > query conn "select 2 + 2"
615-- A 'Query' value does not represent the actual query that will be
616-- executed, but is a template for constructing the final query.
620-- Since applications need to be able to construct queries with
621-- parameters that change, this library provides a query substitution
624-- The 'Query' template accepted by 'query' and 'execute' can contain
625-- any number of \"@?@\" characters. Both 'query' and 'execute'
626-- accept a third argument, typically a tuple. When constructing the
627-- real query to execute, these functions replace the first \"@?@\" in
628-- the template with the first element of the tuple, the second
629-- \"@?@\" with the second element, and so on. If necessary, each
630-- tuple element will be quoted and escaped prior to substitution;
631-- this defeats the single most common injection vector for malicious
634-- For example, given the following 'Query' template:
636-- > select * from user where first_name = ? and age > ?
638-- And a tuple of this form:
640-- > ("Boris" :: String, 37 :: Int)
642-- The query to be executed will look like this after substitution:
644-- > select * from user where first_name = 'Boris' and age > 37
646-- If there is a mismatch between the number of \"@?@\" characters in
647-- your template and the number of elements in your tuple, a
648-- 'FormatError' will be thrown.
650-- Note that the substitution functions do not attempt to parse or
651-- validate your query. It's up to you to write syntactically valid
652-- SQL, and to ensure that each \"@?@\" in your query template is
653-- matched with the right tuple element.
657-- Automated type inference means that you will often be able to avoid
658-- supplying explicit type signatures for the elements of a tuple.
659-- However, sometimes the compiler will not be able to infer your
660-- types. Consider a care where you write a numeric literal in a
663-- > query conn "select ? + ?" (40,2)
665-- The above query will be rejected by the compiler, because it does
666-- not know the specific numeric types of the literals @40@ and @2@.
667-- This is easily fixed:
669-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
671-- The same kind of problem can arise with string literals if you have
672-- the @OverloadedStrings@ language extension enabled. Again, just
673-- use an explicit type signature if this happens.
677-- Haskell lacks a single-element tuple type, so if you have just one
678-- value you want substituted into a query, what should you do?
680-- The obvious approach would appear to be something like this:
682-- > instance (Param a) => QueryParam a where
685-- Unfortunately, this wreaks havoc with type inference, so we take a
686-- different tack. To represent a single value @val@ as a parameter, write
687-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
689-- Here's an example using a singleton list:
691-- > execute conn "insert into users (first_name) values (?)"
696-- Suppose you want to write a query using an @IN@ clause:
698-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
700-- In such cases, it's common for both the elements and length of the
701-- list after the @IN@ keyword to vary from query to query.
703-- To address this case, use the 'In' type wrapper, and use a single
704-- \"@?@\" character to represent the list. Omit the parentheses
705-- around the list; these will be added for you.
709-- > query conn "select * from users where first_name in ?" $
710-- > In ["Anna", "Boris", "Carla"]
712-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
713-- substituted instead, to ensure that your clause remains
714-- syntactically valid.
718-- If you know that you have many rows of data to insert into a table,
719-- it is much more efficient to perform all the insertions in a single
720-- multi-row @INSERT@ statement than individually.
722-- The 'executeMany' function is intended specifically for helping
723-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
724-- query substitution are different than those for 'execute'.
726-- What 'executeMany' searches for in your 'Query' template is a
727-- single substring of the form:
731-- The rules are as follows:
733-- * The keyword @VALUES@ is matched case insensitively.
735-- * There must be no other \"@?@\" characters anywhere in your
738-- * There must one or more \"@?@\" in the parentheses.
740-- * Extra white space is fine.
742-- The last argument to 'executeMany' is a list of parameter
743-- tuples. These will be substituted into the query where the @(?,?)@
744-- string appears, in a form suitable for use in a multi-row @INSERT@
747-- Here is an example:
750-- > "insert into users (first_name,last_name) values (?,?)"
751-- > [("Boris","Karloff"),("Ed","Wood")]
753-- The query that will be executed here will look like this
754-- (reformatted for tidiness):
756-- > insert into users (first_name,last_name) values
757-- > ('Boris','Karloff'),('Ed','Wood')
761-- The 'query' and 'query_' functions return a list of values in the
762-- 'QueryResults' typeclass. This class performs automatic extraction
763-- and type conversion of rows from a query result.
765-- Here is a simple example of how to extract results:
767-- > import qualified Data.Text as Text
769-- > xs <- query_ conn "select name,age from users"
770-- > forM_ xs $ \(name,age) ->
771-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
773-- Notice two important details about this code:
775-- * The number of columns we ask for in the query template must
776-- exactly match the number of elements we specify in a row of the
777-- result tuple. If they do not match, a 'ResultError' exception
780-- * Sometimes, the compiler needs our help in specifying types. It
781-- can infer that @name@ must be a 'Text', due to our use of the
782-- @unpack@ function. However, we have to tell it the type of @age@,
783-- as it has no other information to determine the exact type.
787-- The type of a result tuple will look something like this:
791-- Although SQL can accommodate @NULL@ as a value for any of these
792-- types, Haskell cannot. If your result contains columns that may be
793-- @NULL@, be sure that you use 'Maybe' in those positions of of your
796-- > (Text, Maybe Int, Int)
798-- If 'query' encounters a @NULL@ in a row where the corresponding
799-- Haskell type is not 'Maybe', it will throw a 'ResultError'
804-- To specify that a query returns a single-column result, use the
807-- > xs <- query_ conn "select id from users"
808-- > forM_ xs $ \(Only dbid) -> {- ... -}
812-- Conversion of SQL values to Haskell values is somewhat
813-- permissive. Here are the rules.
815-- * For numeric types, any Haskell type that can accurately represent
816-- all values of the given PostgreSQL type is considered \"compatible\".
817-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
818-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
819-- represent a @SMALLINT@, so it is considered compatble with those types.
821-- * A numeric compatibility check is based only on the type of a
822-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
823-- @BIGINT@ column will be considered incompatible with a Haskell
824-- 'Int16', even if it contains the value @1@.
826-- * If a numeric incompatibility is found, 'query' will throw a
829-- * The 'String' and 'Text' types are assumed to be encoded as
830-- UTF-8. If you use some other encoding, decoding may fail or give
831-- wrong results. In such cases, write a @newtype@ wrapper and a
832-- custom 'Result' instance to handle your encoding.
834getTypename :: Connection -> PQ.Oid -> IO ByteString
835getTypename conn@Connection{..} oid =
836 case oid2builtin oid of
837 Just builtin -> return $! builtin2typname builtin
838 Nothing -> modifyMVar connectionObjects $ \oidmap -> do
839 case IntMap.lookup (oid2int oid) oidmap of
840 Just name -> return (oidmap, name)
842 names <- query conn "SELECT typname FROM pg_type WHERE oid=?"
844 name <- case names of
845 [] -> return $ throw SqlError {
847 sqlErrorMsg = "invalid type oid",
851 _ -> fail "typename query returned more than one result"
852 -- oid is a primary key, so the query should
853 -- never return more than one result
854 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