如何使用node-postgres调用基于refcursor的PostgreSQL函数

核心实现前提 #

• 显式refcursor和会话事务强绑定，从开启事务、调用函数打开游标、拉取游标结果到提交事务，所有操作必须使用同一个从连接池取出的客户端连接，不能让连接池自动分配不同的连接执行拆分的查询，否则会出现游标不存在的报错
• 整体执行逻辑和你在psql交互界面的手动操作1:1对齐，不需要特殊的语法适配

完整可运行代码 #

const { Pool } = require('pg')

// 初始化数据库连接池
const pool = new Pool({
  host: '你的数据库地址',
  database: '你的业务库名',
  user: '你的数据库账号',
  password: '你的数据库密码',
  port: 5432
})

async function getFordBrandData() {
  // 从连接池取出固定的单个客户端，全程使用该连接执行所有操作
  const client = await pool.connect()
  try {
    // 对应psql里的BEGIN操作，开启事务
    await client.query('BEGIN')

    // 对应psql里调用showcars函数的步骤，传入自定义游标名，显式声明参数类型为refcursor避免类型匹配问题
    await client.query(
      'SELECT showcars($1::refcursor, $2::refcursor)',
      ['CarModels', 'CarSales']
    )

    // 对应psql里的FETCH ALL操作，分别拉取两个游标的全量结果
    const carModelsResult = await client.query('FETCH ALL IN "CarModels"')
    const carSalesResult = await client.query('FETCH ALL IN "CarSales"')

    // 对应psql里的COMMIT操作，提交事务
    await client.query('COMMIT')

    // 直接返回两组独立的查询结果即可
    return {
      carModels: carModelsResult.rows,
      carSales: carSalesResult.rows
    }
  } catch (error) {
    // 操作异常时回滚事务，避免事务挂起占用连接
    await client.query('ROLLBACK')
    throw error
  } finally {
    // 操作完成后将客户端释放回连接池
    client.release()
  }
}

// 调用示例
getFordBrandData().then(data => {
  console.log('福特车型列表：', data.carModels)
  console.log('福特销量数据：', data.carSales)
})

常见报错排查 #

• 报 cursor "CarModels" does not exist：基本是跨连接执行步骤导致的，比如开启事务用了连接A，调用函数用了连接B，拉取结果时又换了连接，游标实际存在于打开它的连接会话上，其他连接无法识别
• 报 cursor "carmodels" does not exist：是写FETCH语句时没有给游标名加双引号，PostgreSQL会将不带引号的标识符自动转为小写，和你传入的驼峰命名游标名无法匹配
• 查询结果为空：检查是不是在调用函数打开游标之前就提前执行了COMMIT，导致事务还没创建游标就被提交，游标被自动销毁

内容的提问来源于stack exchange，提问作者volume one