I need to search for id field from an array. I'm using the PG gem, so I'm thinking I should be using TextEncoder::Array.new.encode(myarray) to shove in the qualified list. The PostgreSQL structure is as follows:
SELECT id FROM tablename WHERE fieldname IN ('alpha', 'bravo', 'charlie')
So my starting array in Ruby would be ['alpha', 'charlie', 'bravo'], so this is my current syntax:
a = ['alpha', 'bravo', 'ccharlie']
b = $conn.exec_params( %Q{ SELECT id FROM tablename WHERE fieldname IN ( $1 ) }, [ PG::TextEncoder::Array.new.encode(a) ] )
puts b.inspect
exec_params obviously takes an array, even it's a single array element. But using PG::TextEncoder::Array.new.encode(a) results in the following structure:
"{alpha,bravo,charlie}"
...which is an object (.class). The improper result I'm getting is that there are no records found, which is false.
How can I place a Ruby array inside as the first PG parameter?
UPDATE:
I've been using:
...AND NOT (mytable.field = ANY ($6))
where $6 is this:
PG::TextEncoder::Array.new(elements_type: PG::TextEncoder::String.new)
in a PG::TypeMapByColumn.new instance. The Ruby array that's going in there, is valid, holds a value. It is, in this example, a one-element string in an array, to be used in my SQL statement. All is good, but it isn't working. It would help if I actually saw the SQL statement sent to the db. Too much is hidden here.
