Ruby PG gem uses array as parameter with exec_params

Question

I want to pass the value of a ruby array like this:

sql = "SELECT $1"
User.connection.raw_connection.exec_params(sql, [[1,2]])

This returns

PG::IndeterminateDatatype: ERROR:  could not determine data type of parameter $1

If I change sql

to "SELECT $1::int[]"

, I get PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "[1, 2]"

.

Is there a way to pass Ruby array in exec_params

and convert it to PostgreSQL array?

+3

Reed G. Law May 23 '15 at 5:50

2 answers

Benjie · Answer 1 · 2016-09-16T12:59:54+0000

You can use encoders for this:

raw_connection.exec(
  "select $1::int[]",
  [PG::TextEncoder::Array.new.encode([1, 2])]
)

Arup Rakshit · Answer 2 · 2015-05-23T06:11:15+0000

You need to do it like below:

params = [1, 2]
sql = params.size.times.map { |n| "$#{n+1}::INT" }.join(",")
User.connection.raw_connection.exec_params("SELECT #{sql}", params)