Search code examples
postgresqlhaskellcube

Using cubes in postgresql-simple results in error of "syntax error in multi-row template"

How are you meant to use cubes with postgresql-simple?

At the moment, I'm doing things like

query conn "SELECT thing FROM table WHERE coord <@ cube('?, ?') ORDER BY cube_distance(coord, cube('?')) ASC" 
      (In [a, b, c], In [d, e, f], In [g, h, i])

which works fine, but feels like a misuse of In. It also fails in the case of executeMany. For example

executeMany conn "INSERT INTO table(thing, coord) VALUES(?, cube('?'))" lst

where lst :: [(String, In [Float])] fails with a syntax error in multi-row template. The equivalent, but less efficient

mapM_ (execute conn "INSERT INTO table(thing, coord) VALUES(?, cube('?'))") lst

works fine, so I'm assuming this has to do with how executeMany organizes the arguments for insertion.

However, the docs make no mention of a Cube type, and I can't find an example of its use, so I'm not really sure what I'm supposed to do.

Solution

  • You can use this mini-library I wrote. Once you include that, you can do 

    execute "INSERT INTO table(cube_field, name) VALUES(?, ?) RETURNING id" (Cube [[1, 2, 3]], "Testing")

    and, more importantly,

    executeMany "INSERT INTO table(cube_field, name) VALUES(?, ?) RETURNING id"
            [(Cube [[1, 2]], "One"), (Cube [[3, 4]], "Two"), (Cube [[5, 6]], "Three")]

    There's no fromField instance, so you can't select Cubes at this point. I won't be adding that because it looks non-trivial, and I don't need it for my use-case, but patches are definitely welcome.