Search code examples
rubypostgresqlpg

adding array as parameter to sql query in ruby

I have an array

ziparray = ["95626", "95645", "95837"]

I want to pass this to my sql query ,

sql = "SELECT * from table_name WHERE code in ($1);"
res1 = conn.exec(sql, [ziparray])

It does work for single values.

I am using pg gem and connecting to database using 

conn = PG.connect()

I am using postgres and it doesn't take double quotes . I am assuming that to be the problem. How to achieve this.

Update

I could convert to desired string using 

str = "'"
str << ziparray.join("','")
str << "'"
#print str

But I guess the problem is passing of multiple parameters.

this works -

res1 = conn.exec(fipscodesql, ['95626'])

But not this 

res1 = conn.exec(fipscodesql, ['95626', '95625'])

and this is exactly what I did when I converted the array to string. I guess this is not the right way to use parameters. is there any other way.

Solution

  • As others said, you can't parametrise a whole array. Use this instead:

    ziparray = ["95626", "95645", "95837"]
zip_placeholders = ziparray.map.with_index(1) { |_, i| "$#{i}" }.join(', ')
sql = "SELECT * from table_name WHERE code in (#{zip_placeholders});"
# => "SELECT * from table_name WHERE code in ($1, $2, $3)"

    Then you can use the normal parameter binding.