将占位符与副本和pg查询流一起使用

Question

我正在尝试将查询提取为csv文件。我尝试将copy与pg-query-stream一起使用来执行查询，但我遇到了以下错误：

error: bind message supplies 1 parameters, but prepared statement "" requires 0

当从查询中删除copy时，它与占位符一起工作也很好——如果我提供了一个具有copy且没有占位符的查询，它也很好。

const pgp = require('pg-promise')
const QueryStream = require('pg-query-stream')
query1 = "copy (select * from real_state WHERE town_code= $1 ) TO  '/tmp/file.csv'"
const qs = new QueryStream(query1, [22])
await db.stream(qs, s => {
// initiate streaming into the console:
s.pipe(JSONStream.stringify()).pipe(process.stdout)
}).then(data => {
}).catch(error => {
console.log('ERROR:', error)
})

---

query1 = "copy (select * from real_state WHERE town_code= $1 ) TO  '/tmp/file.csv'" ==> error
query2 = "copy (select * from real_state) TO  '/tmp/file.csv'" ==> It works
query3 = "select * from real_state WHERE town_code= $1" ==>  It works

Answer 1

COPY上下文中有一个限制，禁止您使用任何参数。

但是您可以使用pg-promise查询格式来绕过这个限制

const query = pgp.as.format('COPY(SELECT * FROM real_state WHERE town_code = $1) TO $2',
[22, '/tmp/file.csv']);

Answer 2

我认为您将两个特性混合在一起。

pg查询流将行作为流返回，不需要在select中使用copy。只需使用一个简单的选择，然后将结果管道传输到文件流。

const fs = require('fs')
const { Pool } = require('pg')
const QueryStream = require('pg-query-stream')
const query = new QueryStream("select * from real_state WHERE town_code= $1", [22]
const stream = pool.query(query)
const fileStream = fs.createReadStream('/tmp/file.csv')
fileStream.pipe(stream)

如果要使用复制，请使用pg复制流：https://github.com/brianc/node-pg-copy-streams

const fs = require('fs')
const { Pool } = require('pg')
const copyFrom = require('pg-copy-streams').from
const stream = db.query(copyFrom('COPY real_state FROM stdin WHERE town_code= $1', [22])
const fileStream = fs.createReadStream('/tmp/file.csv')
fileStream.pipe(stream)