gistfile1.sql

SELECT MESSAGE.ID,
       MESSAGE.SERVER_ID,
       MESSAGE.RECEIVED_DATE,
       MESSAGE.PROCESSED,
       MESSAGE.ORIGINAL_ID,
       MESSAGE.IMPORT_ID,
       MESSAGE.IMPORT_CHANNEL_ID,
       METADATA_IDS
FROM
  ( SELECT M.ID,
           ARRAY_TO_STRING(ARRAY_AGG(MM.ID::CHARACTER VARYING), ',') AS METADATA_IDS
   FROM D_M107 M
   JOIN D_MM107 MM ON MM.MESSAGE_ID = M.ID
   LEFT JOIN D_MCM107 MCM ON MCM.MESSAGE_ID = MM.MESSAGE_ID
   AND MCM.METADATA_ID = MM.ID
   LEFT JOIN D_MC107 MC_1 ON MC_1.MESSAGE_ID = M.ID
   AND MC_1.IS_ENCRYPTED = FALSE
   AND MC_1.METADATA_ID = 0
   AND ((MM.ID = 0
         AND MC_1.CONTENT_TYPE = 1)
        OR (MM.ID > 0
            AND MC_1.CONTENT_TYPE = 4))
   LEFT JOIN D_MC107 MC_2 ON MC_2.MESSAGE_ID = M.ID
   AND MC_2.IS_ENCRYPTED = FALSE
   AND MC_2.METADATA_ID = MM.ID
   AND MC_2.CONTENT_TYPE = $1
   LEFT JOIN D_MC107 MC_3 ON MC_3.MESSAGE_ID = M.ID
   AND MC_3.IS_ENCRYPTED = FALSE
   AND MC_3.METADATA_ID = MM.ID
   AND MC_3.CONTENT_TYPE = $2
   LEFT JOIN D_MC107 MC_4 ON MC_4.MESSAGE_ID = M.ID
   AND MC_4.IS_ENCRYPTED = FALSE
   AND MC_4.METADATA_ID = MM.ID
   AND MC_4.CONTENT_TYPE = $3
   LEFT JOIN D_MC107 MC_5 ON MC_5.MESSAGE_ID = M.ID
   AND MC_5.IS_ENCRYPTED = FALSE
   AND MC_5.METADATA_ID = MM.ID
   AND MC_5.CONTENT_TYPE = $4
   LEFT JOIN D_MC107 MC_6 ON MC_6.MESSAGE_ID = M.ID
   AND MC_6.IS_ENCRYPTED = FALSE
   AND MC_6.METADATA_ID = MM.ID
   AND MC_6.CONTENT_TYPE = $5
   LEFT JOIN D_MC107 MC_7 ON MC_7.MESSAGE_ID = M.ID
   AND MC_7.IS_ENCRYPTED = FALSE
   AND MC_7.METADATA_ID = MM.ID
   AND MC_7.CONTENT_TYPE = $6
   LEFT JOIN D_MC107 MC_8 ON MC_8.MESSAGE_ID = M.ID
   AND MC_8.IS_ENCRYPTED = FALSE
   AND MC_8.METADATA_ID = MM.ID
   AND MC_8.CONTENT_TYPE = $7
   WHERE M.ID <= $8
     AND ( M.ID::VARCHAR ILIKE '%' || $9 || '%'
          OR MM.CONNECTOR_NAME ILIKE '%' || $10 || '%'
          OR MC_1.CONTENT ILIKE '%' || $11 || '%'
          OR MC_2.CONTENT ILIKE '%' || $12 || '%'
          OR MC_3.CONTENT ILIKE '%' || $13 || '%'
          OR MC_4.CONTENT ILIKE '%' || $14 || '%'
          OR MC_5.CONTENT ILIKE '%' || $15 || '%'
          OR MC_6.CONTENT ILIKE '%' || $16 || '%'
          OR MC_7.CONTENT ILIKE '%' || $17 || '%'
          OR MC_8.CONTENT ILIKE '%' || $18 || '%'
          OR MCM."SOURCE" ILIKE '%' || $19 || '%'
          OR MCM."TYPE" ILIKE '%' || $20 || '%' )
   GROUP BY M.ID
   ORDER BY M.ID DESC
   OFFSET 0 LIMIT 21 ) AS SUBQUERY
INNER JOIN D_M107 MESSAGE ON SUBQUERY.ID = MESSAGE.ID
ORDER BY MESSAGE.ID DESC