Last active
December 15, 2023 21:13
-
-
Save jonbartels/b961574b2043b628f1b0fd96f440179b to your computer and use it in GitHub Desktop.
I kept forgetting how to do dynamic SQL to query all the MC d_ tables so I saved it this time; returns XML
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
New performant version works. THANK YOU!!!!