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 Data.ByteString (ByteString)
115import Data.Int (Int64)
116import qualified Data.IntMap as IntMap
117import Data.List (intersperse)
118import Data.Monoid (mappend, mconcat)
119import Data.Typeable (Typeable)
120import Database.PostgreSQL.Simple.BuiltinTypes (oid2builtin, builtin2typname)
121import Database.PostgreSQL.Simple.Compat (mask)
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 :: Connection -> ByteString -> IO (Maybe ByteString)
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
348defaultFoldOptions = FoldOptions {
349 fetchQuantity = Automatic,
350 transactionMode = TransactionMode ReadCommitted ReadOnly
353foldWithOptions :: ( QueryResults row, QueryParams params )
359 -> (a -> row -> IO a)
361foldWithOptions opts conn template qs a f = do
362 q <- formatQuery conn template qs
363 doFold opts conn template (Query q) a f
365-- | A version of 'fold' that does not perform query substitution.
366fold_ :: (QueryResults r) =>
369 -> a -- ^ Initial state for result consumer.
370 -> (a -> r -> IO a) -- ^ Result consumer.
372fold_ = foldWithOptions_ defaultFoldOptions
375foldWithOptions_ :: (QueryResults r) =>
379 -> a -- ^ Initial state for result consumer.
380 -> (a -> r -> IO a) -- ^ Result consumer.
382foldWithOptions_ opts conn query a f = doFold opts conn query query a f
385doFold :: ( QueryResults row )
391 -> (a -> row -> IO a)
393doFold FoldOptions{..} conn _template q a f = do
394 stat <- withConnection conn PQ.transactionStatus
396 PQ.TransIdle -> withTransactionMode transactionMode conn go
397 PQ.TransInTrans -> go
398 PQ.TransActive -> fail "foldWithOpts FIXME: PQ.TransActive"
399 -- This _shouldn't_ occur in the current incarnation of
400 -- the library, as we aren't using libpq asynchronously.
401 -- However, it could occur in future incarnations of
402 -- this library or if client code uses the Internal module
403 -- to use raw libpq commands on postgresql-simple connections.
404 PQ.TransInError -> fail "foldWithOpts FIXME: PQ.TransInError"
405 -- This should be turned into a better error message.
406 -- It is probably a bad idea to automatically roll
407 -- back the transaction and start another.
408 PQ.TransUnknown -> fail "foldWithOpts FIXME: PQ.TransUnknown"
409 -- Not sure what this means.
412 -- FIXME: what about name clashes with already-declared cursors?
413 _ <- execute_ conn ("DECLARE fold NO SCROLL CURSOR FOR "
415 loop a `finally` execute_ conn "CLOSE fold"
417-- FIXME: choose the Automatic chunkSize more intelligently
418-- One possibility is to use the type of the results, although this
419-- still isn't a perfect solution, given that common types (e.g. text)
420-- are of highly variable size.
421-- A refinement of this technique is to pick this number adaptively
422-- as results are read in from the database.
423 chunkSize = case fetchQuantity of
427 rs <- query conn "FETCH FORWARD ? FROM fold" (Only chunkSize)
428 if null rs then return a else foldM f a rs >>= loop
430-- | A version of 'fold' that does not transform a state value.
431forEach :: (QueryParams q, QueryResults r) =>
433 -> Query -- ^ Query template.
434 -> q -- ^ Query parameters.
435 -> (r -> IO ()) -- ^ Result consumer.
437forEach conn template qs = fold conn template qs () . const
438{-# INLINE forEach #-}
440-- | A version of 'forEach' that does not perform query substitution.
441forEach_ :: (QueryResults r) =>
443 -> Query -- ^ Query template.
444 -> (r -> IO ()) -- ^ Result consumer.
446forEach_ conn template = fold_ conn template () . const
447{-# INLINE forEach_ #-}
449forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
450forM' lo hi m = loop hi []
458finishQuery :: (QueryResults r) => Connection -> Query -> PQ.Result -> IO [r]
459finishQuery conn q result = do
460 status <- PQ.resultStatus result
463 throwIO $ QueryError "query resulted in a command response" q
465 ncols <- PQ.nfields result
466 fields <- forM' 0 (ncols-1) $ \column -> do
467 type_oid <- PQ.ftype result column
468 typename <- getTypename conn type_oid
470 nrows <- PQ.ntuples result
471 forM' 0 (nrows-1) $ \row -> do
472 values <- forM' 0 (ncols-1) (\col -> PQ.getvalue result row col)
473 case convertResults fields values of
474 Left err -> throwIO err
476 PQ.CopyIn -> fail "FIXME: postgresql-simple does not currently support COPY IN"
477 PQ.CopyOut -> fail "FIXME: postgresql-simple does not currently support COPY OUT"
479 errormsg <- maybe "" id <$> PQ.resultErrorMessage result
480 statusmsg <- PQ.resStatus status
481 state <- maybe "" id <$> PQ.resultErrorField result PQ.DiagSqlstate
482 throwIO $ SqlError { sqlState = state
483 , sqlNativeError = fromEnum status
484 , sqlErrorMsg = B.concat [ "query: ", statusmsg
487-- | Of the four isolation levels defined by the SQL standard,
488-- these are the three levels distinguished by PostgreSQL as of version 9.0.
489-- See <http://www.postgresql.org/docs/9.1/static/transaction-iso.html>
490-- for more information.
496 deriving (Show, Eq, Ord, Enum, Bounded)
501 deriving (Show, Eq, Ord, Enum, Bounded)
503data TransactionMode = TransactionMode {
504 isolationLevel :: !IsolationLevel,
505 readWriteMode :: !ReadWriteMode
506 } deriving (Show, Eq)
508defaultTransactionMode :: TransactionMode
509defaultTransactionMode = TransactionMode ReadCommitted ReadWrite
511defaultIsolationLevel :: IsolationLevel
512defaultIsolationLevel = ReadCommitted
514defaultReadWriteMode :: ReadWriteMode
515defaultReadWriteMode = ReadWrite
517-- | Execute an action inside a SQL transaction.
519-- This function initiates a transaction with a \"@begin
520-- transaction@\" statement, then executes the supplied action. If
521-- the action succeeds, the transaction will be completed with
522-- 'Base.commit' before this function returns.
524-- If the action throws /any/ kind of exception (not just a
525-- PostgreSQL-related exception), the transaction will be rolled back using
526-- 'rollback', then the exception will be rethrown.
527withTransaction :: Connection -> IO a -> IO a
528withTransaction = withTransactionMode defaultTransactionMode
530-- | Execute an action inside a SQL transaction with a given isolation level.
531withTransactionLevel :: IsolationLevel -> Connection -> IO a -> IO a
532withTransactionLevel lvl
533 = withTransactionMode defaultTransactionMode { isolationLevel = lvl }
535-- | Execute an action inside a SQL transaction with a given transaction mode.
536withTransactionMode :: TransactionMode -> Connection -> IO a -> IO a
537withTransactionMode mode conn act =
538 mask $ \restore -> do
540 r <- restore act `onException` rollback conn
544-- | Rollback a transaction.
545rollback :: Connection -> IO ()
546rollback conn = execute_ conn "ABORT" >> return ()
548-- | Commit a transaction.
549commit :: Connection -> IO ()
550commit conn = execute_ conn "COMMIT" >> return ()
552-- | Begin a transaction.
553begin :: Connection -> IO ()
554begin = beginMode defaultTransactionMode
556-- | Begin a transaction with a given isolation level
557beginLevel :: IsolationLevel -> Connection -> IO ()
558beginLevel lvl = beginMode defaultTransactionMode { isolationLevel = lvl }
560-- | Begin a transaction with a given transaction mode
561beginMode :: TransactionMode -> Connection -> IO ()
562beginMode mode conn = do
563 _ <- execute_ conn $!
565 TransactionMode ReadCommitted ReadWrite ->
567 TransactionMode ReadCommitted ReadOnly ->
569 TransactionMode RepeatableRead ReadWrite ->
570 "BEGIN ISOLATION LEVEL REPEATABLE READ"
571 TransactionMode RepeatableRead ReadOnly ->
572 "BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY"
573 TransactionMode Serializable ReadWrite ->
574 "BEGIN ISOLATION LEVEL SERIALIZABLE"
575 TransactionMode Serializable ReadOnly ->
576 "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY"
579fmtError :: String -> Query -> [Action] -> a
580fmtError msg q xs = throw FormatError {
583 , fmtParams = map twiddle xs
585 where twiddle (Plain b) = toByteString b
586 twiddle (Escape s) = s
587 twiddle (Many ys) = B.concat (map twiddle ys)
591-- SQL-based applications are somewhat notorious for their
592-- susceptibility to attacks through the injection of maliciously
593-- crafted data. The primary reason for widespread vulnerability to
594-- SQL injections is that many applications are sloppy in handling
595-- user data when constructing SQL queries.
597-- This library provides a 'Query' type and a parameter substitution
598-- facility to address both ease of use and security.
602-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
603-- exposes a tiny API that is not compatible with the 'ByteString'
604-- API; this makes it difficult to construct queries from fragments of
605-- strings. The 'query' and 'execute' functions require queries to be
608-- To most easily construct a query, enable GHC's @OverloadedStrings@
609-- language extension and write your query as a normal literal string.
611-- > {-# LANGUAGE OverloadedStrings #-}
613-- > import Database.PostgreSQL.Simple
616-- > conn <- connect defaultConnectInfo
617-- > query conn "select 2 + 2"
619-- A 'Query' value does not represent the actual query that will be
620-- executed, but is a template for constructing the final query.
624-- Since applications need to be able to construct queries with
625-- parameters that change, this library provides a query substitution
628-- The 'Query' template accepted by 'query' and 'execute' can contain
629-- any number of \"@?@\" characters. Both 'query' and 'execute'
630-- accept a third argument, typically a tuple. When constructing the
631-- real query to execute, these functions replace the first \"@?@\" in
632-- the template with the first element of the tuple, the second
633-- \"@?@\" with the second element, and so on. If necessary, each
634-- tuple element will be quoted and escaped prior to substitution;
635-- this defeats the single most common injection vector for malicious
638-- For example, given the following 'Query' template:
640-- > select * from user where first_name = ? and age > ?
642-- And a tuple of this form:
644-- > ("Boris" :: String, 37 :: Int)
646-- The query to be executed will look like this after substitution:
648-- > select * from user where first_name = 'Boris' and age > 37
650-- If there is a mismatch between the number of \"@?@\" characters in
651-- your template and the number of elements in your tuple, a
652-- 'FormatError' will be thrown.
654-- Note that the substitution functions do not attempt to parse or
655-- validate your query. It's up to you to write syntactically valid
656-- SQL, and to ensure that each \"@?@\" in your query template is
657-- matched with the right tuple element.
661-- Automated type inference means that you will often be able to avoid
662-- supplying explicit type signatures for the elements of a tuple.
663-- However, sometimes the compiler will not be able to infer your
664-- types. Consider a care where you write a numeric literal in a
667-- > query conn "select ? + ?" (40,2)
669-- The above query will be rejected by the compiler, because it does
670-- not know the specific numeric types of the literals @40@ and @2@.
671-- This is easily fixed:
673-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
675-- The same kind of problem can arise with string literals if you have
676-- the @OverloadedStrings@ language extension enabled. Again, just
677-- use an explicit type signature if this happens.
681-- Haskell lacks a single-element tuple type, so if you have just one
682-- value you want substituted into a query, what should you do?
684-- The obvious approach would appear to be something like this:
686-- > instance (Param a) => QueryParam a where
689-- Unfortunately, this wreaks havoc with type inference, so we take a
690-- different tack. To represent a single value @val@ as a parameter, write
691-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
693-- Here's an example using a singleton list:
695-- > execute conn "insert into users (first_name) values (?)"
700-- Suppose you want to write a query using an @IN@ clause:
702-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
704-- In such cases, it's common for both the elements and length of the
705-- list after the @IN@ keyword to vary from query to query.
707-- To address this case, use the 'In' type wrapper, and use a single
708-- \"@?@\" character to represent the list. Omit the parentheses
709-- around the list; these will be added for you.
713-- > query conn "select * from users where first_name in ?" $
714-- > In ["Anna", "Boris", "Carla"]
716-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
717-- substituted instead, to ensure that your clause remains
718-- syntactically valid.
722-- If you know that you have many rows of data to insert into a table,
723-- it is much more efficient to perform all the insertions in a single
724-- multi-row @INSERT@ statement than individually.
726-- The 'executeMany' function is intended specifically for helping
727-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
728-- query substitution are different than those for 'execute'.
730-- What 'executeMany' searches for in your 'Query' template is a
731-- single substring of the form:
735-- The rules are as follows:
737-- * The keyword @VALUES@ is matched case insensitively.
739-- * There must be no other \"@?@\" characters anywhere in your
742-- * There must one or more \"@?@\" in the parentheses.
744-- * Extra white space is fine.
746-- The last argument to 'executeMany' is a list of parameter
747-- tuples. These will be substituted into the query where the @(?,?)@
748-- string appears, in a form suitable for use in a multi-row @INSERT@
751-- Here is an example:
754-- > "insert into users (first_name,last_name) values (?,?)"
755-- > [("Boris","Karloff"),("Ed","Wood")]
757-- The query that will be executed here will look like this
758-- (reformatted for tidiness):
760-- > insert into users (first_name,last_name) values
761-- > ('Boris','Karloff'),('Ed','Wood')
765-- The 'query' and 'query_' functions return a list of values in the
766-- 'QueryResults' typeclass. This class performs automatic extraction
767-- and type conversion of rows from a query result.
769-- Here is a simple example of how to extract results:
771-- > import qualified Data.Text as Text
773-- > xs <- query_ conn "select name,age from users"
774-- > forM_ xs $ \(name,age) ->
775-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
777-- Notice two important details about this code:
779-- * The number of columns we ask for in the query template must
780-- exactly match the number of elements we specify in a row of the
781-- result tuple. If they do not match, a 'ResultError' exception
784-- * Sometimes, the compiler needs our help in specifying types. It
785-- can infer that @name@ must be a 'Text', due to our use of the
786-- @unpack@ function. However, we have to tell it the type of @age@,
787-- as it has no other information to determine the exact type.
791-- The type of a result tuple will look something like this:
795-- Although SQL can accommodate @NULL@ as a value for any of these
796-- types, Haskell cannot. If your result contains columns that may be
797-- @NULL@, be sure that you use 'Maybe' in those positions of of your
800-- > (Text, Maybe Int, Int)
802-- If 'query' encounters a @NULL@ in a row where the corresponding
803-- Haskell type is not 'Maybe', it will throw a 'ResultError'
808-- To specify that a query returns a single-column result, use the
811-- > xs <- query_ conn "select id from users"
812-- > forM_ xs $ \(Only dbid) -> {- ... -}
816-- Conversion of SQL values to Haskell values is somewhat
817-- permissive. Here are the rules.
819-- * For numeric types, any Haskell type that can accurately represent
820-- all values of the given PostgreSQL type is considered \"compatible\".
821-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
822-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
823-- represent a @SMALLINT@, so it is considered compatble with those types.
825-- * A numeric compatibility check is based only on the type of a
826-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
827-- @BIGINT@ column will be considered incompatible with a Haskell
828-- 'Int16', even if it contains the value @1@.
830-- * If a numeric incompatibility is found, 'query' will throw a
833-- * The 'String' and 'Text' types are assumed to be encoded as
834-- UTF-8. If you use some other encoding, decoding may fail or give
835-- wrong results. In such cases, write a @newtype@ wrapper and a
836-- custom 'Result' instance to handle your encoding.
838getTypename :: Connection -> PQ.Oid -> IO ByteString
839getTypename conn@Connection{..} oid =
840 case oid2builtin oid of
841 Just builtin -> return $! builtin2typname builtin
842 Nothing -> modifyMVar connectionObjects $ \oidmap -> do
843 case IntMap.lookup (oid2int oid) oidmap of
844 Just name -> return (oidmap, name)
846 names <- query conn "SELECT typname FROM pg_type WHERE oid=?"
848 name <- case names of
849 [] -> return $ throw SqlError {
851 sqlErrorMsg = "invalid type oid",
855 _ -> fail "typename query returned more than one result"
856 -- oid is a primary key, so the query should
857 -- never return more than one result
858 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