postgresqlhaskellhasql

Hasql: Error from variable substitution in 'SET' statement

I'm trying to write a parameterized statement with Hasql to set a variable in PostgreSQL.

import qualified Hasql.Encoders as E
import qualified Hasql.Decoders as D

setOrganization :: Query Int64 ()
setOrganization = statement sql (E.value E.int8) D.unit False
  where
    sql = "set my_session.organization_id = $1"

The result from the above is:

ResultError (ServerError "42601" "syntax error at or near \"$1\"" Nothing Nothing)

Adding single quotes e.g.

    sql = "set my_session.organization_id = '$1'"

Gives this result when I run a query using the variable:

ResultError (ServerError "22P02" "invalid input syntax for integer: \"$1\"" Nothing Nothing)

Which does make sense since the organization_id is a bigint / int8.

Hard-coding the $1 value in either format works. I've tried different Hasql types e.g. E.text and E.unknown and that does not work.

Update: Using the more primitive execParams function from postgresql-libpq.

execParams c "SET my_session.organization_id = '$1'" [Just (Oid 20, "1",Text)] Text

Unquoted variable gives FatalError result. Single-quoted variable gives CommandOk, but is wrong type (not bigint) for later queries.

Solution

  • The SET command cannot be used with a prepared statement. This is a limitation of PostgreSQL. A prepared statement is a statement with optional parameters that can be executed several times with varying parameter values. All statements with parameters are prepared statements in PostgreSQL, regardless if you execute them only once or if you give them a name for reuse.

    You'll have to construct a query string with a constant value and execute that.

    Alternatively, you could write a function in PostgreSQL that runs the set command for you with dynamic SQL and call that function in a prepared SELECT statement with a parameter.