Database.HDBC.PostgreSQL

HDBC driver interface for PostgreSQL 8.x

Written by John Goerzen, [e-mail]

NOTE ON DATES AND TIMES

The recommended correspondence between PostgreSQL date and time types and HDBC SqlValue types is:

• SqlLocalDate: DATE
• SqlLocalTimeOfDay: TIME WITHOUT TIME ZONE
• SqlZonedLocalTimeOfDay: TIME WITH TIME ZONE
• SqlLocalTime: TIMESTAMP WITHOUT TIME ZONE
• SqlZonedTime: TIMESTAMP WITH TIME ZONE
• SqlUTCTime: TIMESTAMP WITH TIME ZONE
• SqlDiffTime: INTERVAL
• SqlPOSIXTime: NUMERIC
• SqlEpochTime: INTEGER
• SqlTimeDiff: INTERVAL

Other combinations are possible, and may even be converted automatically. The above simply represents the types that seem the most logical correspondence, and thus are tested by the HDBC-PostgreSQL test suite.

Connecting to Databases

connectPostgreSQL :: String -> IO Connection

Connect to a PostgreSQL server.

See http://www.postgresql.org/docs/8.1/static/libpq.html#LIBPQ-CONNECT for the meaning of the connection string.

withPostgreSQL :: String -> (Connection -> IO a) -> IO a

Connect to a PostgreSQL server, and automatically disconnect if the handler exits normally or throws an exception.

connectPostgreSQL' :: String -> IO Connection

withPostgreSQL' :: String -> (Connection -> IO a) -> IO a

data Connection

Instances

IConnection Connection

Methods

disconnect :: Connection -> IO ()

commit :: Connection -> IO ()

rollback :: Connection -> IO ()

runRaw :: Connection -> String -> IO ()

run :: Connection -> String -> [SqlValue] -> IO Integer

prepare :: Connection -> String -> IO Statement

clone :: Connection -> IO Connection

hdbcDriverName :: Connection -> String

hdbcClientVer :: Connection -> String

proxiedClientName :: Connection -> String

proxiedClientVer :: Connection -> String

dbServerVer :: Connection -> String

dbTransactionSupport :: Connection -> Bool

getTables :: Connection -> IO [String]

describeTable :: Connection -> String -> IO [(String, SqlColDesc)]

Transactions

begin :: Connection -> IO ()

PostgreSQL Error Codes

When an SqlError is thrown, the field seState is set to one of the following error codes.

successfulCompletion :: String

Is set to "00000".

warning :: String

Is set to "01000".

warningDynamicResultSetsReturned :: String

Is set to "0100C".

warningImplicitZeroBitPadding :: String

Is set to "01008".

warningNullValueEliminatedInSetFunction :: String

Is set to "01003".

warningPrivilegeNotGranted :: String

Is set to "01007".

warningPrivilegeNotRevoked :: String

Is set to "01006".

warningStringDataRightTruncation :: String

Is set to "01004".

warningDeprecatedFeature :: String

Is set to "01P01".

noData :: String

Is set to "02000".

noAdditionalDynamicResultSetsReturned :: String

Is set to "02001".

sqlStatementNotYetComplete :: String

Is set to "03000".

connectionException :: String

Is set to "08000".

connectionDoesNotExist :: String

Is set to "08003".

connectionFailure :: String

Is set to "08006".

sqlclientUnableToEstablishSqlconnection :: String

Is set to "08001".

sqlserverRejectedEstablishmentOfSqlconnection :: String

Is set to "08004".

transactionResolutionUnknown :: String

Is set to "08007".

protocolViolation :: String

Is set to "08P01".

triggeredActionException :: String

Is set to "09000".

featureNotSupported :: String

Is set to "0A000".

invalidTransactionInitiation :: String

Is set to "0B000".

locatorException :: String

Is set to "0F000".

lEInvalidSpecification :: String

Is set to "0F001".

invalidGrantor :: String

Is set to "0L000".

invalidGrantOperation :: String

Is set to "0LP01".

invalidRoleSpecification :: String

Is set to "0P000".

cardinalityViolation :: String

Is set to "21000".

dataException :: String

Is set to "22000".

arrayElementError :: String

Is set to "2202E".

arraySubscriptError :: String

Same as arrayElementError.

characterNotInRepertoire :: String

Is set to "22021".

datetimeFieldOverflow :: String

Is set to "22008".

datetimeValueOutOfRange :: String

Same as datetimeFieldOverflow.

divisionByZero :: String

Is set to "22012".

errorInAssignment :: String

Is set to "22005".

escapeCharacterConflict :: String

Is set to "2200B".

indicatorOverflow :: String

Is set to "22022".

intervalFieldOverflow :: String

