Ruby PG Gem 使用 TextEncoder::Array for SELECT WHERE fieldname IN exec_params [英] Ruby PG Gem Using TextEncoder::Array for SELECT WHERE fieldname IN exec_params

问题描述

我需要从数组中搜索 id 字段.我正在使用 PG gem，所以我想我应该使用 TextEncoder::Array.new.encode(myarray) 推入合格列表.PostgreSQL 结构如下:

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')

所以我在 Ruby 中的起始数组将是 ['alpha', 'charlie', 'bravo']，所以这是我当前的语法:

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 显然需要一个数组，即使它是单个数组元素.但是使用 PG::TextEncoder::Array.new.encode(a) 会产生以下结构:

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}"

...这是一个对象(.class).我得到的错误结果是没有找到记录，这是错误的.

...which is an object (.class). The improper result I'm getting is that there are no records found, which is false.

如何将 Ruby 数组作为第一个 PG 参数放入其中?

How can I place a Ruby array inside as the first PG parameter?

更新:

我一直在使用:

...AND NOT (mytable.field = ANY ($6))

其中 $6 是这个:

PG::TextEncoder::Array.new(elements_type: PG::TextEncoder::String.new)

在 PG::TypeMapByColumn.new 实例中.进入那里的 Ruby 数组是有效的，包含一个值.在本例中，它是一个数组中的单元素字符串，用于我的 SQL 语句.一切都很好，但它不起作用.如果我真的看到发送到 db 的 SQL 语句会有所帮助.这里隐藏了太多东西.

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.

推荐答案

我找到了答案:...AND NOT hashtag = ANY ($1)... 其中 $1 是正确的 Ruby 数组.PG gem 人员实际上并不支持它.

I found the answer: ...AND NOT hashtag = ANY ($1)... where $1 is a proper Ruby array. It's not actually supported by the PG gem people.

这篇关于Ruby PG Gem 使用 TextEncoder::Array for SELECT WHERE fieldname IN exec_params的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！