{-# LANGUAGE DeriveDataTypeable #-}{-# LANGUAGE NamedFieldPuns #-}{-# LANGUAGE OverloadedStrings #-}{-# LANGUAGE PatternGuards #-}{-# LANGUAGE RecordWildCards #-}{-# LANGUAGE ViewPatterns #-}-------------------------------------------------------------------------------- |-- Module: Database.PostgreSQL.Simple-- Copyright: (c) 2011 MailRank, Inc.-- (c) 2011-2012 Leon P Smith-- License: BSD3-- Maintainer: Leon P Smith <leon@melding-monads.com>-- Stability: experimental-- Portability: portable---- A mid-level client library for the PostgreSQL database, aimed at ease of-- use and high performance.--------------------------------------------------------------------------------moduleDatabase.PostgreSQL.Simple(-- * Writing queries-- $use-- ** The Query type-- $querytype-- ** Parameter substitution-- $subst-- *** Type inference-- $inference-- ** Substituting a single parameter-- $only_param-- ** Representing a list of values-- $in-- ** Modifying multiple rows at once-- $many-- * Extracting results-- $result-- ** Handling null values-- $null-- ** Type conversions-- $types-- * TypesBase.ConnectInfo(..),Connection,Query,ToRow,FromRow,In(..),Binary(..),Only(..),(:.)(..)-- ** Exceptions,SqlError(..),FormatError(fmtMessage,fmtQuery,fmtParams),QueryError(qeMessage,qeQuery),ResultError(errSQLType,errHaskellType,errMessage)-- * Connection management,Base.connect,Base.connectPostgreSQL,Base.postgreSQLConnectionString,Base.defaultConnectInfo,Base.close-- * Queries that return results,query,query_-- * Queries that stream results,FoldOptions(..),FetchQuantity(..),defaultFoldOptions,fold,foldWithOptions,fold_,foldWithOptions_,forEach,forEach_-- * Statements that do not return results,execute,execute_,executeMany-- , Base.insertID-- * Transaction handling,withTransaction,TransactionMode(..),IsolationLevel(..),ReadWriteMode(..),defaultTransactionMode,defaultIsolationLevel,defaultReadWriteMode,withTransactionLevel,withTransactionMode-- , Base.autocommit,begin,beginLevel,beginMode,commit,rollback-- * Helper functions,formatMany,formatQuery)whereimportBlaze.ByteString.Builder(Builder,fromByteString,toByteString)importBlaze.ByteString.Builder.Char8(fromChar)importControl.Applicative((<$>),pure)importControl.Concurrent.MVarimportControl.Exception(Exception,onException,throw,throwIO,finally)importControl.Monad(foldM)importData.ByteString(ByteString)importData.Int(Int64)importqualifiedData.IntMapasIntMapimportData.List(intersperse)importData.Monoid(mappend,mconcat)importData.Typeable(Typeable)importDatabase.PostgreSQL.Simple.BuiltinTypes(oid2builtin,builtin2typname)importDatabase.PostgreSQL.Simple.Compat(mask)importDatabase.PostgreSQL.Simple.FromField(ResultError(..))importDatabase.PostgreSQL.Simple.FromRow(FromRow(..))importDatabase.PostgreSQL.Simple.OkimportDatabase.PostgreSQL.Simple.ToField(Action(..),inQuotes)importDatabase.PostgreSQL.Simple.ToRow(ToRow(..))importDatabase.PostgreSQL.Simple.Types(Binary(..),In(..),Only(..),Query(..),(:.)(..))importDatabase.PostgreSQL.Simple.InternalasBaseimportqualifiedDatabase.PostgreSQL.LibPQasPQimportqualifiedData.ByteString.Char8asBimportqualifiedData.TextasTimportqualifiedData.Text.EncodingasTEimportqualifiedData.VectorasVimportControl.Monad.Trans.ReaderimportControl.Monad.Trans.State.Strict-- | Exception thrown if a 'Query' could not be formatted correctly.-- This may occur if the number of \'@?@\' characters in the query-- string does not match the number of parameters provided.dataFormatError=FormatError{fmtMessage::String,fmtQuery::Query,fmtParams::[ByteString]}deriving(Eq,Show,Typeable)instanceExceptionFormatError-- | Format a query string.---- This function is exposed to help with debugging and logging. Do not-- use it to prepare queries for execution.---- String parameters are escaped according to the character set in use-- on the 'Connection'.---- Throws 'FormatError' if the query string could not be formatted-- correctly.formatQuery::ToRowq=>Connection->Query->q->IOByteStringformatQueryconnq@(Querytemplate)qs|nullxs&&'?'`B.notElem`template=returntemplate|otherwise=toByteString<$>buildQueryconnqtemplatexswherexs=toRowqs-- | Format a query string with a variable number of rows.---- This function is exposed to help with debugging and logging. Do not-- use it to prepare queries for execution.---- The query string must contain exactly one substitution group,-- identified by the SQL keyword \"@VALUES@\" (case insensitive)-- followed by an \"@(@\" character, a series of one or more \"@?@\"-- characters separated by commas, and a \"@)@\" character. White-- space in a substitution group is permitted.---- Throws 'FormatError' if the query string could not be formatted-- correctly.formatMany::(ToRowq)=>Connection->Query->[q]->IOByteStringformatMany_q[]=fmtError"no rows supplied"q[]formatManyconnq@(Querytemplate)qs=docaseparseTemplatetemplateofJust(before,qbits,after)->dobs<-mapM(buildQueryconnqqbits.toRow)qsreturn.toByteString.mconcat$fromByteStringbefore:intersperse(fromChar',')bs++[fromByteStringafter]Nothing->fmtError"syntax error in query template for executeMany"q[]-- Split the input string into three pieces, @before@, @qbits@, and @after@,-- following this grammar:---- start: ^ before qbits after $-- before: ([^?]* [^?\w])? 'VALUES' \s*-- qbits: '(' \s* '?' \s* (',' \s* '?' \s*)* ')'-- after: [^?]*---- \s: [ \t\n\r\f]-- \w: [A-Z] | [a-z] | [\x80-\xFF] | '_' | '$' | [0-9]---- This would be much more concise with some sort of regex engine.-- 'formatMany' used to use pcre-light instead of this hand-written parser,-- but pcre is a hassle to install on Windows.parseTemplate::ByteString->Maybe(ByteString,ByteString,ByteString)parseTemplatetemplate=-- Convert input string to uppercase, to facilitate searching.search$B.maptoUpper_asciitemplatewhere-- Search for the next occurrence of "VALUES"searchbs=caseB.breakSubstring"VALUES"bsof(x,y)-- If "VALUES" is not present in the string, or any '?' characters-- were encountered prior to it, fail.|B.nully||('?'`B.elem`x)->Nothing-- If "VALUES" is preceded by an identifier character (a.k.a. \w),-- try the next occurrence.|not(B.nullx)&&isIdent(B.lastx)->search$B.drop6y-- Otherwise, we have a legitimate "VALUES" token.|otherwise->parseQueryBits$skipSpace$B.drop6y-- Parse '(' \s* '?' \s* . If this doesn't match-- (and we don't consume a '?'), look for another "VALUES".---- qb points to the open paren (if present), meaning it points to the-- beginning of the "qbits" production described above. This is why we-- pass it down to finishQueryBits.parseQueryBitsqb|Just('(',skipSpace->bs1)<-B.unconsqb,Just('?',skipSpace->bs2)<-B.unconsbs1=finishQueryBitsqbbs2|otherwise=searchqb-- Parse (',' \s* '?' \s*)* ')' [^?]* .---- Since we've already consumed at least one '?', there's no turning back.-- The parse has to succeed here, or the whole thing fails-- (because we don't allow '?' to appear outside of the VALUES list).finishQueryBitsqbbs0|Just(')',bs1)<-B.unconsbs0=if'?'`B.elem`bs1thenNothingelseJust$slice3templateqbbs1|Just(',',skipSpace->bs1)<-B.unconsbs0,Just('?',skipSpace->bs2)<-B.unconsbs1=finishQueryBitsqbbs2|otherwise=Nothing-- Slice a string into three pieces, given the start offset of the second-- and third pieces. Each "offset" is actually a tail of the uppercase-- version of the template string. Its length is used to infer the offset.---- It is important to note that we only slice the original template.-- We don't want our all-caps trick messing up the actual query string.slice3sourcep1p2=(s1,s2,source'')where(s1,source')=B.splitAt(B.lengthsource-B.lengthp1)source(s2,source'')=B.splitAt(B.lengthp1-B.lengthp2)source'toUpper_asciic|c>='a'&&c<='z'=toEnum(fromEnumc-32)|otherwise=c-- Based on the definition of {ident_cont} in src/backend/parser/scan.l-- in the PostgreSQL source. No need to check [a-z], since we converted-- the whole string to uppercase.isIdentc=(c>='0'&&c<='9')||(c>='A'&&c<='Z')||(c>='\x80'&&c<='\xFF')||c=='_'||c=='$'-- Based on {space} in scan.lisSpace_asciic=(c==' ')||(c>='\t'&&c<='\r')skipSpace=B.dropWhileisSpace_asciiescapeStringConn::Connection->ByteString->IO(EitherByteStringByteString)escapeStringConnconns=withConnectionconn$\c->PQ.escapeStringConncs>>=checkErrorcescapeByteaConn::Connection->ByteString->IO(EitherByteStringByteString)escapeByteaConnconns=withConnectionconn$\c->PQ.escapeByteaConncs>>=checkErrorccheckError::PQ.Connection->Maybea->IO(EitherByteStringa)checkError_(Justx)=return$RightxcheckErrorcNothing=Left.maybe""id<$>PQ.errorMessagecbuildQuery::Connection->Query->ByteString->[Action]->IOBuilderbuildQueryconnqtemplatexs=zipParams(splittemplate)<$>mapMsubxswherequote=either(\msg->fmtError(utf8ToStringmsg)qxs)(inQuotes.fromByteString)utf8ToString=T.unpack.TE.decodeUtf8sub(Plainb)=purebsub(Escapes)=quote<$>escapeStringConnconnssub(EscapeByteAs)=quote<$>escapeByteaConnconnssub(Manyys)=mconcat<$>mapMsubyssplits=fromByteStringh:ifB.nulltthen[]elsesplit(B.tailt)where(h,t)=B.break(=='?')szipParams(t:ts)(p:ps)=t`mappend`p`mappend`zipParamstspszipParams[t][]=tzipParams__=fmtError(show(B.count'?'template)++" '?' characters, but "++show(lengthxs)++" parameters")qxs-- | Execute an @INSERT@, @UPDATE@, or other SQL query that is not-- expected to return results.---- Returns the number of rows affected.---- Throws 'FormatError' if the query could not be formatted correctly.execute::(ToRowq)=>Connection->Query->q->IOInt64executeconntemplateqs=doresult<-execconn=<<formatQueryconntemplateqsfinishExecuteconntemplateresult-- | Execute a multi-row @INSERT@, @UPDATE@, or other SQL query that is not-- expected to return results.---- Returns the number of rows affected.---- Throws 'FormatError' if the query could not be formatted correctly.executeMany::(ToRowq)=>Connection->Query->[q]->IOInt64executeMany__[]=return0executeManyconnqqs=doresult<-execconn=<<formatManyconnqqsfinishExecuteconnqresult-- | Perform a @SELECT@ or other SQL query that is expected to return-- results. All results are retrieved and converted before this-- function returns.---- When processing large results, this function will consume a lot of-- client-side memory. Consider using 'fold' instead.---- Exceptions that may be thrown:---- * 'FormatError': the query string could not be formatted correctly.---- * 'QueryError': the result contains no columns (i.e. you should be-- using 'execute' instead of 'query').---- * 'ResultError': result conversion failed.query::(ToRowq,FromRowr)=>Connection->Query->q->IO[r]queryconntemplateqs=doresult<-execconn=<<formatQueryconntemplateqsfinishQueryconntemplateresult-- | A version of 'query' that does not perform query substitution.query_::(FromRowr)=>Connection->Query->IO[r]query_connq@(Queryque)=doresult<-execconnquefinishQueryconnqresult-- | Perform a @SELECT@ or other SQL query that is expected to return-- results. Results are streamed incrementally from the server, and-- consumed via a left fold.---- When dealing with small results, it may be simpler (and perhaps-- faster) to use 'query' instead.---- This fold is /not/ strict. The stream consumer is responsible for-- forcing the evaluation of its result to avoid space leaks.---- Exceptions that may be thrown:---- * 'FormatError': the query string could not be formatted correctly.---- * 'QueryError': the result contains no columns (i.e. you should be-- using 'execute' instead of 'query').---- * 'ResultError': result conversion failed.fold::(FromRowrow,ToRowparams)=>Connection->Query->params->a->(a->row->IOa)->IOafold=foldWithOptionsdefaultFoldOptionsdataFetchQuantity=Automatic|Fixed!IntdataFoldOptions=FoldOptions{fetchQuantity::!FetchQuantity,transactionMode::!TransactionMode}defaultFoldOptions::FoldOptionsdefaultFoldOptions=FoldOptions{fetchQuantity=Automatic,transactionMode=TransactionModeReadCommittedReadOnly}foldWithOptions::(FromRowrow,ToRowparams)=>FoldOptions->Connection->Query->params->a->(a->row->IOa)->IOafoldWithOptionsoptsconntemplateqsaf=doq<-formatQueryconntemplateqsdoFoldoptsconntemplate(Queryq)af-- | A version of 'fold' that does not perform query substitution.fold_::(FromRowr)=>Connection->Query-- ^ Query.->a-- ^ Initial state for result consumer.->(a->r->IOa)-- ^ Result consumer.->IOafold_=foldWithOptions_defaultFoldOptionsfoldWithOptions_::(FromRowr)=>FoldOptions->Connection->Query-- ^ Query.->a-- ^ Initial state for result consumer.->(a->r->IOa)-- ^ Result consumer.->IOafoldWithOptions_optsconnqueryaf=doFoldoptsconnqueryqueryafdoFold::(FromRowrow)=>FoldOptions->Connection->Query->Query->a->(a->row->IOa)->IOadoFoldFoldOptions{..}conn_templateqaf=dostat<-withConnectionconnPQ.transactionStatuscasestatofPQ.TransIdle->withTransactionModetransactionModeconngoPQ.TransInTrans->goPQ.TransActive->fail"foldWithOpts FIXME: PQ.TransActive"-- This _shouldn't_ occur in the current incarnation of-- the library, as we aren't using libpq asynchronously.-- However, it could occur in future incarnations of-- this library or if client code uses the Internal module-- to use raw libpq commands on postgresql-simple connections.PQ.TransInError->fail"foldWithOpts FIXME: PQ.TransInError"-- This should be turned into a better error message.-- It is probably a bad idea to automatically roll-- back the transaction and start another.PQ.TransUnknown->fail"foldWithOpts FIXME: PQ.TransUnknown"-- Not sure what this means.wherego=do-- FIXME: what about name clashes with already-declared cursors?_<-execute_conn("DECLARE fold NO SCROLL CURSOR FOR "`mappend`q)loopa`finally`execute_conn"CLOSE fold"-- FIXME: choose the Automatic chunkSize more intelligently-- One possibility is to use the type of the results, although this-- still isn't a perfect solution, given that common types (e.g. text)-- are of highly variable size.-- A refinement of this technique is to pick this number adaptively-- as results are read in from the database.chunkSize=casefetchQuantityofAutomatic->256Fixedn->nloopa=dors<-queryconn"FETCH FORWARD ? FROM fold"(OnlychunkSize)ifnullrsthenreturnaelsefoldMfars>>=loop-- | A version of 'fold' that does not transform a state value.forEach::(ToRowq,FromRowr)=>Connection->Query-- ^ Query template.->q-- ^ Query parameters.->(r->IO())-- ^ Result consumer.->IO()forEachconntemplateqs=foldconntemplateqs().const{-# INLINE forEach #-}-- | A version of 'forEach' that does not perform query substitution.forEach_::(FromRowr)=>Connection->Query-- ^ Query template.->(r->IO())-- ^ Result consumer.->IO()forEach_conntemplate=fold_conntemplate().const{-# INLINE forEach_ #-}forM'::(Ordn,Numn)=>n->n->(n->IOa)->IO[a]forM'lohim=loophi[]whereloop!n!as|n<lo=returnas|otherwise=doa<-mnloop(n-1)(a:as)finishQuery::(FromRowr)=>Connection->Query->PQ.Result->IO[r]finishQueryconnqresult=dostatus<-PQ.resultStatusresultcasestatusofPQ.EmptyQuery->throwIO$QueryError"query: Empty query"qPQ.CommandOk->dothrowIO$QueryError"query resulted in a command response"qPQ.TuplesOk->doncols<-PQ.nfieldsresultletunCol(PQ.Colx)=fromIntegralx::Inttypenames<-V.generateM(unColncols)(\(PQ.Col.fromIntegral->col)->dogetTypenameconn=<<PQ.ftyperesultcol)nrows<-PQ.ntuplesresultncols<-PQ.nfieldsresultforM'0(nrows-1)$\row->doletrw=RowrowtypenamesresultcaserunStateT(runReaderT(unRPfromRow)rw)0ofOk(val,col)|col==ncols->returnval|otherwise->dovals<-forM'0(ncols-1)$\c->dov<-PQ.getvalueresultrowcreturn(typenamesV.!unColc,fmapellipsisv)throw(ConversionFailed(show(unColncols)++" values: "++showvals)(show(unColcol)++" slots in target type")"mismatch between number of columns to \
\convert and number in target type")Errors[]->throwIO$ConversionFailed"""""unknown error"Errors[x]->throwIOxErrorsxs->throwIO$ManyErrorsxsPQ.CopyOut->throwIO$QueryError"query: COPY TO is not supported"qPQ.CopyIn->throwIO$QueryError"query: COPY FROM is not supported"qPQ.BadResponse->throwResultError"query"resultstatusPQ.NonfatalError->throwResultError"query"resultstatusPQ.FatalError->throwResultError"query"resultstatusellipsis::ByteString->ByteStringellipsisbs|B.lengthbs>15=B.take10bs`B.append`"[...]"|otherwise=bs-- | Of the four isolation levels defined by the SQL standard,-- these are the three levels distinguished by PostgreSQL as of version 9.0.-- See <http://www.postgresql.org/docs/9.1/static/transaction-iso.html>-- for more information. Note that prior to PostgreSQL 9.0, 'RepeatableRead'-- was equivalent to 'Serializable'.dataIsolationLevel=DefaultIsolationLevel-- ^ the isolation level will be taken from-- PostgreSQL's per-connection-- @default_transaction_isolation@ variable,-- which is initialized according to the-- server's config. The default configuration-- is 'ReadCommitted'.|ReadCommitted|RepeatableRead|Serializablederiving(Show,Eq,Ord,Enum,Bounded)dataReadWriteMode=DefaultReadWriteMode-- ^ the read-write mode will be taken from-- PostgreSQL's per-connection-- @default_transaction_read_only@ variable,-- which is initialized according to the-- server's config. The default configuration-- is 'ReadWrite'.|ReadWrite|ReadOnlyderiving(Show,Eq,Ord,Enum,Bounded)dataTransactionMode=TransactionMode{isolationLevel::!IsolationLevel,readWriteMode::!ReadWriteMode}deriving(Show,Eq)defaultTransactionMode::TransactionModedefaultTransactionMode=TransactionModedefaultIsolationLeveldefaultReadWriteModedefaultIsolationLevel::IsolationLeveldefaultIsolationLevel=DefaultIsolationLeveldefaultReadWriteMode::ReadWriteModedefaultReadWriteMode=DefaultReadWriteMode-- | Execute an action inside a SQL transaction.---- This function initiates a transaction with a \"@begin-- transaction@\" statement, then executes the supplied action. If-- the action succeeds, the transaction will be completed with-- 'Base.commit' before this function returns.---- If the action throws /any/ kind of exception (not just a-- PostgreSQL-related exception), the transaction will be rolled back using-- 'rollback', then the exception will be rethrown.withTransaction::Connection->IOa->IOawithTransaction=withTransactionModedefaultTransactionMode-- | Execute an action inside a SQL transaction with a given isolation level.withTransactionLevel::IsolationLevel->Connection->IOa->IOawithTransactionLevellvl=withTransactionModedefaultTransactionMode{isolationLevel=lvl}-- | Execute an action inside a SQL transaction with a given transaction mode.withTransactionMode::TransactionMode->Connection->IOa->IOawithTransactionModemodeconnact=mask$\restore->dobeginModemodeconnr<-restoreact`onException`rollbackconncommitconnreturnr-- | Rollback a transaction.rollback::Connection->IO()rollbackconn=execute_conn"ABORT">>return()-- | Commit a transaction.commit::Connection->IO()commitconn=execute_conn"COMMIT">>return()-- | Begin a transaction.begin::Connection->IO()begin=beginModedefaultTransactionMode-- | Begin a transaction with a given isolation levelbeginLevel::IsolationLevel->Connection->IO()beginLevellvl=beginModedefaultTransactionMode{isolationLevel=lvl}-- | Begin a transaction with a given transaction modebeginMode::TransactionMode->Connection->IO()beginModemodeconn=do_<-execute_conn$!Query(B.concat["BEGIN",isolevel,readmode])return()whereisolevel=caseisolationLevelmodeofDefaultIsolationLevel->""ReadCommitted->" ISOLATION LEVEL READ COMMITTED"RepeatableRead->" ISOLATION LEVEL REPEATABLE READ"Serializable->" ISOLATION LEVEL SERIALIZABLE"readmode=casereadWriteModemodeofDefaultReadWriteMode->""ReadWrite->" READ WRITE"ReadOnly->" READ ONLY"fmtError::String->Query->[Action]->afmtErrormsgqxs=throwFormatError{fmtMessage=msg,fmtQuery=q,fmtParams=maptwiddlexs}wheretwiddle(Plainb)=toByteStringbtwiddle(Escapes)=stwiddle(EscapeByteAs)=stwiddle(Manyys)=B.concat(maptwiddleys)-- $use---- SQL-based applications are somewhat notorious for their-- susceptibility to attacks through the injection of maliciously-- crafted data. The primary reason for widespread vulnerability to-- SQL injections is that many applications are sloppy in handling-- user data when constructing SQL queries.---- This library provides a 'Query' type and a parameter substitution-- facility to address both ease of use and security.-- $querytype---- A 'Query' is a @newtype@-wrapped 'ByteString'. It intentionally-- exposes a tiny API that is not compatible with the 'ByteString'-- API; this makes it difficult to construct queries from fragments of-- strings. The 'query' and 'execute' functions require queries to be-- of type 'Query'.---- To most easily construct a query, enable GHC's @OverloadedStrings@-- language extension and write your query as a normal literal string.---- > {-# LANGUAGE OverloadedStrings #-}-- >-- > import Database.PostgreSQL.Simple-- >-- > hello = do-- > conn <- connect defaultConnectInfo-- > query conn "select 2 + 2"---- A 'Query' value does not represent the actual query that will be-- executed, but is a template for constructing the final query.-- $subst---- Since applications need to be able to construct queries with-- parameters that change, this library provides a query substitution-- capability.---- The 'Query' template accepted by 'query' and 'execute' can contain-- any number of \"@?@\" characters. Both 'query' and 'execute'-- accept a third argument, typically a tuple. When constructing the-- real query to execute, these functions replace the first \"@?@\" in-- the template with the first element of the tuple, the second-- \"@?@\" with the second element, and so on. If necessary, each-- tuple element will be quoted and escaped prior to substitution;-- this defeats the single most common injection vector for malicious-- data.---- For example, given the following 'Query' template:---- > select * from user where first_name = ? and age > ?---- And a tuple of this form:---- > ("Boris" :: String, 37 :: Int)---- The query to be executed will look like this after substitution:---- > select * from user where first_name = 'Boris' and age > 37---- If there is a mismatch between the number of \"@?@\" characters in-- your template and the number of elements in your tuple, a-- 'FormatError' will be thrown.---- Note that the substitution functions do not attempt to parse or-- validate your query. It's up to you to write syntactically valid-- SQL, and to ensure that each \"@?@\" in your query template is-- matched with the right tuple element.-- $inference---- Automated type inference means that you will often be able to avoid-- supplying explicit type signatures for the elements of a tuple.-- However, sometimes the compiler will not be able to infer your-- types. Consider a case where you write a numeric literal in a-- parameter tuple:---- > query conn "select ? + ?" (40,2)---- The above query will be rejected by the compiler, because it does-- not know the specific numeric types of the literals @40@ and @2@.-- This is easily fixed:---- > query conn "select ? + ?" (40 :: Double, 2 :: Double)---- The same kind of problem can arise with string literals if you have-- the @OverloadedStrings@ language extension enabled. Again, just-- use an explicit type signature if this happens.-- $only_param---- Haskell lacks a single-element tuple type, so if you have just one-- value you want substituted into a query, what should you do?---- The obvious approach would appear to be something like this:---- > instance (Param a) => QueryParam a where-- > ...---- Unfortunately, this wreaks havoc with type inference, so we take a-- different tack. To represent a single value @val@ as a parameter, write-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.---- Here's an example using a singleton list:---- > execute conn "insert into users (first_name) values (?)"-- > ["Nuala"]-- $in---- Suppose you want to write a query using an @IN@ clause:---- > select * from users where first_name in ('Anna', 'Boris', 'Carla')---- In such cases, it's common for both the elements and length of the-- list after the @IN@ keyword to vary from query to query.---- To address this case, use the 'In' type wrapper, and use a single-- \"@?@\" character to represent the list. Omit the parentheses-- around the list; these will be added for you.---- Here's an example:---- > query conn "select * from users where first_name in ?" $-- > In ["Anna", "Boris", "Carla"]---- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be-- substituted instead, to ensure that your clause remains-- syntactically valid.-- $many---- If you know that you have many rows of data to insert into a table,-- it is much more efficient to perform all the insertions in a single-- multi-row @INSERT@ statement than individually.---- The 'executeMany' function is intended specifically for helping-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for-- query substitution are different than those for 'execute'.---- What 'executeMany' searches for in your 'Query' template is a-- single substring of the form:---- > values (?,?,?)---- The rules are as follows:---- * The keyword @VALUES@ is matched case insensitively.---- * There must be no other \"@?@\" characters anywhere in your-- template.---- * There must one or more \"@?@\" in the parentheses.---- * Extra white space is fine.---- The last argument to 'executeMany' is a list of parameter-- tuples. These will be substituted into the query where the @(?,?)@-- string appears, in a form suitable for use in a multi-row @INSERT@-- or @UPDATE@.---- Here is an example:---- > executeMany conn-- > "insert into users (first_name,last_name) values (?,?)"-- > [("Boris","Karloff"),("Ed","Wood")]---- The query that will be executed here will look like this-- (reformatted for tidiness):---- > insert into users (first_name,last_name) values-- > ('Boris','Karloff'),('Ed','Wood')-- $result---- The 'query' and 'query_' functions return a list of values in the-- 'FromRow' typeclass. This class performs automatic extraction-- and type conversion of rows from a query result.---- Here is a simple example of how to extract results:---- > import qualified Data.Text as Text-- >-- > xs <- query_ conn "select name,age from users"-- > forM_ xs $ \(name,age) ->-- > putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)---- Notice two important details about this code:---- * The number of columns we ask for in the query template must-- exactly match the number of elements we specify in a row of the-- result tuple. If they do not match, a 'ResultError' exception-- will be thrown.---- * Sometimes, the compiler needs our help in specifying types. It-- can infer that @name@ must be a 'Text', due to our use of the-- @unpack@ function. However, we have to tell it the type of @age@,-- as it has no other information to determine the exact type.-- $null---- The type of a result tuple will look something like this:---- > (Text, Int, Int)---- Although SQL can accommodate @NULL@ as a value for any of these-- types, Haskell cannot. If your result contains columns that may be-- @NULL@, be sure that you use 'Maybe' in those positions of of your-- tuple.---- > (Text, Maybe Int, Int)---- If 'query' encounters a @NULL@ in a row where the corresponding-- Haskell type is not 'Maybe', it will throw a 'ResultError'-- exception.-- $only_result---- To specify that a query returns a single-column result, use the-- 'Only' type.---- > xs <- query_ conn "select id from users"-- > forM_ xs $ \(Only dbid) -> {- ... -}-- $types---- Conversion of SQL values to Haskell values is somewhat-- permissive. Here are the rules.---- * For numeric types, any Haskell type that can accurately represent-- all values of the given PostgreSQL type is considered \"compatible\".-- For instance, you can always extract a PostgreSQL 16-bit @SMALLINT@-- column to a Haskell 'Int'. The Haskell 'Float' type can accurately-- represent a @SMALLINT@, so it is considered compatble with those types.---- * A numeric compatibility check is based only on the type of a-- column, /not/ on its values. For instance, a PostgreSQL 64-bit-- @BIGINT@ column will be considered incompatible with a Haskell-- 'Int16', even if it contains the value @1@.---- * If a numeric incompatibility is found, 'query' will throw a-- 'ResultError'.---- * The 'String' and 'Text' types are assumed to be encoded as-- UTF-8. If you use some other encoding, decoding may fail or give-- wrong results. In such cases, write a @newtype@ wrapper and a-- custom 'Result' instance to handle your encoding.getTypename::Connection->PQ.Oid->IOByteStringgetTypenameconn@Connection{..}oid=caseoid2builtinoidofJustbuiltin->return$!builtin2typnamebuiltinNothing->modifyMVarconnectionObjects$\oidmap->docaseIntMap.lookup(oid2intoid)oidmapofJustname->return(oidmap,name)Nothing->donames<-queryconn"SELECT typname FROM pg_type WHERE oid=?"(Onlyoid)name<-casenamesof[]->return$throwSqlError{sqlNativeError=-1,sqlErrorMsg="invalid type oid",sqlState=""}[Onlyx]->returnx_->fail"typename query returned more than one result"-- oid is a primary key, so the query should-- never return more than one resultreturn(IntMap.insert(oid2intoid)nameoidmap,name)