"cannot insert multiple commands into a prepared statement" 如何从 KOA 服务中的 PostgreSQL 函数返回游标结果

Question

我使用PostgreSQL FUNCTION返回结果集。我在获取Node.js/KOA api以使用参数正确调用它时遇到了问题。我可以让它在没有参数的情况下工作，但参数会将它变成一个准备好的语句。准备好的语句不喜欢多个命令。

以下是我使用的数据库对象和配置：

const { Pool } = require('pg');
const config = require('../configuration');
exports.pool = this.pool;
exports.start = async function() {
const host = config.get("PGHOST");
const user = config.get("PGUSER");
const port = config.get("PGPORT");
const password = config.get("PGPASSWORD");
const database = config.get("PGDATABASE");
this.pool = new Pool({ user, host, database, password, port });
console.log('Postgres database pool started.');
};
exports.close = async function() {
await this.pool.end;
};
exports.query = async function(query, data) {
let rs = await this.pool.query(query, data);
return rs;
};

这是我的KOA服务(它使用pg.Pool节点模块，我认为我的问题在哪里…(：

let getFilteredDevelopers = async (developerId, firstName, lastName) => {
let query = { 
text: `
BEGIN;
SELECT ks_get_filtered_developers($1, $2, $3);
FETCH ALL IN "ks_developers_cursor";
COMMIT;
`,
values: [ developerId, firstName, lastName  ] 
};
try {
let result = await database.query(query);
return result[2].rows;
} catch (error) {
return `Failed to fetch developers.`;
}
};

这种方法在没有参数的情况下运行良好。但当我将参数添加到koa-pg调用时，它会抛出错误："无法将多个命令插入到准备好的语句中"。

以下是我的功能：

CREATE OR REPLACE FUNCTION ks_get_filtered_developers (
p_developer_id NUMERIC,
p_first_name TEXT,
p_last_name TEXT
) RETURNS refcursor AS
$$
DECLARE
v_query TEXT = '';
v_where_clause TEXT = '';
v_developers_cursor refcursor = 'ks_developers_cursor';
BEGIN
IF (p_developer_id IS NOT NULL) THEN
v_where_clause = v_where_clause || FORMAT(' AND d.developer_id = %s ', p_developer_id);
END IF;
v_query = '
SELECT d.developer_id AS id, d.* 
FROM ks_developers d
WHERE 1=1
' || v_where_clause || '
ORDER BY d.developer_id
';
OPEN v_developers_cursor FOR 
EXECUTE v_query;
RETURN v_developers_cursor;
END;
$$
LANGUAGE plpgsql;

我如何以适当的方式实现这一点？我错过了什么或误解了什么？

Answer 1

问题是我不了解如何使用pg节点模块。基本上，我试图把所有东西都塞进一个查询中，因为我认为我必须这样做；"助手"；数据库模块中的查询，并且它很窄。我把它和pg池查询函数名搞混了。我朝自己的脚开枪。

这篇文章向我展示了如何对一个事务执行多个语句：

https://node-postgres.com/features/transactions

因此，为了快速解决这个问题，我将池暴露给自己，并开始查询池，然后释放连接。这允许多个准备好的语句和操作。

以下是我将我的koa修改为：

let getFilteredDevelopers = async (developerId, firstName, lastName) => {
const client = await database.pool.connect();
try {
await client.query('BEGIN');
const selectQuery = `SELECT ks_get_filtered_developers($1, $2, $3)`;
await client.query(selectQuery, [ developerId, firstName, lastName ]);
const fetchQuery = `FETCH ALL IN "ks_developers_cursor"`;
const result = await client.query(fetchQuery);
client.query('COMMIT');
return result.rows;
} catch (error) {
client.query('ROLLBACK');
return `Failed to fetch developers.`;
} finally {
client.release();
}
};

我将重构我的代码以更好地处理这个问题，但我想回答为什么这个代码不起作用，以及我误解了什么。