Instance of : ToRow (Values (Int, Text))

[delanoe]

Hello, I am trying this example in the documentation:

https://github.com/lpsmith/postgresql-simple/blob/14d00c22713aab7d55a81a81b47cc5bb7280398f/src/Database/PostgreSQL/Simple/Types.hs#L228

I need to specify the types , fine, but I get an error :

• No instance for (ToRow (Values (Int, Text)))
    arising from a use of ‘query’

I am wondering how I should declare this instance and I am guessing there is something wrong somewhere and unexpected since the example is documented.

Do you have an example please ?

Many thanks in advance for the help.

[lpsmith]

Does your example use a single ? parameter? Sounds like you need to wrap the Values type in an Only constructor.

[delanoe]

Indeed, you are right! Using the Only constructor, it compiles now. Many thanks.

[delanoe]

It compiles but I get an SQL error: "subquery should return one column"

Then I am trying to use the function renderQuery you gave here: https://github.com/lpsmith/postgresql-simple/issues/262

I can hardly use it to show the resulting query sent to the database. May you give an example how I could debug this please ?

Here is the function:

insertDocs2 :: Connection -> IO [DocId]                                                                                                                                                                
insertDocs2 conn = query conn [sql|                                                                                                                                                                    
    WITH input_rows(typename,user_id,parent_id,name,hyperdata) AS (                                                                                                                                    
       VALUES (?)                                                                                                                                                                                      
       )                                                                                                                                                                                               
    , ins AS (                                                                                                                                                                                         
       INSERT INTO nodes (typename,user_id,parent_id,name,hyperdata)                                                                                                                                   
       SELECT * FROM input_rows                                                                                                                                                                        
       ON CONFLICT ((hyperdata ->> 'doi')) DO NOTHING -- on unique index                                                                                                                               
       RETURNING id,hyperdata                                                                                                                                                                          
       )                                                                                                                                                                                               

    SELECT true AS source                     -- true for 'inserted'                                                                                                                                   
         , id                                                                                                                                                                                          
         , hyperdata ->> 'doi'  as doi                                                                                                                                                                 
    FROM   ins                                                                                                                                                                                         
    UNION  ALL                                                                                                                                                                                         
    SELECT false AS source                    -- false for 'selected'                                                                                                                                  
         , c.id                                                                                                                                                                                        
         , hyperdata ->> 'doi' as doi                                                                                                                                                                  
    FROM   input_rows                                                                                                                                                                                  
    JOIN   nodes c USING (hyperdata);         -- columns of unique index                                                                                                                               
           |] (Only $ Values [ "int4","int4","int4","text","jsonb"]-                                                                                                                                   
              [ (4 :: Int ,1 :: Int,452162 :: Int, DT.pack "name", DT.pack "{}")]                                                                                                                      
              )          

Many thanks in advance.

[lpsmith]

Yes, renderQuery would not be very useful to you. Basicall, what you would want to do is replace query with formatQuery.

[delanoe]

Indeed. Now I better understand how to use formatQuery. I have solved my issue, found the right query, and I do think I have now a great workflow to work with postgresql-simple. Many thanks for this library, for your answers. Best wishes for your projects.