[Haskell-cafe] Handling Postgresql array types

[Tom Ellis]

On Sat, Dec 27, 2014 at 11:23:36AM +0000, Tom Ellis wrote:
> > PS. I started looking into Opaleye as a possible DSL and would appreciate 
> > any help you can give me in figuring out how to represent arrays in that. 
> >  I am assuming I need to do something with queryRunnerColumn but I was not 
> > able to understand the example.
> 
> This is indeed somewhat fiddly to do because it requires fiddling with the
> implementation of postgresql-simple instances.  Unfortunately that library
> does not provide us with enough primitives to do this directly.

OK, I just pushed a patch to GitHub

    https://github.com/tomjaguarpaw/haskell-opaleye

Now you can do the below.  You can't actually do anything with your arrays
yet except just read them from tables and return them from `runQuery` but
let me know what functionality you want and I will endevour to support it.


import           Database.PostgreSQL.Simple (execute_, ConnectInfo(..), connect)
import           Data.String (fromString)
import           Opaleye

connectInfo :: ConnectInfo
connectInfo =  ConnectInfo { connectHost = "localhost"
                           , connectPort = 25433      
                           , connectUser = "tom"
                           , connectPassword = "tom"
                           , connectDatabase = "opaleye_test" }

table :: Table (Column (PGArray (PGArray PGInt4)))
               (Column (PGArray (PGArray PGInt4)))
table = Table "arraytable" (required "colname")

query :: Query (Column (PGArray (PGArray PGInt4)))
query = queryTable table

main :: IO ()
main = do
  conn <- connect connectInfo

  let q = execute_ conn . fromString

  q "DROP TABLE IF EXISTS arraytable"
  q "CREATE TABLE arraytable (colname integer[][])"
  q "INSERT INTO arraytable VALUES ('{{1,2}, {3,4}}'), ('{{5,6}, {7,8}}')"

  results' <- runQuery conn query :: IO [[[Int]]]
  print results'

-- ghci> main
-- [[[1,2],[3,4]],[[5,6],[7,8]]]