具有命名参数的“ WHERE col IN” [英] `WHERE col IN` with named parameters

问题描述

在有关如何使用带有IN子句的参数化查询，语法如下：

In the example on how to use parameterized queries with an IN clause, the syntax is as follows:

const data = [1, 'two', 3, 'four'];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
    .then(data => {
...

我似乎无法将其用于命名参数：

I can't seem to get this to work for named parameters:

db.manyOrNone('SELECT widget FROM widgets WHERE id IN ($(ids:list))', 
              { ids: [1, 2, 3] })

（我正在使用：list ，因为它似乎可以与<$ c一起可互换 $ c>：csv ）。
我尝试了各种组合，例如：

(I'm using :list since it appears to be interchangeable with :csv). I've tried various combinations like:

• （$（ids）：list）（或附近的语法错误）


• （$ {ids ：list}）（ $或附近的语法错误）

• ($(ids):list) (syntax error at or near ":")
• (${ids:list}) (syntax error at or near "$")

我一直收到无效的语法错误是否支持Postgres？或者我必须像实例中一样将参数作为数组传递并引用它们？

I keep getting invalid syntax errors from Postgres. Is this supported? Or do I have to pass the parameters as an array and reference them like in the example?

版本：

• Postgres：9.5.7


• pg承诺：5.7.1

更新
我将参数更改为 ... WHERE widget IN（$（ids：csv））... 并且现在可以正常使用，因此：list 和：csv 似乎不是 可互换。

UPDATE I changed my param to be ... WHERE widget IN ($(ids:csv))... and it works now, So it seems that :list and :csv are not interchangeable.

推荐答案

问题是您使用的是 pg-promise 。当前版本是8.4.4，您正在使用5.7.1

The problem is that you are using an ancient version of pg-promise. The current version is 8.4.4, and you are using 5.7.1

别名：list 与：csv ，但添加的时间远远晚于v5.7.1

Alias :list is fully interchangeable with :csv, but it was added much later than v5.7.1

升级到最新版本，它将也可以与：list 一起使用。

Upgrade to the latest version, and it will work with :list as well.

还假定当前文档中的所有内容都相应地引用了当前文档。库的版本。

Also assume that everything that's in the current documentation refers accordingly, to the current version of the library.

这篇关于具有命名参数的“ WHERE col IN”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！