Groovy sql.rows returns org.postgresql.util.PSQLException: No hstore extension installed

Question

I am using Groovy Sql in Grails with named parameters to get results from a Postgres DB. My statement is generated dynamically, i.e. concatenated to become the final statement, with the params being added to a map as I go along.

sqlWhere += " AND bar = :namedParam1"
paramsMap.namedParam1 = "blah"

For readability, I am using the groovy string syntax which allows me to write my sql statement over multiple lines, like this:

sql = """
      SELECT *
      FROM foo
      WHERE 1=1
      ${sqlWhere}
      """

The expression is evaluated as a string containing the linebreaks as \n:

SELECT *\n ...

This is not a problem when I pass params like this

results = sql.rows(sqlString, paramsMap)

but it does become one if paramsMap is empty (which happens since AND bar = :namedParam1 is not always concatenated into the query). I then get an error

org.postgresql.util.PSQLException: No hstore extension installed  

which does not really seem to relate to the true nature of the problem. I have for now fixed this with an if...else

if (sqlQuery.params.size() > 0) {
    results = sql.rows(sqlString, paramsMap)
} else {
    results = sql.rows(sqlString.replace('\n',' '))
}

But this seems a bit weird (especially since it does not work if I use the replace in the if-branch as well).

My question is: why do I really get this error message and is there a better way to prevent it from occuring?

Answer 1

It's certainly a bug in groovy.sql.SQL implementation. The method rows() can't deal with an empty map passed as params. As a workaround, you can test for it and pass an empty list instead.

def paramsMap = [:]
...
if (paramsMap.isEmpty())
    paramsMap= []

Issue created at https://issues.apache.org/jira/browse/GROOVY-8082