1{-# LANGUAGE DeriveDataTypeable #-}
2{-# LANGUAGE NamedFieldPuns #-}
3{-# LANGUAGE OverloadedStrings #-}
4{-# LANGUAGE PatternGuards #-}
5{-# LANGUAGE RecordWildCards #-}
6{-# LANGUAGE ViewPatterns #-}
8------------------------------------------------------------------------------
10-- Module: Database.PostgreSQL.Simple
11-- Copyright: (c) 2011 MailRank, Inc.
12-- (c) 2011-2012 Leon P Smith
14-- Maintainer: Leon P Smith <leon@melding-monads.com>
15-- Stability: experimental
17-- A mid-level client library for the PostgreSQL database, aimed at ease of
18-- use and high performance.
20------------------------------------------------------------------------------
22module Database.PostgreSQL.Simple
30 -- ** Parameter substitution
36 -- ** Substituting a single parameter
39 -- ** Representing a list of values
42 -- ** Modifying multiple rows at once
45 -- ** @RETURNING@: modifications that returns results
48 -- * Extracting results
51 -- ** Handling null values
54 -- ** Type conversions
70 , FormatError(fmtMessage, fmtQuery, fmtParams)
71 , QueryError(qeMessage, qeQuery)
72 , ResultError(errSQLType, errHaskellType, errMessage)
73 -- * Connection management
75 , Base.connectPostgreSQL
76 , Base.postgreSQLConnectionString
77 , Base.defaultConnectInfo
79 -- * Queries that return results
82 -- * Queries that stream results
93 -- * Statements that do not return results
98 -- * Transaction handling
105 -- * Helper functions
110import Blaze.ByteString.Builder
111 ( Builder, fromByteString, toByteString )
112import Blaze.ByteString.Builder.Char8 (fromChar)
113import Blaze.Text ( integral )
114import Control.Applicative ((<$>), pure)
115import Control.Exception as E
116import Control.Monad (foldM)
117import Data.ByteString (ByteString)
118import Data.Int (Int64)
119import Data.List (intersperse)
120import Data.Monoid (mconcat)
121import Data.Typeable (Typeable)
122import Database.PostgreSQL.Simple.Compat ( (<>) )
123import Database.PostgreSQL.Simple.FromField (ResultError(..))
124import Database.PostgreSQL.Simple.FromRow (FromRow(..))
125import Database.PostgreSQL.Simple.Ok
126import Database.PostgreSQL.Simple.ToField (Action(..), inQuotes)
127import Database.PostgreSQL.Simple.ToRow (ToRow(..))
128import Database.PostgreSQL.Simple.Types
129 ( Binary(..), In(..), Only(..), Query(..), (:.)(..) )
130import Database.PostgreSQL.Simple.Internal as Base
131import Database.PostgreSQL.Simple.Transaction
132import Database.PostgreSQL.Simple.TypeInfo
133import qualified Database.PostgreSQL.LibPQ as PQ
134import qualified Data.ByteString.Char8 as B
135import qualified Data.Text as T
136import qualified Data.Text.Encoding as TE
137import Control.Monad.Trans.Reader
138import Control.Monad.Trans.State.Strict
140-- | Exception thrown if a 'Query' could not be formatted correctly.
141-- This may occur if the number of \'@?@\' characters in the query
142-- string does not match the number of parameters provided.
143data FormatError = FormatError {
146 , fmtParams :: [ByteString]
147 } deriving (Eq, Show, Typeable)
149instance Exception FormatError
151-- | Format a query string.
153-- This function is exposed to help with debugging and logging. Do not
154-- use it to prepare queries for execution.
156-- String parameters are escaped according to the character set in use
157-- on the 'Connection'.
159-- Throws 'FormatError' if the query string could not be formatted
161formatQuery :: ToRow q => Connection -> Query -> q -> IO ByteString
162formatQuery conn q@(Query template) qs
163 | null xs && '?' `B.notElem` template = return template
164 | otherwise = toByteString <$> buildQuery conn q template xs
167-- | Format a query string with a variable number of rows.
169-- This function is exposed to help with debugging and logging. Do not
170-- use it to prepare queries for execution.
172-- The query string must contain exactly one substitution group,
173-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
174-- followed by an \"@(@\" character, a series of one or more \"@?@\"
175-- characters separated by commas, and a \"@)@\" character. White
176-- space in a substitution group is permitted.
178-- Throws 'FormatError' if the query string could not be formatted
180formatMany :: (ToRow q) => Connection -> Query -> [q] -> IO ByteString
181formatMany _ q [] = fmtError "no rows supplied" q []
182formatMany conn q@(Query template) qs = do
183 case parseTemplate template of
184 Just (before, qbits, after) -> do
185 bs <- mapM (buildQuery conn q qbits . toRow) qs
186 return . toByteString . mconcat $ fromByteString before :
187 intersperse (fromChar ',') bs ++
188 [fromByteString after]
189 Nothing -> fmtError "syntax error in multi-row template" q []
191-- Split the input string into three pieces, @before@, @qbits@, and @after@,
192-- following this grammar:
194-- start: ^ before qbits after $
195-- before: ([^?]* [^?\w])? 'VALUES' \s*
196-- qbits: '(' \s* '?' \s* (',' \s* '?' \s*)* ')'
200-- \w: [A-Z] | [a-z] | [\x80-\xFF] | '_' | '$' | [0-9]
202-- This would be much more concise with some sort of regex engine.
203-- 'formatMany' used to use pcre-light instead of this hand-written parser,
204-- but pcre is a hassle to install on Windows.
205parseTemplate :: ByteString -> Maybe (ByteString, ByteString, ByteString)
206parseTemplate template =
207 -- Convert input string to uppercase, to facilitate searching.
208 search $ B.map toUpper_ascii template
210 -- Search for the next occurrence of "VALUES"
212 case B.breakSubstring "VALUES" bs of
214 -- If "VALUES" is not present in the string, or any '?' characters
215 -- were encountered prior to it, fail.
216 | B.null y || ('?' `B.elem` x)
219 -- If "VALUES" is preceded by an identifier character (a.k.a. \w),
220 -- try the next occurrence.
221 | not (B.null x) && isIdent (B.last x)
222 -> search $ B.drop 6 y
224 -- Otherwise, we have a legitimate "VALUES" token.
226 -> parseQueryBits $ skipSpace $ B.drop 6 y
228 -- Parse '(' \s* '?' \s* . If this doesn't match
229 -- (and we don't consume a '?'), look for another "VALUES".
231 -- qb points to the open paren (if present), meaning it points to the
232 -- beginning of the "qbits" production described above. This is why we
233 -- pass it down to finishQueryBits.
235 | Just ('(', skipSpace -> bs1) <- B.uncons qb
236 , Just ('?', skipSpace -> bs2) <- B.uncons bs1
237 = finishQueryBits qb bs2
241 -- Parse (',' \s* '?' \s*)* ')' [^?]* .
243 -- Since we've already consumed at least one '?', there's no turning back.
244 -- The parse has to succeed here, or the whole thing fails
245 -- (because we don't allow '?' to appear outside of the VALUES list).
246 finishQueryBits qb bs0
247 | Just (')', bs1) <- B.uncons bs0
248 = if '?' `B.elem` bs1
250 else Just $ slice3 template qb bs1
251 | Just (',', skipSpace -> bs1) <- B.uncons bs0
252 , Just ('?', skipSpace -> bs2) <- B.uncons bs1
253 = finishQueryBits qb bs2
257 -- Slice a string into three pieces, given the start offset of the second
258 -- and third pieces. Each "offset" is actually a tail of the uppercase
259 -- version of the template string. Its length is used to infer the offset.
261 -- It is important to note that we only slice the original template.
262 -- We don't want our all-caps trick messing up the actual query string.
263 slice3 source p1 p2 =
266 (s1, source') = B.splitAt (B.length source - B.length p1) source
267 (s2, source'') = B.splitAt (B.length p1 - B.length p2) source'
269 toUpper_ascii c | c >= 'a' && c <= 'z' = toEnum (fromEnum c - 32)
272 -- Based on the definition of {ident_cont} in src/backend/parser/scan.l
273 -- in the PostgreSQL source. No need to check [a-z], since we converted
274 -- the whole string to uppercase.
275 isIdent c = (c >= '0' && c <= '9')
276 || (c >= 'A' && c <= 'Z')
277 || (c >= '\x80' && c <= '\xFF')
281 -- Based on {space} in scan.l
282 isSpace_ascii c = (c == ' ') || (c >= '\t' && c <= '\r')
284 skipSpace = B.dropWhile isSpace_ascii
286escapeStringConn :: Connection -> ByteString -> IO (Either ByteString ByteString)
287escapeStringConn conn s =
288 withConnection conn $ \c ->
289 PQ.escapeStringConn c s >>= checkError c
291escapeByteaConn :: Connection -> ByteString -> IO (Either ByteString ByteString)
292escapeByteaConn conn s =
293 withConnection conn $ \c ->
294 PQ.escapeByteaConn c s >>= checkError c
296checkError :: PQ.Connection -> Maybe a -> IO (Either ByteString a)
297checkError _ (Just x) = return $ Right x
298checkError c Nothing = Left . maybe "" id <$> PQ.errorMessage c
300buildQuery :: Connection -> Query -> ByteString -> [Action] -> IO Builder
301buildQuery conn q template xs = zipParams (split template) <$> mapM sub xs
302 where quote = either (\msg -> fmtError (utf8ToString msg) q xs)
303 (inQuotes . fromByteString)
304 utf8ToString = T.unpack . TE.decodeUtf8
305 sub (Plain b) = pure b
306 sub (Escape s) = quote <$> escapeStringConn conn s
307 sub (EscapeByteA s) = quote <$> escapeByteaConn conn s
308 sub (Many ys) = mconcat <$> mapM sub ys
309 split s = fromByteString h : if B.null t then [] else split (B.tail t)
310 where (h,t) = B.break (=='?') s
311 zipParams (t:ts) (p:ps) = t <> p <> zipParams ts ps
313 zipParams _ _ = fmtError (show (B.count '?' template) ++
314 " '?' characters, but " ++
315 show (length xs) ++ " parameters") q xs
317-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not
318-- expected to return results.
320-- Returns the number of rows affected.
322-- Throws 'FormatError' if the query could not be formatted correctly.
323execute :: (ToRow q) => Connection -> Query -> q -> IO Int64
324execute conn template qs = do
325 result <- exec conn =<< formatQuery conn template qs
326 finishExecute conn template result
328-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not
329-- expected to return results.
331-- Returns the number of rows affected.
333-- Throws 'FormatError' if the query could not be formatted correctly.
334executeMany :: (ToRow q) => Connection -> Query -> [q] -> IO Int64
335executeMany _ _ [] = return 0
336executeMany conn q qs = do
337 result <- exec conn =<< formatMany conn q qs
338 finishExecute conn q result
340-- | Execute @INSERT ... RETURNING@, @UPDATE ... RETURNING@, or other SQL
341-- query that accepts multi-row input and is expected to return results.
342-- Note that it is possible to write
343-- @'query' conn "INSERT ... RETURNING ..." ...@
344-- in cases where you are only inserting a single row, and do not need
345-- functionality analogous to 'executeMany'.
347-- Throws 'FormatError' if the query could not be formatted correctly.
348returning :: (ToRow q, FromRow r) => Connection -> Query -> [q] -> IO [r]
349returning _ _ [] = return []
350returning conn q qs = do
351 result <- exec conn =<< formatMany conn q qs
352 finishQuery conn q result
354-- | Perform a @SELECT@ or other SQL query that is expected to return
355-- results. All results are retrieved and converted before this
358-- When processing large results, this function will consume a lot of
359-- client-side memory. Consider using 'fold' instead.
361-- Exceptions that may be thrown:
363-- * 'FormatError': the query string could not be formatted correctly.
365-- * 'QueryError': the result contains no columns (i.e. you should be
366-- using 'execute' instead of 'query').
368-- * 'ResultError': result conversion failed.
369query :: (ToRow q, FromRow r)
370 => Connection -> Query -> q -> IO [r]
371query conn template qs = do
372 result <- exec conn =<< formatQuery conn template qs
373 finishQuery conn template result
375-- | A version of 'query' that does not perform query substitution.
376query_ :: (FromRow r) => Connection -> Query -> IO [r]
377query_ conn q@(Query que) = do
378 result <- exec conn que
379 finishQuery conn q result
381-- | Perform a @SELECT@ or other SQL query that is expected to return
382-- results. Results are streamed incrementally from the server, and
383-- consumed via a left fold.
385-- When dealing with small results, it may be simpler (and perhaps
386-- faster) to use 'query' instead.
388-- This fold is /not/ strict. The stream consumer is responsible for
389-- forcing the evaluation of its result to avoid space leaks.
391-- Exceptions that may be thrown:
393-- * 'FormatError': the query string could not be formatted correctly.
395-- * 'QueryError': the result contains no columns (i.e. you should be
396-- using 'execute' instead of 'query').
398-- * 'ResultError': result conversion failed.
400fold :: ( FromRow row, ToRow params )
405 -> (a -> row -> IO a)
407fold = foldWithOptions defaultFoldOptions
415 fetchQuantity :: !FetchQuantity,
416 transactionMode :: !TransactionMode
419defaultFoldOptions :: FoldOptions
420defaultFoldOptions = FoldOptions {
421 fetchQuantity = Automatic,
422 transactionMode = TransactionMode ReadCommitted ReadOnly
425foldWithOptions :: ( FromRow row, ToRow params )
431 -> (a -> row -> IO a)
433foldWithOptions opts conn template qs a f = do
434 q <- formatQuery conn template qs
435 doFold opts conn template (Query q) a f
437-- | A version of 'fold' that does not perform query substitution.
438fold_ :: (FromRow r) =>
441 -> a -- ^ Initial state for result consumer.
442 -> (a -> r -> IO a) -- ^ Result consumer.
444fold_ = foldWithOptions_ defaultFoldOptions
447foldWithOptions_ :: (FromRow r) =>
451 -> a -- ^ Initial state for result consumer.
452 -> (a -> r -> IO a) -- ^ Result consumer.
454foldWithOptions_ opts conn query a f = doFold opts conn query query a f
457doFold :: ( FromRow row )
463 -> (a -> row -> IO a)
465doFold FoldOptions{..} conn _template q a0 f = do
466 stat <- withConnection conn PQ.transactionStatus
468 PQ.TransIdle -> withTransactionMode transactionMode conn go
469 PQ.TransInTrans -> go
470 PQ.TransActive -> fail "foldWithOpts FIXME: PQ.TransActive"
471 -- This _shouldn't_ occur in the current incarnation of
472 -- the library, as we aren't using libpq asynchronously.
473 -- However, it could occur in future incarnations of
474 -- this library or if client code uses the Internal module
475 -- to use raw libpq commands on postgresql-simple connections.
476 PQ.TransInError -> fail "foldWithOpts FIXME: PQ.TransInError"
477 -- This should be turned into a better error message.
478 -- It is probably a bad idea to automatically roll
479 -- back the transaction and start another.
480 PQ.TransUnknown -> fail "foldWithOpts FIXME: PQ.TransUnknown"
481 -- Not sure what this means.
484 name <- newTempName conn
485 _ <- execute_ conn $ mconcat
486 [ "DECLARE ", name, " NO SCROLL CURSOR FOR ", q ]
488 fetch (Query name) = query_ conn $
489 Query (toByteString (fromByteString "FETCH FORWARD "
490 <> integral chunkSize
491 <> fromByteString " FROM "
492 <> fromByteString name
495 (execute_ conn ("CLOSE " <> name) >> return ()) `E.catch` \ex ->
496 -- Don't throw exception if CLOSE failed because the transaction is
497 -- aborted. Otherwise, it will throw away the original error.
498 if isFailedTransactionError ex then return () else throwIO ex
500 go = bracket declare close $ \name ->
503 if null rs then return a else foldM f a rs >>= loop
506-- FIXME: choose the Automatic chunkSize more intelligently
507-- One possibility is to use the type of the results, although this
508-- still isn't a perfect solution, given that common types (e.g. text)
509-- are of highly variable size.
510-- A refinement of this technique is to pick this number adaptively
511-- as results are read in from the database.
512 chunkSize = case fetchQuantity of
516-- | A version of 'fold' that does not transform a state value.
517forEach :: (ToRow q, FromRow r) =>
519 -> Query -- ^ Query template.
520 -> q -- ^ Query parameters.
521 -> (r -> IO ()) -- ^ Result consumer.
523forEach conn template qs = fold conn template qs () . const
524{-# INLINE forEach #-}
526-- | A version of 'forEach' that does not perform query substitution.
527forEach_ :: (FromRow r) =>
529 -> Query -- ^ Query template.
530 -> (r -> IO ()) -- ^ Result consumer.
532forEach_ conn template = fold_ conn template () . const
533{-# INLINE forEach_ #-}
535forM' :: (Ord n, Num n) => n -> n -> (n -> IO a) -> IO [a]
536forM' lo hi m = loop hi []
544finishQuery :: (FromRow r) => Connection -> Query -> PQ.Result -> IO [r]
545finishQuery conn q result = do
546 status <- PQ.resultStatus result
549 throwIO $ QueryError "query: Empty query" q
551 throwIO $ QueryError "query resulted in a command response" q
553 let unCol (PQ.Col x) = fromIntegral x :: Int
554 nrows <- PQ.ntuples result
555 ncols <- PQ.nfields result
556 forM' 0 (nrows-1) $ \row -> do
557 let rw = Row row result
558 okvc <- runConversion (runStateT (runReaderT (unRP fromRow) rw) 0) conn
560 Ok (val,col) | col == ncols -> return val
562 vals <- forM' 0 (ncols-1) $ \c -> do
563 tinfo <- getTypeInfo conn =<< PQ.ftype result c
564 v <- PQ.getvalue result row c
567 throw (ConversionFailed
568 (show (unCol ncols) ++ " values: " ++ show vals)
571 (show (unCol col) ++ " slots in target type")
572 "mismatch between number of columns to \
573 \convert and number in target type")
574 Errors [] -> throwIO $ ConversionFailed "" Nothing "" "" "unknown error"
575 Errors [x] -> throwIO x
576 Errors xs -> throwIO $ ManyErrors xs
578 throwIO $ QueryError "query: COPY TO is not supported" q
580 throwIO $ QueryError "query: COPY FROM is not supported" q
581 PQ.BadResponse -> throwResultError "query" result status
582 PQ.NonfatalError -> throwResultError "query" result status
583 PQ.FatalError -> throwResultError "query" result status
585ellipsis :: ByteString -> ByteString
587 | B.length bs > 15 = B.take 10 bs `B.append` "[...]"
590fmtError :: String -> Query -> [Action] -> a
591fmtError msg q xs = throw FormatError {
594 , fmtParams = map twiddle xs
596 where twiddle (Plain b) = toByteString b
597 twiddle (Escape s) = s
598 twiddle (EscapeByteA s) = s
599 twiddle (Many ys) = B.concat (map twiddle ys)
603-- SQL-based applications are somewhat notorious for their
604-- susceptibility to attacks through the injection of maliciously
605-- crafted data. The primary reason for widespread vulnerability to
606-- SQL injections is that many applications are sloppy in handling
607-- user data when constructing SQL queries.
609-- This library provides a 'Query' type and a parameter substitution
610-- facility to address both ease of use and security.
614-- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally
615-- exposes a tiny API that is not compatible with the 'ByteString'
616-- API; this makes it difficult to construct queries from fragments of
617-- strings. The 'query' and 'execute' functions require queries to be
620-- To most easily construct a query, enable GHC's @OverloadedStrings@
621-- language extension and write your query as a normal literal string.
623-- > {-# LANGUAGE OverloadedStrings #-}
625-- > import Database.PostgreSQL.Simple
628-- > conn <- connect defaultConnectInfo
629-- > query_ conn "select 2 + 2"
631-- A 'Query' value does not represent the actual query that will be
632-- executed, but is a template for constructing the final query.
636-- Since applications need to be able to construct queries with
637-- parameters that change, this library provides a query substitution
640-- The 'Query' template accepted by 'query' and 'execute' can contain
641-- any number of \"@?@\" characters. Both 'query' and 'execute'
642-- accept a third argument, typically a tuple. When constructing the
643-- real query to execute, these functions replace the first \"@?@\" in
644-- the template with the first element of the tuple, the second
645-- \"@?@\" with the second element, and so on. If necessary, each
646-- tuple element will be quoted and escaped prior to substitution;
647-- this defeats the single most common injection vector for malicious
650-- For example, given the following 'Query' template:
652-- > select * from user where first_name = ? and age > ?
654-- And a tuple of this form:
656-- > ("Boris" :: String, 37 :: Int)
658-- The query to be executed will look like this after substitution:
660-- > select * from user where first_name = 'Boris' and age > 37
662-- If there is a mismatch between the number of \"@?@\" characters in
663-- your template and the number of elements in your tuple, a
664-- 'FormatError' will be thrown.
666-- Note that the substitution functions do not attempt to parse or
667-- validate your query. It's up to you to write syntactically valid
668-- SQL, and to ensure that each \"@?@\" in your query template is
669-- matched with the right tuple element.
673-- Automated type inference means that you will often be able to avoid
674-- supplying explicit type signatures for the elements of a tuple.
675-- However, sometimes the compiler will not be able to infer your
676-- types. Consider a case where you write a numeric literal in a
679-- > query conn "select ? + ?" (40,2)
681-- The above query will be rejected by the compiler, because it does
682-- not know the specific numeric types of the literals @40@ and @2@.
683-- This is easily fixed:
685-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)
687-- The same kind of problem can arise with string literals if you have
688-- the @OverloadedStrings@ language extension enabled. Again, just
689-- use an explicit type signature if this happens.
693-- Haskell lacks a single-element tuple type, so if you have just one
694-- value you want substituted into a query, what should you do?
696-- The obvious approach would appear to be something like this:
698-- > instance (ToField a) => ToRow a where
701-- Unfortunately, this wreaks havoc with type inference, so we take a
702-- different tack. To represent a single value @val@ as a parameter, write
703-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.
705-- Here's an example using a singleton list:
707-- > execute conn "insert into users (first_name) values (?)"
712-- Suppose you want to write a query using an @IN@ clause:
714-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')
716-- In such cases, it's common for both the elements and length of the
717-- list after the @IN@ keyword to vary from query to query.
719-- To address this case, use the 'In' type wrapper, and use a single
720-- \"@?@\" character to represent the list. Omit the parentheses
721-- around the list; these will be added for you.
725-- > query conn "select * from users where first_name in ?" $
726-- > In ["Anna", "Boris", "Carla"]
728-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be
729-- substituted instead, to ensure that your clause remains
730-- syntactically valid.
734-- If you know that you have many rows of data to insert into a table,
735-- it is much more efficient to perform all the insertions in a single
736-- multi-row @INSERT@ statement than individually.
738-- The 'executeMany' function is intended specifically for helping
739-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for
740-- query substitution are different than those for 'execute'.
742-- What 'executeMany' searches for in your 'Query' template is a
743-- single substring of the form:
747-- The rules are as follows:
749-- * The keyword @VALUES@ is matched case insensitively.
751-- * There must be no other \"@?@\" characters anywhere in your
754-- * There must be one or more \"@?@\" in the parentheses.
756-- * Extra white space is fine.
758-- The last argument to 'executeMany' is a list of parameter
759-- tuples. These will be substituted into the query where the @(?,?)@
760-- string appears, in a form suitable for use in a multi-row @INSERT@
763-- Here is an example:
766-- > "insert into users (first_name,last_name) values (?,?)"
767-- > [("Boris","Karloff"),("Ed","Wood")]
769-- The query that will be executed here will look like this
770-- (reformatted for tidiness):
772-- > insert into users (first_name,last_name) values
773-- > ('Boris','Karloff'),('Ed','Wood')
777-- PostgreSQL supports returning values from data manipulation statements
778-- such as @INSERT@ and @UPDATE@. You can use these statements by
779-- using 'query' instead of 'execute'. For multi-tuple inserts,
780-- use 'returning' instead of 'executeMany'.
782-- For example, were there an auto-incrementing @id@ column and
783-- timestamp column @t@ that defaulted to the present time for the
784-- @sales@ table, then the following query would insert two new
785-- sales records and also return their new @id@s and timestamps.
787-- > let q = "insert into sales (amount, label) values (?,?) returning id, t"
788-- > xs :: [(Int, UTCTime)] <- query conn q (15,"Sawdust")
789-- > ys :: [(Int, UTCTime)] <- returning conn q [(20,"Chips"),(300,"Wood")]
793-- The 'query' and 'query_' functions return a list of values in the
794-- 'FromRow' typeclass. This class performs automatic extraction
795-- and type conversion of rows from a query result.
797-- Here is a simple example of how to extract results:
799-- > import qualified Data.Text as Text
801-- > xs <- query_ conn "select name,age from users"
802-- > forM_ xs $ \(name,age) ->
803-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)
805-- Notice two important details about this code:
807-- * The number of columns we ask for in the query template must
808-- exactly match the number of elements we specify in a row of the
809-- result tuple. If they do not match, a 'ResultError' exception
812-- * Sometimes, the compiler needs our help in specifying types. It
813-- can infer that @name@ must be a 'Text', due to our use of the
814-- @unpack@ function. However, we have to tell it the type of @age@,
815-- as it has no other information to determine the exact type.
819-- The type of a result tuple will look something like this:
823-- Although SQL can accommodate @NULL@ as a value for any of these
824-- types, Haskell cannot. If your result contains columns that may be
825-- @NULL@, be sure that you use 'Maybe' in those positions of your
828-- > (Text, Maybe Int, Int)
830-- If 'query' encounters a @NULL@ in a row where the corresponding
831-- Haskell type is not 'Maybe', it will throw a 'ResultError'
836-- To specify that a query returns a single-column result, use the
839-- > xs <- query_ conn "select id from users"
840-- > forM_ xs $ \(Only dbid) -> {- ... -}
844-- Conversion of SQL values to Haskell values is somewhat
845-- permissive. Here are the rules.
847-- * For numeric types, any Haskell type that can accurately represent
848-- all values of the given PostgreSQL type is considered \"compatible\".
849-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@
850-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately
851-- represent a @SMALLINT@, so it is considered compatble with those types.
853-- * A numeric compatibility check is based only on the type of a
854-- column, /not/ on its values. For instance, a PostgreSQL 64-bit
855-- @BIGINT@ column will be considered incompatible with a Haskell
856-- 'Int16', even if it contains the value @1@.
858-- * If a numeric incompatibility is found, 'query' will throw a
861-- * The 'String' and 'Text' types are assumed to be encoded as
862-- UTF-8. If you use some other encoding, decoding may fail or give
863-- wrong results. In such cases, write a @newtype@ wrapper and a
864-- custom 'Result' instance to handle your encoding.
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