Executing a raw SQL query in Rails with an array parameter against PostgreSQL

First create a reusable composite type definition that describes an Array of Integers:

# Somewhere in the static context
IntegerArray = ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Array.new(ActiveModel::Type::Integer.new).freeze

Then use this type in queries:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY($1)"
params = IntegerArray.serialize(user_ids)
ActiveRecord::Base.connection.exec_query(query, "MyLogLabel", [[nil, params]])

Note that there's no need to use PG::TextEncoder directly nor PG::Connection#exec_params. There's still a need to use ActiveRecord::ConnectionAdapters::PostgreSQL since composite types are not backend-agnostic.

Not pretty, but it's possible to get a plain PG::Connection connection like so:

connection = ActiveRecord::Base.connection.instance_variable_get(:@connection)

Unfortunately, the pg gem doesn't very elegantly support arrays either, so this only gets you halfway there, and you have to do some rather verbose, manual encoding of the array:

user_ids = [1, 154, 31908]
query = "SELECT id FROM users WHERE id = ANY ($1)"
params = [PG::TextEncoder::Array.new.encode(user_ids)]
connection.exec_params(query, params)

Depending on how pretty you want to make this, you could monkeypatch ActiveRecord::ConnectionAdapters::PostgreSQLAdapter to expose @connection, or subclass it and configure Rails to use that subclass. Also, the pg gem supports customizing typecasting behavior, which would reduce some of the tedium.

Instead of using the array directly, you will have to convert it to a string if you want to use parameter bindings:

user_ids = [1,154,31908]
user_ids = user_ids.join(',')
query = "SELECT id FROM users WHERE id = ANY (ARRAY[$1])"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])

This results in the query SELECT id FROM users WHERE id = ANY (ARRAY[1,154,31908]).

If you are just goofing around, and trying out queries, you could also ditch bindings and use string interpolation directly:

query = "SELECT id FROM users WHERE id = ANY (ARRAY[#{user_ids.join(',')}])"

Keep in mind that this would open you up for SQL Injection and similar nasties, so I do not recommend this approach for anything that is accessible from the outside.

The following worked for me.

 user_ids = [1, 2, 3].to_s
 user_ids = user_ids.gsub("[", "{")
 user_ids = user_ids.gsub("]", "}")
 query = "SELECT id FROM users WHERE id = ANY ($1)"

 ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, user_ids]])