Is set to "22015".

invalidArgumentForLog :: String

Is set to "2201E".

invalidArgumentForPowerFunction :: String

Is set to "2201F".

invalidArgumentForWidthBucketFunction :: String

Is set to "2201G".

invalidCharacterValueForCast :: String

Is set to "22018".

invalidDatetimeFormat :: String

Is set to "22007".

invalidEscapeCharacter :: String

Is set to "22019".

invalidEscapeOctet :: String

Is set to "2200D".

invalidEscapeSequence :: String

Is set to "22025".

nonstandardUseOfEscapeCharacter :: String

Is set to "22P06".

invalidIndicatorParameterValue :: String

Is set to "22010".

invalidLimitValue :: String

Is set to "22020".

invalidParameterValue :: String

Is set to "22023".

invalidRegularExpression :: String

Is set to "2201B".

invalidTimeZoneDisplacementValue :: String

Is set to "22009".

invalidUseOfEscapeCharacter :: String

Is set to "2200C".

mostSpecificTypeMismatch :: String

Is set to "2200G".

nullValueNotAllowed :: String

Is set to "22004".

nullValueNoIndicatorParameter :: String

Is set to "22002".

numericValueOutOfRange :: String

Is set to "22003".

stringDataLengthMismatch :: String

Is set to "22026".

stringDataRightTruncation :: String

Is set to "22001".

substringError :: String

Is set to "22011".

trimError :: String

Is set to "22027".

unterminatedCString :: String

Is set to "22024".

zeroLengthCharacterString :: String

Is set to "2200F".

floatingPointException :: String

Is set to "22P01".

invalidTextRepresentation :: String

Is set to "22P02".

invalidBinaryRepresentation :: String

Is set to "22P03".

badCopyFileFormat :: String

Is set to "22P04".

untranslatableCharacter :: String

Is set to "22P05".

notAnXmlDocument :: String

Is set to "2200L".

invalidXmlDocument :: String

Is set to "2200M".

invalidXmlContent :: String

Is set to "2200N".

invalidXmlComment :: String

Is set to "2200S".

invalidXmlProcessingInstruction :: String

Is set to "2200T".

integrityConstraintViolation :: String

Is set to "23000".

restrictViolation :: String

Is set to "23001".

notNullViolation :: String

Is set to "23502".

foreignKeyViolation :: String

Is set to "23503".

uniqueViolation :: String

Is set to "23505".

checkViolation :: String

Is set to "23514".

invalidCursorState :: String

Is set to "24000".

invalidTransactionState :: String

Is set to "25000".

activeSqlTransaction :: String

Is set to "25001".

branchTransactionAlreadyActive :: String

Is set to "25002".

heldCursorRequiresSameIsolationLevel :: String

Is set to "25008".

inappropriateAccessModeForBranchTransaction :: String

Is set to "25003".

inappropriateIsolationLevelForBranchTransaction :: String

Is set to "25004".

noActiveSqlTransactionForBranchTransaction :: String

Is set to "25005".

readOnlySqlTransaction :: String

Is set to "25006".

schemaAndDataStatementMixingNotSupported :: String

Is set to "25007".

noActiveSqlTransaction :: String

Is set to "25P01".

inFailedSqlTransaction :: String

Is set to "25P02".

invalidSqlStatementName :: String

Is set to "26000".

triggeredDataChangeViolation :: String

Is set to "27000".

invalidAuthorizationSpecification :: String

Is set to "28000".

dependentPrivilegeDescriptorsStillExist :: String

Is set to "2B000".

dependentObjectsStillExist :: String

Is set to "2BP01".

invalidTransactionTermination :: String

Is set to "2D000".

sqlRoutineException :: String

Is set to "2F000".

sREFunctionExecutedNoReturnStatement :: String

Is set to "2F005".

sREModifyingSqlDataNotPermitted :: String

Is set to "2F002".

sREProhibitedSqlStatementAttempted :: String

Is set to "2F003".

sREReadingSqlDataNotPermitted :: String

Is set to "2F004".

invalidCursorName :: String

Is set to "34000".

externalRoutineException :: String

Is set to "38000".

eREContainingSqlNotPermitted :: String

Is set to "38001".

eREModifyingSqlDataNotPermitted :: String

Is set to "38002".

eREProhibitedSqlStatementAttempted :: String

Is set to "38003".

eREReadingSqlDataNotPermitted :: String

Is set to "38004".

externalRoutineInvocationException :: String

Is set to "39000".

eRIEInvalidSqlstateReturned :: String

Is set to "39001".

eRIENullValueNotAllowed :: String

Is set to "39004".

eRIETriggerProtocolViolated :: String

Is set to "39P01".

eRIESrfProtocolViolated :: String

