[Haskell-cafe] Handling Postgresql array types

[info at rotnetix.com]

Thank you very much, that works great.  My Haskell, poor as it is, is still 
a lot better than may SQL so I am doing most of my array operations in 
Haskell.  The only functionality I currently use is array_length, slices 
(as in SELECT colname [1:2][1:1]) and WHERE colname = '{}'.

The other issue I had is once the array select is working is how to select 
a column with a special name... For some reason out DB designer decided 
that one of the columns should be named "column".  Which is a pain anyway 
but in normal SQL you can deal with that by selecting it fully qualified as 
in arrayTable.column but if I try that in opaleye, like:

table = Table "arraytable" (required "arraytable.column") 

the query complains:
*** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, 
sqlErrorMsg = "syntax error at or near \".\"", sqlErrorDetail = "", 
sqlErrorHint = ""}

Finally I am curious as to how easy it will be for me to create a different 
mapping for PGArray, so that instead of (PGArray (PGArray PGFloat8)) -> 
[[Double]] I can do (PGArray (PGArray Float8)) -> Matrix Double.  It is not 
a big deal to do the conversion later but if the library allows that kind 
of thing to be easily done it can make the code more readable.

Thanks again for the great support. 

On Saturday, December 27, 2014 11:09:57 PM UTC+11, Tom Ellis wrote:
>
> 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]]] 
> _______________________________________________ 
> Haskell-Cafe mailing list 
> Haskel... at haskell.org <javascript:> 
> http://www.haskell.org/mailman/listinfo/haskell-cafe 
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/haskell-cafe/attachments/20141227/6b607c90/attachment-0002.html>