I want to create a "prepared statement" in postgres using the node-postgres module. I want to create it without binding it to parameters because the binding will take place in a loop.
我想使用node-postgres模块在postgres中创建一个“预备语句”。我想创建它而不将其绑定到参数,因为绑定将在循环中进行。
In the documentation i read :
在我阅读的文档中:
query(object config, optional function callback) : Query
If _text_ and _name_ are provided within the config, the query will result in the creation of a prepared statement.
I tried
client.query({"name":"mystatement", "text":"select id from mytable where id=$1"});
but when I try passing only the text & name keys in the config object, I get an exception :
但是当我尝试只传递配置对象中的文本和名称键时,我得到一个例外:
(translated) message is binding 0 parameters but the prepared statement expects 1
(已翻译)消息绑定0参数但预备语句需要1
Is there something I am missing ? How do you create/prepare a statement without binding it to specific value in order to avoid re-preparing the statement in every step of a loop ?
有什么我想念的吗?如何在不将其绑定到特定值的情况下创建/准备语句,以避免在循环的每个步骤中重新准备语句?
3 个解决方案
#1
10
I just found an answer on this issue by the author of node-postgres.
我刚刚在node-postgres的作者那里找到了关于这个问题的答案。
With node-postgres the first time you issue a named query it is parsed, bound, and executed all at once. Every subsequent query issued on the same connection with the same name will automatically skip the "parse" step and only rebind and execute the already planned query.
使用node-postgres第一次发出命名查询时,它会立即被解析,绑定和执行。在具有相同名称的同一连接上发出的每个后续查询将自动跳过“解析”步骤,并仅重新绑定并执行已计划的查询。
Currently node-postgres does not support a way to create a named, prepared query and not execute the query. This feature is supported within libpq and the client/server protocol (used by the pure javascript bindings), but I've not directly exposed it in the API. I thought it would add complexity to the API without any real benefit. Since named statements are bound to the client in which they are created, if the client is disconnected and reconnected or a different client is returned from the client pool, the named statement will no longer work (it requires a re-parsing).
目前,node-postgres不支持创建命名的,准备好的查询而不执行查询的方法。 libpq和客户端/服务器协议(纯javascript绑定使用)支持此功能,但我没有直接在API中公开它。我认为这会增加API的复杂性而没有任何实际好处。由于命名语句绑定到创建它们的客户端,如果客户端断开连接并重新连接或从客户端池返回不同的客户端,则命名语句将不再起作用(它需要重新解析)。
#2
2
Update: Reading your question again, here's what I believe you need to do. You need to pass a "value" array as well.
更新:再次阅读您的问题,这是我认为您需要做的事情。您还需要传递“值”数组。
Just to clarify; where you would normally "prepare" your query, just prepare the object you pass to it, without the value array. Then where you would normally "execute" your query, set the value array in the object and pass it to the query. If it's the first time, the driver will do the actual prepare for you the first time around, and simple do binding and execution for the rest of the iteration.
只是为了澄清;你通常会“准备”你的查询,只需准备你传递给它的对象,而不是值数组。然后,您通常会“执行”您的查询,在对象中设置值数组并将其传递给查询。如果这是第一次,驱动程序将在第一次为您做实际的准备,并且简单地对迭代的其余部分进行绑定和执行。
#3
0
You can use pg-prepared for that:
您可以使用pg-prepared为此:
var prep = require('pg-prepared')
// First prepare statement without binding parameters
var item = prep('select id from mytable where id=${id}')
// Then execute the query and bind parameters in loop
for (i in [1,2,3]) {
client.query(item({id: i}), function(err, result) {...})
}
#1
10
I just found an answer on this issue by the author of node-postgres.
我刚刚在node-postgres的作者那里找到了关于这个问题的答案。
With node-postgres the first time you issue a named query it is parsed, bound, and executed all at once. Every subsequent query issued on the same connection with the same name will automatically skip the "parse" step and only rebind and execute the already planned query.
使用node-postgres第一次发出命名查询时,它会立即被解析,绑定和执行。在具有相同名称的同一连接上发出的每个后续查询将自动跳过“解析”步骤,并仅重新绑定并执行已计划的查询。
Currently node-postgres does not support a way to create a named, prepared query and not execute the query. This feature is supported within libpq and the client/server protocol (used by the pure javascript bindings), but I've not directly exposed it in the API. I thought it would add complexity to the API without any real benefit. Since named statements are bound to the client in which they are created, if the client is disconnected and reconnected or a different client is returned from the client pool, the named statement will no longer work (it requires a re-parsing).
目前,node-postgres不支持创建命名的,准备好的查询而不执行查询的方法。 libpq和客户端/服务器协议(纯javascript绑定使用)支持此功能,但我没有直接在API中公开它。我认为这会增加API的复杂性而没有任何实际好处。由于命名语句绑定到创建它们的客户端,如果客户端断开连接并重新连接或从客户端池返回不同的客户端,则命名语句将不再起作用(它需要重新解析)。
#2
2
Update: Reading your question again, here's what I believe you need to do. You need to pass a "value" array as well.
更新:再次阅读您的问题,这是我认为您需要做的事情。您还需要传递“值”数组。
Just to clarify; where you would normally "prepare" your query, just prepare the object you pass to it, without the value array. Then where you would normally "execute" your query, set the value array in the object and pass it to the query. If it's the first time, the driver will do the actual prepare for you the first time around, and simple do binding and execution for the rest of the iteration.
只是为了澄清;你通常会“准备”你的查询,只需准备你传递给它的对象,而不是值数组。然后,您通常会“执行”您的查询,在对象中设置值数组并将其传递给查询。如果这是第一次,驱动程序将在第一次为您做实际的准备,并且简单地对迭代的其余部分进行绑定和执行。
#3
0
You can use pg-prepared for that:
您可以使用pg-prepared为此:
var prep = require('pg-prepared')
// First prepare statement without binding parameters
var item = prep('select id from mytable where id=${id}')
// Then execute the query and bind parameters in loop
for (i in [1,2,3]) {
client.query(item({id: i}), function(err, result) {...})
}