Is set to "39P02".

savepointException :: String

Is set to "3B000".

sEInvalidSpecification :: String

Is set to "3B001".

invalidCatalogName :: String

Is set to "3D000".

invalidSchemaName :: String

Is set to "3F000".

transactionRollback :: String

Is set to "40000".

tRIntegrityConstraintViolation :: String

Is set to "40002".

tRSerializationFailure :: String

Is set to "40001".

tRStatementCompletionUnknown :: String

Is set to "40003".

tRDeadlockDetected :: String

Is set to "40P01".

syntaxErrorOrAccessRuleViolation :: String

Is set to "42000".

syntaxError :: String

Is set to "42601".

insufficientPrivilege :: String

Is set to "42501".

cannotCoerce :: String

Is set to "42846".

groupingError :: String

Is set to "42803".

invalidForeignKey :: String

Is set to "42830".

invalidName :: String

Is set to "42602".

nameTooLong :: String

Is set to "42622".

reservedName :: String

Is set to "42939".

datatypeMismatch :: String

Is set to "42804".

indeterminateDatatype :: String

Is set to "42P18".

wrongObjectType :: String

Is set to "42809".

undefinedColumn :: String

Is set to "42703".

undefinedCursor :: String

Same as invalidCursorName.

undefinedDatabase :: String

Same as invalidCatalogName.

undefinedFunction :: String

Is set to "42883".

undefinedPstatement :: String

Same as invalidSqlStatementName.

undefinedSchema :: String

Same as invalidSchemaName.

undefinedTable :: String

Is set to "42P01".

undefinedParameter :: String

Is set to "42P02".

undefinedObject :: String

Is set to "42704".

duplicateColumn :: String

Is set to "42701".

duplicateCursor :: String

Is set to "42P03".

duplicateDatabase :: String

Is set to "42P04".

duplicateFunction :: String

Is set to "42723".

duplicatePstatement :: String

Is set to "42P05".

duplicateSchema :: String

Is set to "42P06".

duplicateTable :: String

Is set to "42P07".

duplicateAlias :: String

Is set to "42712".

duplicateObject :: String

Is set to "42710".

ambiguousColumn :: String

Is set to "42702".

ambiguousFunction :: String

Is set to "42725".

ambiguousParameter :: String

Is set to "42P08".

ambiguousAlias :: String

Is set to "42P09".

invalidColumnReference :: String

Is set to "42P10".

invalidColumnDefinition :: String

Is set to "42611".

invalidCursorDefinition :: String

Is set to "42P11".

invalidDatabaseDefinition :: String

Is set to "42P12".

invalidFunctionDefinition :: String

Is set to "42P13".

invalidPstatementDefinition :: String

Is set to "42P14".

invalidSchemaDefinition :: String

Is set to "42P15".

invalidTableDefinition :: String

Is set to "42P16".

invalidObjectDefinition :: String

Is set to "42P17".

withCheckOptionViolation :: String

Is set to "44000".

insufficientResources :: String

Is set to "53000".

diskFull :: String

Is set to "53100".

outOfMemory :: String

Is set to "53200".

tooManyConnections :: String

Is set to "53300".

programLimitExceeded :: String

Is set to "54000".

statementTooComplex :: String

Is set to "54001".

tooManyColumns :: String

Is set to "54011".

tooManyArguments :: String

Is set to "54023".

objectNotInPrerequisiteState :: String

Is set to "55000".

objectInUse :: String

Is set to "55006".

cantChangeRuntimeParam :: String

Is set to "55P02".

lockNotAvailable :: String

Is set to "55P03".

operatorIntervention :: String

Is set to "57000".

queryCanceled :: String

Is set to "57014".

adminShutdown :: String

Is set to "57P01".

crashShutdown :: String

Is set to "57P02".

cannotConnectNow :: String

Is set to "57P03".

ioError :: String

Is set to "58030".

undefinedFile :: String

Is set to "58P01".

duplicateFile :: String

Is set to "58P02".

configFileError :: String

Is set to "F0000".

lockFileExists :: String

Is set to "F0001".

plpgsqlError :: String

Is set to "P0000".

raiseException :: String

Is set to "P0001".

noDataFound :: String

Is set to "P0002".

tooManyRows :: String

Is set to "P0003".

internalError :: String

Is set to "XX000".

dataCorrupted :: String

Is set to "XX001".

indexCorrupted :: String

Is set to "XX002".

Threading

Provided the local libpq library is thread-safe, multiple Connections may be used to have concurrent database queries. Concurrent queries issued on a single Connection will be performed serially.

When the local libpq library is not thread-safe (ie. it has not been compiled with --enable-thread-safety), only a single database function will be performed at a time.