Question 395006 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres query work when executed normally but throw error when executed as procedure

When I run the below query it executes correctly. But when called as a procedure throws an error

Exception assignment source returned 2 columns 42601

From the analysis I found it’s coming from here.

    ( 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
      ) 
    GROUP BY 
      BB.Column_1
  ) AS InvalidFiles

Full Query Below

    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
      ) 
    GROUP BY 
      BB.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'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found the issue myself. The ( SELECT string_agg(BB.File_N, ',' ORDER BY BB.File_N)
    should be ( SELECT string_agg(BB.File_N, '','' ORDER BY BB.File_N)

    '','' is correct

    ',' is Wrong


  2. 0

    You can reduce the use of "correlated sub-queries" by joining sub-queries instead, this will have a beneficial impact on performance and simplifies the query as well.

    SELECT 
      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
    , D.TotalFileCnt
    , E.ValidFileCnt
    , D.TotalFileCnt - E.ValidFileCnt AS InvalidFileCnt
    , E.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
LEFT JOIN (
        SELECT
            Column_1
            , COUNT(File_1) AS TotalFileCnt
        FROM Table_1
        GROUP BY Column_1
        ) D ON D.Column_1 = A.Column_1
LEFT JOIN (
        SELECT
              Column_2
            , COUNT(DISTINCT File_2) AS ValidFileCnt
            , COUNT(CASE WHEN Column_5 = 'COMPLETE' THEN 1 END) AS Complete
        FROM Table_2
        GROUP BY Column_2
        ) e ON E.Column_2 = B.Column_2
WHERE A.Column_4 = 'A'
    AND A.FILE = 'N'

    Those joined subqueries are performed once, whereas each "correlated sub-query" is performed PER ROW (here a reduction of 4 queries per row by using joins).

    Note: correlated sub-queries are capable of returning NULL, hence when replacing with joins use LEFT JOIN to mimic this same capability. If you don’t require possible NULLs then you may use INNER JOINs instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search