如何在PostgreSQL存储过程/函数中返回多表结果并在NestJS中获取？

一、PostgreSQL端实现 #

1. 使用函数返回多结果集 #

PostgreSQL函数可通过多次RETURN QUERY语句返回多个独立结果集。假设存在users和orders两张表，示例函数如下：

CREATE OR REPLACE FUNCTION get_multi_results()
RETURNS SETOF record AS $$
BEGIN
    -- 返回第一个结果集：users表前10条数据
    RETURN QUERY SELECT id, name, email FROM users LIMIT 10;
    -- 返回第二个结果集：orders表前10条数据
    RETURN QUERY SELECT id, user_id, total_amount, created_at FROM orders LIMIT 10;
END;
$$ LANGUAGE plpgsql;

2. 使用存储过程返回多结果集（PostgreSQL 11+） #

存储过程支持通过REF CURSOR输出参数返回多个结果集，每个游标对应一张表的查询结果：

CREATE OR REPLACE PROCEDURE get_multi_results_proc(
    OUT user_cursor refcursor,
    OUT order_cursor refcursor
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 打开游标指向users表数据
    OPEN user_cursor FOR SELECT id, name, email FROM users LIMIT 10;
    -- 打开游标指向orders表数据
    OPEN order_cursor FOR SELECT id, user_id, total_amount, created_at FROM orders LIMIT 10;
END;
$$;

调用存储过程需在事务上下文内执行：

BEGIN;
CALL get_multi_results_proc('user_cur', 'order_cur');
FETCH ALL FROM user_cur;
FETCH ALL FROM order_cur;
COMMIT;

二、NestJS端获取数据 #

1. 使用原生pg模块 #

直接通过pg包操作数据库，可直接处理多结果集：

安装依赖

npm install pg pg-pool

实现代码

import { Injectable } from '@nestjs/common';
import { Pool } from 'pg';

@Injectable()
export class DataService {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      user: '你的数据库用户',
      host: 'localhost',
      database: '你的数据库名',
      password: '你的数据库密码',
      port: 5432,
    });
  }

  async getMultiResults(): Promise<{ users: any[], orders: any[] }> {
    const client = await this.pool.connect();
    try {
      // 调用存储过程方式（推荐，结果集更可控）
      await client.query('BEGIN');
      await client.query('CALL get_multi_results_proc($1, $2)', ['user_cur', 'order_cur']);
      const usersResult = await client.query('FETCH ALL FROM user_cur');
      const ordersResult = await client.query('FETCH ALL FROM order_cur');
      await client.query('COMMIT');

      return {
        users: usersResult.rows,
        orders: ordersResult.rows,
      };

      // 若使用函数方式，需读取底层结果集数组
      // const result = await client.query('SELECT * FROM get_multi_results()');
      // return {
      //   users: result.rows,
      //   orders: result._results[1].rows,
      // };
    } finally {
      client.release();
    }
  }
}

2. 使用TypeORM #

通过TypeORM操作时，需借助底层pg客户端处理多结果集：

安装依赖

npm install @nestjs/typeorm typeorm pg

实现代码

import { Injectable } from '@nestjs/common';
import { InjectEntityManager } from '@nestjs/typeorm';
import { EntityManager } from 'typeorm';

@Injectable()
export class DataService {
  constructor(@InjectEntityManager() private entityManager: EntityManager) {}

  async getMultiResults(): Promise<{ users: any[], orders: any[] }> {
    const queryRunner = this.entityManager.connection.createQueryRunner();
    await queryRunner.connect();
    try {
      await queryRunner.query('BEGIN');
      await queryRunner.query('CALL get_multi_results_proc($1, $2)', ['user_cur', 'order_cur']);
      const users = await queryRunner.query('FETCH ALL FROM user_cur');
      const orders = await queryRunner.query('FETCH ALL FROM order_cur');
      await queryRunner.query('COMMIT');

      return { users, orders };
    } finally {
      await queryRunner.release();
    }
  }
}

三、注意事项 #

• 游标方式必须在事务上下文内执行，游标仅在事务有效期内有效。
• 生产环境建议定义DTO类映射返回数据，避免使用any类型，提升代码可维护性。
• 需确保数据库连接/查询 runner 被正确释放，防止连接泄漏。

内容的提问来源于stack exchange，提问作者deepak samuel