I kept forgetting how to do dynamic SQL to query all the MC d_ tables so I saved it this time; returns XML

mirth_daily_volume_report.sql

WITH channel_query AS (
    SELECT dc.local_channel_id,
          c.name,
          format('SELECT ' ||
                 '%L::TEXT as channel_name, ' ||
                 'date_trunc(''day'', received_date)::TEXT as day, ' ||
                 'count(*)::BIGINT as cnt FROM d_m%s' ||
                 ' WHERE received_date >= now() - INTERVAL ''%s DAYS'' GROUP BY 1, 2', c.name,
                 dc.local_channel_id, 90) as query
    FROM d_channels dc
    INNER JOIN channel c ON c.id = dc.channel_id),
     big_query AS (
        SELECT array_to_string(array_agg(query), E'\n UNION ALL \n ') || ' ORDER BY 1 ASC, 2 DESC ' as biggie
        FROM channel_query
        )
SELECT
    query_to_xml(biggie, true, true, 'jonb-ns')
FROM big_query;

Here is my version, I create a function that can be reused, similar to a stored procedure, which should return the dates associated with each connector:

CREATE OR REPLACE FUNCTION last_activity()
RETURNS TABLE (
    channel_id TEXT,
	channel_name TEXT,
    connector_name TEXT,
    max_received_date TIMESTAMP,
    max_response_date TIMESTAMP
) AS $$
DECLARE
    query_text TEXT;
BEGIN
    -- Your existing CTEs and query construction
    WITH CHANNEL_QUERY AS (
        SELECT DC.LOCAL_CHANNEL_ID,
                C.NAME,
                FORMAT(
                    'SELECT 
						%L::TEXT as channel_id,
						%L::TEXT as channel_name, 						
						connector_name, 
						 max(RECEIVED_DATE)::TIMESTAMP AS max_received_date, 
						 max(RESPONSE_DATE)::TIMESTAMP AS max_response_date 
                     FROM D_MM%s GROUP BY connector_name',
					DC.channel_id,
					C.NAME,
                    DC.LOCAL_CHANNEL_ID
                ) AS QUERY
        FROM D_CHANNELS DC
        INNER JOIN CHANNEL C ON C.ID = DC.CHANNEL_ID
    ),
    BIG_QUERY AS (
        SELECT ARRAY_TO_STRING(ARRAY_AGG(QUERY), E'\n UNION ALL \n ') || ' ORDER BY 1 ASC, 2 DESC ' AS BIGGIE
        FROM CHANNEL_QUERY
    )
    SELECT BIGGIE INTO query_text
    FROM BIG_QUERY;

    -- Execute the dynamic query and return the result
    RETURN QUERY EXECUTE query_text;

END $$ LANGUAGE plpgsql;

And then you can call it with

SELECT * FROM last_activity();

However, in production it is very slow, so slow that I cancelled it. You code also runs so slow I cancelled it, so it could be our code, but may have more to do with the health of our production db.

New performant version works. THANK YOU!!!!

CREATE OR REPLACE FUNCTION last_activity()
RETURNS TABLE (
    channel_id TEXT,
	channel_name TEXT,
    connector_name TEXT,
    RECEIVED_DATE TIMESTAMP,
    RESPONSE_DATE TIMESTAMP
) AS $$
DECLARE
    query_text TEXT;
BEGIN
    -- Your existing CTEs and query construction
    WITH CHANNEL_QUERY AS (
        SELECT DC.LOCAL_CHANNEL_ID,
                C.NAME,
                FORMAT(
                    '(SELECT
                       %L::TEXT as channel_id,
                       %L::TEXT as channel_name,
                       connector_name,
                       RECEIVED_DATE::TIMESTAMP,
                       RESPONSE_DATE::TIMESTAMP
                    FROM D_MM%s ORDER BY message_id DESC LIMIT 1)',
					DC.channel_id,
					C.NAME,
                    DC.LOCAL_CHANNEL_ID
                ) AS QUERY
        FROM D_CHANNELS DC
        INNER JOIN CHANNEL C ON C.ID = DC.CHANNEL_ID
    ),
    BIG_QUERY AS (
        SELECT ARRAY_TO_STRING(ARRAY_AGG(QUERY), E'\n UNION ALL \n ') || ' ORDER BY 1 ASC, 2 DESC ' AS BIGGIE
        FROM CHANNEL_QUERY
    )
    SELECT BIGGIE INTO query_text
    FROM BIG_QUERY;

    -- Execute the dynamic query and return the result
    RETURN QUERY EXECUTE query_text;

END $$ LANGUAGE plpgsql;