An interaction with the database

Error during errand

Error codes

Run an errand.

Execute a query and return the internal raw result.

Execute a query and process its result set.

Execute a query and dismiss its result.

Execute a query and process its result set using the provided result processor.

Query including statement and parameters

Use the pgsq quasi-quoter to conveniently create queries.

This quasi-quoter allows you to generate instances of Query. It lets you write SQL with some small enhancements. pgsq heavily relies on QueryTable which can be implemented by mkTable for a type of your choice.

Some syntax definitions that might be useful later on:

TypeName          ::= UpperAlpha {AlphaNumeric | '_'}
Name              ::= (Alpha | '_') {AlphaNumeric | '_'}
QualifiedTypeName ::= {TypeName '.'} TypeName

Alpha includes all alphabetical characters; UpperAlpha includes all upper-case alphabetical characters; AlphaNumeric includes all alpha-numeric characters.

Embed values

You can embed values whose types implement Column.

ValueExp ::= '$' Name

magicNumber :: Int
magicNumber = 1337

myQuery :: Query
myQuery =
    [pgsq| SELECT * FROM table t WHERE t.column1 > $magicNumber AND t.column2 < $otherNumber |]
    where otherNumber = magicNumber * 2

$magicNumber and $otherNumber are references to values magicNumber and otherNumber.

The quasi-quoter will generate a Query expression similar to the following.

Query "SELECT * FROM table t WHERE t.column1 > $1 AND t.column2 < $2"
      [pack magicNumber, pack otherNumber]

Table names

Types that implement QueryTable associate a table name with themselves. Since the table name is not always known to the user, one can insert it dynamically.

TableNameExp ::= '@' QualifiedTypeName

The @-operators is also an alias for the function ABS. If you have an expression that triggers the quasi-quoter such as @A, but you would like to use the ABS functionality, then simply reformat your expression to @(A) or ABS(A).

instance QueryTable YourType where
    tableName _ = "YourTable"

myQuery :: Query
myQuery =
    [pgsq| SELECT * FROM @YourType WHERE @YourType.column = 1337 |]

The table name will be inlined which results in the following.

Query "SELECT * FROM \"YourTable\" WHERE \"YourTable\".column = 1337" []

Identifier column names

Each instance of QueryTable also provides the name of the identifier column. Using this column name you can identify specific rows of a certain table.

TableIdentExp ::= '&' TypeName

& is also the operator for bitwise-AND. To resolve the ambiguity for expressions like A&B, simply reformat it to A & B or A&(B).

instance QueryTable YourType where
    tableName _   = "YourTable"
    tableIDName _ = "id"

listIDs :: Query
listIDs =
    [pgsq| SELECT &YourType FROM @YourType |]

listIDs is now a query which lists the IDs of each row. This is especially useful in combination with Reference.

fetchIDs :: Errand [Reference YourType]
fetchIDs =
    query [pgsq| SELECT &YourType FROM @YourType |]

Selectors

mkTable will automatically implement Result and QueryTable for you. This allows you to make use of the selector expander.

SelectorExp ::= '#' QualifiedTypeName

# is also the operator for bitwise-XOR. To resolve the ambiguity for expressions like A#B, simply reformat it to A # B or A#(B) or A#"B".

data Actor = Actor {
    actorName :: String,
    actorAge  :: Word
} deriving (Show, Eq, Ord)

mkTable ''Actor []

fetchOldActors :: Errand [Actor]
fetchOldActors =
    query [pgsq| SELECT #Actor FROM @Actor a WHERE a.actorAge >= $oldAge |]
    where oldAge = 70

#Actor will expand to a list of columns that are necessary to construct an instance of Actor. In this case it is equivalent to

@Actor.actorName, @Actor.actorAge

Just like pgsq but only produces the statement associated with the query. Referenced values are not inlined, they are simply dismissed.

A type which implements this class can be used as a table in a quasi-quoted query. mkTable can implement this for you.

SELECT expression

Query parameter or value of a column - see pack on how to generate Values manually but conveniently.

Types which implement this type class may be used as column types.

Allows you to implement a custom result parser for your type. mkTable can implement this for your type.

Result processor

Error that occured during result processing

Move cursor to the next column.

Unpack the current column and move the cursor to the next column.

Helper type to capture an single column.

Reference a row of type a.

Qualify a as a table type. mkTable can implement this class for you.

Generate a Query expression which will create the table described by the given type.

Example:

data Table = Table { myField :: Int }
mkTable ''Table []
...
query_ $(mkCreateQuery ''Table)

Implement the type classes QueryTable, Table and Result for the given type.

The given type must fulfill these requirements:

• Data type
• No type context
• No type variables
• One record constructor with 1 or more fields
• All field types must have an instance of Column

Example:

{-# LANGUAGE TemplateHaskell, QuasiQuotes #-}
module Movies where

...

data Movie = Movie {
    movieTitle :: String,
    movieYear  :: Int
} deriving Show

mkTable ''Movie []

data Actor = Actor {
    actorName :: String,
    actorAge  :: Int
} deriving Show

mkTable ''Actor [Unique ['actorName], Check [pgss| actorAge >= 18 |]]

data MovieCast = MovieCast {
    movieCastMovie :: Reference Movie,
    movieCastActor :: Reference Actor
} deriving Show

mkTable ''MovieCast [Unique ['movieCastMovie, 'movieCastActor]]

In this example, Reference takes care of adding the FOREIGN KEY constraint, so we don't have to.

Options to mkTable.