PostgreSQL查询正常执行，作为存储过程执行时报错排查

错误根源 #

错误码42601是PostgreSQL的语法错误，提示“赋值源返回2列”，问题不在你标注的InvalidFiles代码段，而是出在InvalidFileCnt的定义上：

(
  ( SELECT COUNT(File_1) 
    FROM Table_1 
    WHERE Column_1 = A.Column_1
  ),

  ( SELECT COUNT(DISTINCT File_2) 
    FROM   Table_2 
    WHERE Column_2 = B.Column_2
  )
) AS InvalidFileCnt,

这段代码用逗号拼接了两个独立子查询，会返回2列数据，但你试图将这2列赋值给单个列别名InvalidFileCnt，违反了SQL的列定义规则——单个列别名只能对应1列数据。

解决方法 #

InvalidFileCnt的逻辑显然是总文件数减去有效文件数，直接修改为计算差值的表达式即可：

(
  (SELECT COUNT(File_1) FROM Table_1 WHERE Column_1 = A.Column_1)
  -
  (SELECT COUNT(DISTINCT File_2) FROM Table_2 WHERE Column_2 = B.Column_2)
) AS InvalidFileCnt,

另外，你标注的InvalidFiles代码段可以优化：外层已经关联了A.Column_1，子查询里的GROUP BY BB.Column_1是多余的，去掉后可避免不必要的分组操作：

( SELECT string_agg(BB.File_N, ',' ORDER BY BB.File_N) 
  FROM Table_1 BB 
  WHERE BB.Column_1 = A.Column_1 
    AND BB.File_2 NOT IN (
      SELECT COALESCE(File_2, 0) 
      FROM Table_2 Vld 
      JOIN Table_3 Grp ON Grp.Column_2 = Vld.Column_2 
      WHERE Grp.Column_1 = A.Column_1
    )
) AS InvalidFiles

修正后的完整查询 #

SELECT 
  DISTINCT A.Column_1,
  B.Column_2,
  Column_3,
  A.Column_4,
  B.Column_5,
  B.Column_6, 
  B.Column_7, 
  Column_8, 
  Column_9, 
  Column_10, 
  Column_11, 

  (SELECT COUNT(File_1) FROM Table_1 WHERE Column_1 = A.Column_1) AS TotalFileCnt, 

  (SELECT COUNT(DISTINCT File_2) FROM Table_2 WHERE Column_2 = B.Column_2) AS ValidFileCnt, 

  (
    (SELECT COUNT(File_1) FROM Table_1 WHERE Column_1 = A.Column_1)
    -
    (SELECT COUNT(DISTINCT File_2) FROM Table_2 WHERE Column_2 = B.Column_2)
  ) AS InvalidFileCnt, 

  (SELECT COUNT(*) FROM Table_2 Test WHERE Test.Column_2 = B.Column_2 AND Column_5 = 'COMPLETE') AS Complete, 

  (SELECT string_agg(BB.File_N, ',' ORDER BY BB.File_N) 
    FROM Table_1 BB 
    WHERE BB.Column_1 = A.Column_1 
      AND BB.File_2 NOT IN (
        SELECT COALESCE(File_2, 0) 
        FROM Table_2 Vld 
        JOIN Table_3 Grp ON Grp.Column_2 = Vld.Column_2 
        WHERE Grp.Column_1 = A.Column_1
      )
  ) AS InvalidFiles
  
FROM 
  Table_4 A 
  JOIN Table_3 B ON A.Column_1 = B.Column_1 
  JOIN Table_2 C ON B.Column_2 = C.Column_2 
WHERE 
  A.Column_4 = 'A' 
  AND A.file = 'N'

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