insert array into postgresql map prepared statement

Question

I have this code :

require 'pg'
a = ["bla","foo","bar","test","wow"]

begin
con = PG.connect :dbname => 'base', :user => 'thatsme'

a.map do |e|
    con.prepare('statement1','INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)')
    con.exec_params('statement1', ["#{e}", '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
end

This causes an error, which is

ERROR:  syntax error at or near "statement1"

I don't get it. I'm missing something...


Show source
| ruby   | postgresql   2017-01-01 10:01 2 Answers

Answers ( 2 )

  1. 2017-01-01 11:01

    The exec_params method doesn't take a prepared statement name, that's not a supported argument.

    You're probably intending to use the send_query_prepared method which does have that argument.

    Here's a refactored version of your code:

    a.each_with_index do |r, i|
      con.prepare("statement_#{i}","INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)")
      con.exec_prepared("statement_#{i}", [r, '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
    end
    

    You should use each instead of map if you're not concerned with the results, as map creates a temporary array of the rewritten entries. Additionally each_with_index avoids manually manipulating i.

  2. 2017-01-01 11:01

    Here's the solution (if this could help someone) :

    con = PG.connect :dbname => 'base', :user => 'thatsme'
    i = 1;
    
    a.map do |r|
        con.prepare("statement_#{i}","INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)")
        con.exec_prepared("statement_#{i}", [r.to_s, '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
        i += 1
    end
    
◀ Go back