Search code examples
sqlrubypg

select with multiple values in an insert statement using PG gem with Ruby

Can you guys help me out with this. If I use this db query directly in the postgresql database it works fine, however when using the PG gem in Ruby it expects me to have 6 values in the array, however I want to get those values from a SELECT statement.

The error message that I'm getting is: 

ERROR:  bind message supplies 1 parameters, but prepared statement "statement2" requires 6 (PG::ProtocolViolation)

Here's the code in Ruby using PG gem.

conn.prepare('statement2', 'INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) VALUES ($1, $2, $3, $4, $5, $6)')
conn.exec_prepared('statement2', ["SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = 192.168.0.1"])

The actual SQL that works looks like this:

INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = '192.168.0.1';
Solution 
  • res = conn.exec("SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = 192.168.0.1").first

conn.prepare('statement2', 'INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) VALUES ($1, $2, $3, $4, $5, $6)')
conn.exec_prepared('statement2', res.values)

    or simply run the query as is:

    conn.exec("INSERT INTO client_results (client_type, distance, hostname, serial_number, rssi, txrate) SELECT client_type, distance_in_ft, hostname, serial_number, -65, 224 FROM test_clients WHERE eth_ipv4 = '192.168.0.1'")