Limit the number of groupings when using grouping sets?

[sebastian]

Postgres only seems to support up to 32, according to this error message.

"Aircloak API query error.\nDataSource: Clinic.\nError: ERROR 54023 (too_many_arguments) GROUPING must have fewer than 32 arguments\n\n query: SELECT "__ac_regular_stats"."__ac_count_duid","__ac_regular_stats"."__ac_group_0","__ac_regular_stats"."__ac_group_1","__ac_regular_stats"."__ac_group_2","__ac_regular_stats"."__ac_group_3","__ac_regular_stats"."__ac_group_4","__ac_regular_stats"."__ac_group_5","__ac_regular_stats"."__ac_group_6","__ac_regular_stats"."__ac_group_7","__ac_regular_stats"."__ac_group_8","__ac_regular_stats"."__ac_group_9","__ac_regular_stats"."__ac_group_10","__ac_regular_stats"."__ac_group_11","__ac_regular_stats"."__ac_group_12","__ac_regular_stats"."__ac_group_13","__ac_regular_stats"."__ac_group_14","__ac_regular_stats"."__ac_group_15","__ac_regular_stats"."__ac_group_16","__ac_regular_stats"."__ac_group_17","__ac_regular_stats"."__ac_group_18","__ac_regular_stats"."__ac_group_19","__ac_regular_stats"."__ac_group_20","__ac_regular_stats"."__ac_group_21","__ac_regular_stats"."__ac_group_22","__ac_regular_stats"."__ac_group_23","__ac_regular_stats"."__ac_group_24","__ac_regular_stats"."__ac_group_25","__ac_regular_stats"."__ac_group_26","__ac_regular_stats"."__ac_group_27","__ac_regular_stats"."__ac_group_28","__ac_regular_stats"."__ac_group_29","__ac_regular_stats"."__ac_group_30","__ac_regular_stats"."__ac_group_31","__ac_regular_stats"."__ac_group_32","__ac_regular_stats"."__ac_group_33","__ac_regular_stats"."__ac_group_34","__ac_regular_stats"."__ac_group_35","__ac_regular_stats"."__ac_group_36","__ac_regular_stats"."__ac_group_37","__ac_regular_stats"."__ac_group_38","__ac_regular_stats"."__ac_group_39","__ac_regular_stats"."__ac_agg_0_count","__ac_regular_stats"."__ac_agg_0_sum","__ac_regular_stats"."__ac_agg_0_min","__ac_regular_stats"."__ac_agg_0_max","__ac_regular_stats"."__ac_agg_0_stddev","__ac_regular_stats"."__ac_grouping_id","__ac_regular_stats"."__ac_min_uid","__ac_regular_stats"."__ac_max_uid","__ac_regular_stats"."__ac_min_uid" AS "__ac_nlc__2","__ac_regular_stats"."__ac_max_uid" AS "__ac_nlc__3" FROM (SELECT "__ac_uid_grouping"."__ac_grouping_id" AS "__ac_grouping_id","__ac_uid_grouping"."__ac_group_0" AS "__ac_group_0","__ac_uid_grouping"."__ac_group_1" AS "__ac_group_1","__ac_uid_grouping"."__ac_group_2" AS "__ac_group_2","__ac_uid_grouping"."__ac_group_3" AS "__ac_group_3","__ac_uid_grouping"."__ac_group_4" AS "__ac_group_4","__ac_uid_grouping"."__ac_group_5" AS "__ac_group_5","__ac_uid_grouping"."__ac_group_6" AS "__ac_group_6","__ac_uid_grouping"."__ac_group_7" AS "__ac_group_7","__ac_uid_grouping"."__ac_group_8" AS "__ac_group_8","__ac_uid_grouping"."__ac_group_9" AS "__ac_group_9","__ac_uid_grouping"."__ac_group_10" AS "__ac_group_10","__ac_uid_grouping"."__ac_group_11" AS "__ac_group_11","__ac_uid_grouping"."__ac_group_12" AS "__ac_group_12","__ac_uid_grouping"."__ac_group_13" AS "__ac_group_13","__ac_uid_grouping"."__ac_group_14" AS "__ac_group_14","__ac_uid_grouping"."__ac_group_15" AS "__ac_group_15","__ac_uid_grouping"."__ac_group_16" AS "__ac_group_16","__ac_uid_grouping"."__ac_group_17" AS "__ac_group_17","__ac_uid_grouping"."__ac_group_18" AS "__ac_group_18","__ac_uid_grouping"."__ac_group_19" AS "__ac_group_19","__ac_uid_grouping"."__ac_group_20" AS "__ac_group_20","__ac_uid_grouping"."__ac_group_21" AS "__ac_group_21","__ac_uid_grouping"."__ac_group_22" AS "__ac_group_22","__ac_uid_grouping"."__ac_group_23" AS "__ac_group_23","__ac_uid_grouping"."__ac_group_24" AS "__ac_group_24","__ac_uid_grouping"."__ac_group_25" AS "__ac_group_25","__ac_uid_grouping"."__ac_group_26" AS "__ac_group_26","__ac_uid_grouping"."__ac_group_27" AS "__ac_group_27","__ac_uid_grouping"."__ac_group_28" AS "__ac_group_28","__ac_uid_grouping"."__ac_group_29" AS "__ac_group_29","__ac_uid_grouping"."__ac_group_30" AS "__ac_group_30","__ac_uid_grouping"."__ac_group_31" AS "__ac_group_31","__ac_uid_grouping"."__ac_group_32" AS "__ac_group_32","__ac_uid_grouping"."__ac_group_33" AS "__ac_group_33","__ac_uid_grouping"."__ac_group_34" AS "__ac_group_34","__ac_uid_grouping"."__ac_group_35" AS "__ac_group_35","__ac_uid_grouping"."__ac_group_36" AS "__ac_group_36","__ac_uid_grouping"."__ac_group_37" AS "__ac_group_37","__ac_uid_grouping"."__ac_group_38" AS "__ac_group_38","__ac_uid_grouping"."__ac_group_39" AS "__ac_group_39",COUNT("__ac_uid_grouping"."patient_id") AS "__ac_count_duid",MIN("__ac_uid_grouping"."patient_id") AS "__ac_min_uid",MAX("__ac_uid_grouping"."patient_id") AS "__ac_max_uid",COUNT("__ac_uid_grouping"."__ac_agg_0") AS "__ac_agg_0_count",SUM("__ac_uid_grouping"."__ac_agg_0") AS "__ac_agg_0_sum",MIN("__ac_uid_grouping"."__ac_agg_0") AS "__ac_agg_0_min",MAX("__ac_uid_grouping"."__ac_agg_0") AS "__ac_agg_0_max",STDDEV("__ac_uid_grouping"."__ac_agg_0") AS "__ac_agg_0_stddev" FROM (SELECT "prescriptions"."patient_id" AS "patient_id","prescriptions"."drug_code" AS "__ac_group_0",CASE WHEN ("prescriptions"."acute" < -10) THEN -10 WHEN ("prescriptions"."acute" > 10) THEN 10 ELSE "prescriptions"."acute" END AS "__ac_group_1",CASE WHEN ("prescriptions"."status" < -10) THEN -10 WHEN ("prescriptions"."status" > 10) THEN 10 ELSE "prescriptions"."status" END AS "__ac_group_2",CASE WHEN ("prescriptions"."do_not_substitute" < -10) THEN -10 WHEN ("prescriptions"."do_not_substitute" > 10) THEN 10 ELSE "prescriptions"."do_not_substitute" END AS "__ac_group_3",CASE WHEN ("prescriptions"."amount_quantity" < -10) THEN -10 WHEN ("prescriptions"."amount_quantity" > 5000) THEN 5000 ELSE "prescriptions"."amount_quantity" END AS "__ac_group_4",CASE WHEN ("prescriptions"."refills" < -10) THEN -10 WHEN ("prescriptions"."refills" > 100) THEN 100 ELSE "prescriptions"."refills" END AS "__ac_group_5","prescriptions"."amount_units" AS "__ac_group_6","prescriptions"."limited_use_code" AS "__ac_group_7","prescriptions"."patient_phone" AS "__ac_group_8","prescriptions"."removed_reason" AS "__ac_group_9","prescriptions"."removed_reason_id" AS "__ac_group_10",CASE WHEN ("prescriptions"."dispense_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_quantity" END AS "__ac_group_11","prescriptions"."dispense_format" AS "__ac_group_12",CASE WHEN ("prescriptions"."dispense_frequency_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_frequency_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_frequency_quantity" END AS "__ac_group_13","prescriptions"."dispense_frequency_unit" AS "__ac_group_14",CASE WHEN ("prescriptions"."sample_given" < -10) THEN -10 WHEN ("prescriptions"."sample_given" > 10) THEN 10 ELSE "prescriptions"."sample_given" END AS "__ac_group_15","prescriptions"."old_id" AS "__ac_group_16","prescriptions"."audit_created_at" AS "__ac_group_17","prescriptions"."ink_private_note_created_by" AS "__ac_group_18",CASE WHEN ("prescriptions"."deleted" < -10) THEN -10 WHEN ("prescriptions"."deleted" > 10) THEN 10 ELSE "prescriptions"."deleted" END AS "__ac_group_19",CASE WHEN ("prescriptions"."hidefromcpp" < -10) THEN -10 WHEN ("prescriptions"."hidefromcpp" > 10) THEN 10 ELSE "prescriptions"."hidefromcpp" END AS "__ac_group_20","prescriptions"."rxwrittenon" AS "__ac_group_21","prescriptions"."rxwrittenby" AS "__ac_group_22","prescriptions"."rxlastrefilledon" AS "__ac_group_23","prescriptions"."rxpatientcompliant" AS "__ac_group_24","prescriptions"."dispensingfacility" AS "__ac_group_25","prescriptions"."pickupon" AS "__ac_group_26","prescriptions"."diagcode" AS "__ac_group_27","prescriptions"."nonauthoritative" AS "__ac_group_28","prescriptions"."ink_private_notes_text" AS "__ac_group_29","prescriptions"."ink_public_notes_text" AS "__ac_group_30",CASE WHEN ("prescriptions"."initalfilldayssupply" < -10) THEN -10 WHEN ("prescriptions"."initalfilldayssupply" > 10) THEN 10 ELSE "prescriptions"."initalfilldayssupply" END AS "__ac_group_31",CASE WHEN ("prescriptions"."initalfillquantity" < -10) THEN -10 WHEN ("prescriptions"."initalfillquantity" > 10) THEN 10 ELSE "prescriptions"."initalfillquantity" END AS "__ac_group_32","prescriptions"."adhoc_drugstrength" AS "__ac_group_33","prescriptions"."ink_public_note_created_on" AS "__ac_group_34","prescriptions"."ink_public_note_created_by" AS "__ac_group_35","prescriptions"."refill_amount_quantity" AS "__ac_group_36","prescriptions"."refill_amount_units" AS "__ac_group_37","prescriptions"."protocol_identifier_id" AS "__ac_group_38","prescriptions"."long_term_medication" AS "__ac_group_39",COUNT() AS "__ac_agg_0",GROUPING("prescriptions"."drug_code", CASE WHEN ("prescriptions"."acute" < -10) THEN -10 WHEN ("prescriptions"."acute" > 10) THEN 10 ELSE "prescriptions"."acute" END, CASE WHEN ("prescriptions"."status" < -10) THEN -10 WHEN ("prescriptions"."status" > 10) THEN 10 ELSE "prescriptions"."status" END, CASE WHEN ("prescriptions"."do_not_substitute" < -10) THEN -10 WHEN ("prescriptions"."do_not_substitute" > 10) THEN 10 ELSE "prescriptions"."do_not_substitute" END, CASE WHEN ("prescriptions"."amount_quantity" < -10) THEN -10 WHEN ("prescriptions"."amount_quantity" > 5000) THEN 5000 ELSE "prescriptions"."amount_quantity" END, CASE WHEN ("prescriptions"."refills" < -10) THEN -10 WHEN ("prescriptions"."refills" > 100) THEN 100 ELSE "prescriptions"."refills" END, "prescriptions"."amount_units", "prescriptions"."limited_use_code", "prescriptions"."patient_phone", "prescriptions"."removed_reason", "prescriptions"."removed_reason_id", CASE WHEN ("prescriptions"."dispense_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_quantity" END, "prescriptions"."dispense_format", CASE WHEN ("prescriptions"."dispense_frequency_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_frequency_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_frequency_quantity" END, "prescriptions"."dispense_frequency_unit", CASE WHEN ("prescriptions"."sample_given" < -10) THEN -10 WHEN ("prescriptions"."sample_given" > 10) THEN 10 ELSE "prescriptions"."sample_given" END, "prescriptions"."old_id", "prescriptions"."audit_created_at", "prescriptions"."ink_private_note_created_by", CASE WHEN ("prescriptions"."deleted" < -10) THEN -10 WHEN ("prescriptions"."deleted" > 10) THEN 10 ELSE "prescriptions"."deleted" END, CASE WHEN ("prescriptions"."hidefromcpp" < -10) THEN -10 WHEN ("prescriptions"."hidefromcpp" > 10) THEN 10 ELSE "prescriptions"."hidefromcpp" END, "prescriptions"."rxwrittenon", "prescriptions"."rxwrittenby", "prescriptions"."rxlastrefilledon", "prescriptions"."rxpatientcompliant", "prescriptions"."dispensingfacility", "prescriptions"."pickupon", "prescriptions"."diagcode", "prescriptions"."nonauthoritative", "prescriptions"."ink_private_notes_text", "prescriptions"."ink_public_notes_text", CASE WHEN ("prescriptions"."initalfilldayssupply" < -10) THEN -10 WHEN ("prescriptions"."initalfilldayssupply" > 10) THEN 10 ELSE "prescriptions"."initalfilldayssupply" END, CASE WHEN ("prescriptions"."initalfillquantity" < -10) THEN -10 WHEN ("prescriptions"."initalfillquantity" > 10) THEN 10 ELSE "prescriptions"."initalfillquantity" END, "prescriptions"."adhoc_drugstrength", "prescriptions"."ink_public_note_created_on", "prescriptions"."ink_public_note_created_by", "prescriptions"."refill_amount_quantity", "prescriptions"."refill_amount_units", "prescriptions"."protocol_identifier_id", "prescriptions"."long_term_medication") AS "__ac_grouping_id" FROM "pt_prescriptions" AS "prescriptions" WHERE "prescriptions"."patient_id" IS NOT NULL GROUP BY GROUPING SETS (("prescriptions"."patient_id", "prescriptions"."drug_code"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."acute" < -10) THEN -10 WHEN ("prescriptions"."acute" > 10) THEN 10 ELSE "prescriptions"."acute" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."status" < -10) THEN -10 WHEN ("prescriptions"."status" > 10) THEN 10 ELSE "prescriptions"."status" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."do_not_substitute" < -10) THEN -10 WHEN ("prescriptions"."do_not_substitute" > 10) THEN 10 ELSE "prescriptions"."do_not_substitute" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."amount_quantity" < -10) THEN -10 WHEN ("prescriptions"."amount_quantity" > 5000) THEN 5000 ELSE "prescriptions"."amount_quantity" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."refills" < -10) THEN -10 WHEN ("prescriptions"."refills" > 100) THEN 100 ELSE "prescriptions"."refills" END), ("prescriptions"."patient_id", "prescriptions"."amount_units"), ("prescriptions"."patient_id", "prescriptions"."limited_use_code"), ("prescriptions"."patient_id", "prescriptions"."patient_phone"), ("prescriptions"."patient_id", "prescriptions"."removed_reason"), ("prescriptions"."patient_id", "prescriptions"."removed_reason_id"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."dispense_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_quantity" END), ("prescriptions"."patient_id", "prescriptions"."dispense_format"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."dispense_frequency_quantity" < -10) THEN -10 WHEN ("prescriptions"."dispense_frequency_quantity" > 500) THEN 500 ELSE "prescriptions"."dispense_frequency_quantity" END), ("prescriptions"."patient_id", "prescriptions"."dispense_frequency_unit"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."sample_given" < -10) THEN -10 WHEN ("prescriptions"."sample_given" > 10) THEN 10 ELSE "prescriptions"."sample_given" END), ("prescriptions"."patient_id", "prescriptions"."old_id"), ("prescriptions"."patient_id", "prescriptions"."audit_created_at"), ("prescriptions"."patient_id", "prescriptions"."ink_private_note_created_by"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."deleted" < -10) THEN -10 WHEN ("prescriptions"."deleted" > 10) THEN 10 ELSE "prescriptions"."deleted" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."hidefromcpp" < -10) THEN -10 WHEN ("prescriptions"."hidefromcpp" > 10) THEN 10 ELSE "prescriptions"."hidefromcpp" END), ("prescriptions"."patient_id", "prescriptions"."rxwrittenon"), ("prescriptions"."patient_id", "prescriptions"."rxwrittenby"), ("prescriptions"."patient_id", "prescriptions"."rxlastrefilledon"), ("prescriptions"."patient_id", "prescriptions"."rxpatientcompliant"), ("prescriptions"."patient_id", "prescriptions"."dispensingfacility"), ("prescriptions"."patient_id", "prescriptions"."pickupon"), ("prescriptions"."patient_id", "prescriptions"."diagcode"), ("prescriptions"."patient_id", "prescriptions"."nonauthoritative"), ("prescriptions"."patient_id", "prescriptions"."ink_private_notes_text"), ("prescriptions"."patient_id", "prescriptions"."ink_public_notes_text"), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."initalfilldayssupply" < -10) THEN -10 WHEN ("prescriptions"."initalfilldayssupply" > 10) THEN 10 ELSE "prescriptions"."initalfilldayssupply" END), ("prescriptions"."patient_id", CASE WHEN ("prescriptions"."initalfillquantity" < -10) THEN -10 WHEN ("prescriptions"."initalfillquantity" > 10) THEN 10 ELSE "prescriptions"."initalfillquantity" END), ("prescriptions"."patient_id", "prescriptions"."adhoc_drugstrength"), ("prescriptions"."patient_id", "prescriptions"."ink_public_note_created_on"), ("prescriptions"."patient_id", "prescriptions"."ink_public_note_created_by"), ("prescriptions"."patient_id", "prescriptions"."refill_amount_quantity"), ("prescriptions"."patient_id", "prescriptions"."refill_amount_units"), ("prescriptions"."patient_id", "prescriptions"."protocol_identifier_id"), ("prescriptions"."patient_id", "prescriptions"."long_term_medication"))) AS "__ac_uid_grouping" GROUP BY "__ac_uid_grouping"."__ac_grouping_id", "__ac_uid_grouping"."__ac_group_0", "__ac_uid_grouping"."__ac_group_1", "__ac_uid_grouping"."__ac_group_2", "__ac_uid_grouping"."__ac_group_3", "__ac_uid_grouping"."__ac_group_4", "__ac_uid_grouping"."__ac_group_5", "__ac_uid_grouping"."__ac_group_6", "__ac_uid_grouping"."__ac_group_7", "__ac_uid_grouping"."__ac_group_8", "__ac_uid_grouping"."__ac_group_9", "__ac_uid_grouping"."__ac_group_10", "__ac_uid_grouping"."__ac_group_11", "__ac_uid_grouping"."__ac_group_12", "__ac_uid_grouping"."__ac_group_13", "__ac_uid_grouping"."__ac_group_14", "__ac_uid_grouping"."__ac_group_15", "__ac_uid_grouping"."__ac_group_16", "__ac_uid_grouping"."__ac_group_17", "__ac_uid_grouping"."__ac_group_18", "__ac_uid_grouping"."__ac_group_19", "__ac_uid_grouping"."__ac_group_20", "__ac_uid_grouping"."__ac_group_21", "__ac_uid_grouping"."__ac_group_22", "__ac_uid_grouping"."__ac_group_23", "__ac_uid_grouping"."__ac_group_24", "__ac_uid_grouping"."__ac_group_25", "__ac_uid_grouping"."__ac_group_26", "__ac_uid_grouping"."__ac_group_27", "__ac_uid_grouping"."__ac_group_28", "__ac_uid_grouping"."__ac_group_29", "__ac_uid_grouping"."__ac_group_30", "__ac_uid_grouping"."__ac_group_31", "__ac_uid_grouping"."__ac_group_32", "__ac_uid_grouping"."__ac_group_33", "__ac_uid_grouping"."__ac_group_34", "__ac_uid_grouping"."__ac_group_35", "__ac_uid_grouping"."__ac_group_36", "__ac_uid_grouping"."__ac_group_37", "__ac_uid_grouping"."__ac_group_38", "__ac_uid_grouping"."__ac_group_39") AS "__ac_regular_stats"\nQuery Id: 46300b2c-4998-4c8d-ad20-98810920b465\nQuery Statement: \n select\n grouping_id(\n "drug_code","acute","status","do_not_substitute","amount_quantity","refills","amount_units","limited_use_code","patient_phone","removed_reason","removed_reason_id","dispense_quantity","dispense_format","dispense_frequency_quantity","dispense_frequency_unit","sample_given","old_id","audit_created_at","ink_private_note_created_by","deleted","hidefromcpp","rxwrittenon","rxwrittenby","rxlastrefilledon","rxpatientcompliant","dispensingfacility","pickupon","diagcode","nonauthoritative","ink_private_notes_text","ink_public_notes_text","initalfilldayssupply","initalfillquantity","adhoc_drugstrength","ink_public_note_created_on","ink_public_note_created_by","refill_amount_quantity","refill_amount_units","protocol_identifier_id","long_term_medication"\n ),\n "drug_code","acute","status","do_not_substitute","amount_quantity","refills","amount_units","limited_use_code","patient_phone","removed_reason","removed_reason_id","dispense_quantity","dispense_format","dispense_frequency_quantity","dispense_frequency_unit","sample_given","old_id","audit_created_at","ink_private_note_created_by","deleted","hidefromcpp","rxwrittenon","rxwrittenby","rxlastrefilledon","rxpatientcompliant","dispensingfacility","pickupon","diagcode","nonauthoritative","ink_private_notes_text","ink_public_notes_text","initalfilldayssupply","initalfillquantity","adhoc_drugstrength","ink_public_note_created_on","ink_public_note_created_by","refill_amount_quantity","refill_amount_units","protocol_identifier_id","long_term_medication",\n count(),\n count_noise(*)\n from "prescriptions"\n group by grouping sets ((2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41))"

Maybe we should enforce a limit too?