PgNamed

Introduces named parameters for postgresql-simple library. It uses ? question mark symbol as the indicator of the named parameter which is replaced with the standard syntax with question marks.

Check out the example of usage:

queryNamed dbConnection [sql|
    SELECT *
    FROM users
    WHERE foo = ?foo
      AND bar = ?bar
      AND baz = ?foo
|] [ "foo" =? "fooBar"
   , "bar" =? "barVar"
   ]

Named data types and smart constructors

data NamedParam

Data type to represent each named parameter.

Constructors

NamedParam

Fields

• namedParamName :: !Name
• namedParamParam :: !Action

Instances

Show NamedParam

Defined in PgNamed

Methods

showsPrec :: Int -> NamedParam -> ShowS

show :: NamedParam -> String

showList :: [NamedParam] -> ShowS

newtype Name

Wrapper over name of the argument.

Constructors

Name

Fields

• unName :: Text

Instances

Eq Name

Defined in PgNamed

Methods

(==) :: Name -> Name -> Bool

(/=) :: Name -> Name -> Bool

Ord Name

Defined in PgNamed

Methods

compare :: Name -> Name -> Ordering

(<) :: Name -> Name -> Bool

(<=) :: Name -> Name -> Bool

(>) :: Name -> Name -> Bool

(>=) :: Name -> Name -> Bool

max :: Name -> Name -> Name

min :: Name -> Name -> Name

Show Name

Defined in PgNamed

Methods

showsPrec :: Int -> Name -> ShowS

show :: Name -> String

showList :: [Name] -> ShowS

IsString Name

Defined in PgNamed

Methods

fromString :: String -> Name

(=?) :: ToField a => Name -> a -> NamedParam infix 1

Operator to create NamedParams.

>>> "foo" =? (1 :: Int)
NamedParam {namedParamName = "foo", namedParamParam = Plain "1"}

So it can be used in creating the list of the named arguments:

queryNamed dbConnection [sql|
    SELECT *
    FROM users
    WHERE foo = ?foo
      AND bar = ?bar
      AND baz = ?foo
|] [ "foo" =? "fooBar"
   , "bar" =? "barVar"
   ]

Errors

data PgNamedError

PostgreSQL error type for named parameters.

Constructors

PgNamedParam Name

Named parameter is not specified.

PgNoNames Query

Query has no names inside but was called with named functions.

PgEmptyName Query

Query contains an empty name.

Instances

Eq PgNamedError

Defined in PgNamed

Methods

(==) :: PgNamedError -> PgNamedError -> Bool

(/=) :: PgNamedError -> PgNamedError -> Bool

Show PgNamedError

Defined in PgNamed

Methods

showsPrec :: Int -> PgNamedError -> ShowS

show :: PgNamedError -> String

showList :: [PgNamedError] -> ShowS

type WithNamedError = MonadError PgNamedError

Type alias for monads that can throw errors of the PgNamedError type.

Functions to deal with named parameters

extractNames :: Query -> Either PgNamedError (Query, NonEmpty Name)

This function takes query with named parameters specified like this:

SELECT name, user FROM users WHERE id = ?id

and returns either the error or the query with all names replaced by question marks ? with the list of the names in the order of their appearance.

For example:

>>> extractNames "SELECT * FROM users WHERE foo = ?foo AND bar = ?bar AND baz = ?foo"
Right ("SELECT * FROM users WHERE foo = ? AND bar = ? AND baz = ?","foo" :| ["bar","foo"])

>>> extractNames "SELECT foo FROM my_table WHERE (foo->'bar' ??| ?selectedTags);"
Right ("SELECT foo FROM my_table WHERE (foo->'bar' ?| ?);","selectedTags" :| [])

When the operator is not escaped, it's treated as a named parameter >>> extractNames "SELECT foo FROM my_table WHERE (foo->bar ?| ?selectedTags);" Left PostgreSQL named parameter error: Query contains an empty name: SELECT foo FROM my_table WHERE (foo->bar ?| ?selectedTags);

namesToRow

Arguments

:: forall m. WithNamedError m

=> NonEmpty Name

List of the names used in query

-> [NamedParam]

List of the named parameters

-> m (NonEmpty Action)

Returns the list of values to use in query by given list of Names. Throws PgNamedError if any named parameter is not specified.

Database querying functions with named parameters

queryNamed

Arguments

:: (MonadIO m, WithNamedError m, FromRow res)

=> Connection

Database connection

-> Query

Query with named parameters inside

-> [NamedParam]

The list of named parameters to be used in the query

-> m [res]

Resulting rows

Queries the database with a given query and named parameters and expects a list of rows in return.

queryNamed dbConnection [sql|
    SELECT id
    FROM table
    WHERE foo = ?foo
|] [ "foo" =? "bar" ]

queryWithNamed

Arguments

:: (MonadIO m, WithNamedError m)

=> RowParser res

Custom defined row parser

-> Connection

Database connection

-> Query

Query with named parameters inside

-> [NamedParam]

The list of named parameters to be used in the query

-> m [res]

Resulting rows

Queries the database with a given row parser, Query, and named parameters and expects a list of rows in return.

Sometimes there are multiple ways to parse tuples returned by PostgreSQL into the same data type. However, it's not possible to implement multiple intances of the FromRow typeclass (or any other typeclass).

Consider the following data type:

data Person = Person
    { personName :: !Text
    , personAge  :: !(Maybe Int)
    }

We might want to parse values of the Person data type in two ways:

1. Default by parsing all fields.
2. Parse only name and age to Nothing.

If you want to have multiple instances, you need to create newtype for each case. However, in some cases it might not be convenient to deal with newtypes around large data types. So you can implement custom RowParser and use it with queryWithNamed.

queryWithNamed rowParser dbConnection [sql|
    SELECT id
    FROM table
    WHERE foo = ?foo
|] [ "foo" =? "bar" ]

executeNamed

Arguments

:: (MonadIO m, WithNamedError m)

=> Connection

Database connection

-> Query

Query with named parameters inside

-> [NamedParam]

The list of named parameters to be used in the query

-> m Int64

Number of the rows affected by the given query

Modifies the database with a given query and named parameters and expects a number of the rows affected.

executeNamed dbConnection [sql|
    UPDATE table
    SET foo = 'bar'
    WHERE id = ?id
|] [ "id" =? someId ]

executeNamed_

Arguments

:: (MonadIO m, WithNamedError m)

=> Connection

Database connection

-> Query

Query with named parameters inside

-> [NamedParam]

The list of named parameters to be used in the query

-> m ()

Same as executeNamed but discard the nubmer of rows affected by the given query. This function is useful when you're not interested in this number.

Internal utils

withNamedArgs :: WithNamedError m => Query -> [NamedParam] -> m (Query, NonEmpty Action)

Helper to use named parameters. Use it to implement named wrappers around functions from postgresql-simple library. If you think that the function is useful, consider opening feature request to the postgresql-simple-named library:

• https://github.com/Holmusk/postgresql-simple-named/issues