如何在PostgreSQL中断言子查询的预期结果行数？

针对你遇到的「既要合并数据库操作减少往返，又要确保子查询返回恰好1条结果」的需求，以下是几种可行的客户端层面方案：

方案1：合并SQL+客户端结果断言 #

将查询与插入合并为单条SQL，同时在子查询中统计返回行数，最后在客户端验证行数是否符合预期：

SQL语句 #

WITH foo_query AS (
  SELECT id, COUNT(*) OVER () AS total_rows
  FROM foo
  WHERE nid = 'BAR'
)
INSERT INTO bar (foo_id)
SELECT id FROM foo_query
RETURNING (SELECT total_rows FROM foo_query LIMIT 1) AS foo_result_count;

客户端代码（Slonik） #

const fooResultCount = await pool.oneFirst(sql`
  WITH foo_query AS (
    SELECT id, COUNT(*) OVER () AS total_rows
    FROM foo
    WHERE nid = 'BAR'
  )
  INSERT INTO bar (foo_id)
  SELECT id FROM foo_query
  RETURNING (SELECT total_rows FROM foo_query LIMIT 1) AS foo_result_count;
`);

if (fooResultCount !== 1) {
  throw new Error(`foo查询结果异常：预期1条，实际返回${fooResultCount}条`);
}

这种方式把两次数据库操作合并为一次，同时在客户端完成断言，避免了额外的网络往返。

方案2：封装可复用的Slonik函数 #

把合并查询、断言逻辑封装成通用函数，简化业务代码的调用：

async function insertBarWithValidFoo(pool, targetNid) {
  const result = await pool.one(sql`
    WITH foo_query AS (
      SELECT id, COUNT(*) OVER () AS total_rows
      FROM foo
      WHERE nid = ${targetNid}
    )
    INSERT INTO bar (foo_id)
    SELECT id FROM foo_query
    RETURNING (SELECT total_rows FROM foo_query LIMIT 1) AS foo_result_count, id AS bar_id;
  `);

  if (result.foo_result_count !== 1) {
    throw new Error(`无法插入bar：foo查询返回${result.foo_result_count}条结果（预期1条）`);
  }

  return result.bar_id;
}

// 调用示例
await insertBarWithValidFoo(pool, 'BAR');

方案3：SQL匿名块+客户端异常捕获 #

利用PostgreSQL的匿名块实现断言逻辑，客户端只需捕获SQL抛出的异常即可：

SQL匿名块 #

DO $$
DECLARE
  target_foo_id foo.id%TYPE;
  result_row_count INTEGER;
BEGIN
  SELECT id INTO target_foo_id FROM foo WHERE nid = 'BAR';
  GET DIAGNOSTICS result_row_count = ROW_COUNT;
  
  IF result_row_count != 1 THEN
    RAISE EXCEPTION 'foo查询结果异常：预期1条，实际%条', result_row_count;
  END IF;
  
  INSERT INTO bar (foo_id) VALUES (target_foo_id);
END $$;

客户端代码（Slonik） #

try {
  await pool.query(sql`
    DO $$
    DECLARE
      target_foo_id foo.id%TYPE;
      result_row_count INTEGER;
    BEGIN
      SELECT id INTO target_foo_id FROM foo WHERE nid = 'BAR';
      GET DIAGNOSTICS result_row_count = ROW_COUNT;
      
      IF result_row_count != 1 THEN
        RAISE EXCEPTION 'foo查询结果异常：预期1条，实际%条', result_row_count;
      END IF;
      
      INSERT INTO bar (foo_id) VALUES (target_foo_id);
    END $$;
  `);
} catch (err) {
  // 针对性处理断言失败的错误
  if (err.message.includes('foo查询结果异常')) {
    throw new Error('无法完成插入：目标foo记录不存在或存在多条');
  }
  // 抛出其他数据库错误
  throw err;
}

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