Using PostgreSQL User Defined Functions (UDFs) for Clinical Analytics
A Real-World Example with Continuous Glucose Monitoring (CGM) Data
Healthcare analytics often involves repeated clinical logic—rules that define disease states, risk categories, or thresholds. When this logic is embedded repeatedly across queries, dashboards, and pipelines, it becomes difficult to maintain and validate.
PostgreSQL User Defined Functions (UDFs) offer a clean, reusable, and scalable way to centralize clinical rules directly inside the database.
In this blog, we demonstrate how PostgreSQL UDFs can be used to:
Classify glucose readings into clinical risk categories
Compute Time-in-Range (TIR) metrics using real Continuous Glucose Monitoring (CGM) data
BIG IDEAs Lab Glycemic Variability and Wearable Device Data
Dataset Summary:
Participants with elevated glucose levels were monitored using Dexcom G6 CGMs and Empatica E4 wristbands. The dataset includes five physiological features (glucose, heart rate, IBI, EDA, skin temperature), food logs, and demographics. All data is time-shifted for privacy.
Reference link to the dataset Glycemic Variability Dataset
In this dataset table gv_healthcare.dexcom_staging_lvl1 stores Continuous Glucose Monitoring (CGM) data captured at frequent time intervals for each patient. It includes a unique patient identifier (patientid), the exact timestamp of each glucose measurement (timeof), and the recorded glucose value in milligrams per deciliter (glucosevaluemgdl), along with supporting CGM metadata such as event type and duration. With more than 36,000 glucose readings, this dataset is well suited for longitudinal and time-based diabetes analytics, including glucose trend analysis, risk classification, and Time-in-Range calculations.
The Problem with Rewriting Clinical Logic—and How UDFs Help
Without UDFs:
Without User Defined Functions (UDFs), the same clinical rules such as glucose thresholds or risk categories are repeatedly written in SQL queries, BI dashboards, and analytics pipelines. This repetition often leads to inconsistencies, errors, and extra effort when rules need to be updated.
A UDF:
UDFs solve this problem by storing the logic in one place inside the database. Once defined, the same function can be reused everywhere, ensuring consistent results, easier maintenance, and clearer, more reliable healthcare analytics.
Use case 1: Glucose Risk Classification UDF
In this part, we focus on converting raw glucose values into clinically meaningful categories. Continuous Glucose Monitoring (CGM) devices generate thousands of numeric readings, but these numbers alone are difficult to interpret without clinical context. By applying standard medical thresholds, glucose readings can be classified as Normal, Prediabetic, or Diabetic. Creating a User Defined Function (UDF) for this logic ensures that every glucose value is evaluated using the same clinical rules, making the analysis consistent and easier to understand across queries, dashboards, and research workflows.
Standard CGM Ranges:
Glucose (mg/dL) | Category |
< 100 | Normal |
100–125 | Prediabetic |
≥ 126 | Diabetic |
SQL: CREATE OR REPLACE FUNCTION glucose_risk_category(glucosevaluemgdl NUMERIC) RETURNS TEXT AS $$ BEGIN IF glucosevaluemgdl < 100 THEN RETURN 'Normal'; ELSIF glucosevaluemgdl BETWEEN 100 AND 125 THEN RETURN 'Prediabetic'; ELSE RETURN 'Diabetic'; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; |
Output:  |
Applying the UDF to CGM Data: SQL: SELECT patientid, timeof, glucosevaluemgdl, glucose_risk_category(glucosevaluemgdl) AS glucose_category FROM gv_healthcare.dexcom_staging_lvl1; |
Output:  |
Understanding Query:
In this example, the UDF takes a single input—the glucose value measured in milligrams per deciliter—and returns a text label that represents the clinical interpretation of that value.
The function is written in PL/pgSQL, PostgreSQL’s language, which allows the use of conditional logic such as IF and ELSE. Inside the function, glucose values are compared against clinically accepted thresholds to determine whether the reading is Normal, Prediabetic, or Diabetic.
Declaring the function as IMMUTABLE tells PostgreSQL that the output will always be the same for the same input. This is important for performance, as it allows the database optimizer to reuse results, apply indexes, and execute analytical queries more efficiently. Once created, the UDF can be called just like a built-in SQL function, making it easy to apply consistent clinical logic across all CGM records without rewriting the rules in every query.
Use Case 2 : Time-in-Range (TIR) A Core Diabetes Metric
What Is Time-in-Range?
Time-in-Range measures the percentage of time a patient’s glucose remains within a healthy range, making it more informative than average glucose alone.
Standard CGM Ranges:
Metric | Glucose Range (mg/dL) |
TBR | < 70 (Below Range) |
TIR | 70–180 (In Range) |
TAR | > 180 (Above Range) |
The Time-in-Range UDF focuses on evaluating glucose control over time rather than individual readings. This function classifies each CGM measurement into Below Range, In Range, or Above Range based on widely accepted CGM thresholds. By embedding this logic into a UDF, every glucose reading is assessed uniformly, making downstream aggregation reliable and clinically valid. Declaring the function as immutable allows PostgreSQL to efficiently process large volumes of CGM data. When combined with aggregation queries, this UDF enables the calculation of Time-in-Range, Time-Above-Range, and Time-Below-Range metrics, which are essential indicators of glycemic control in diabetes research and clinical practice.
Step 1: Create a Time-in-Range Classification UDF SQL: CREATE OR REPLACE FUNCTION cgm_time_range(glucosevaluemgdl NUMERIC) RETURNS TEXT AS $$ BEGIN IF glucosevaluemgdl < 70 THEN RETURN 'Below Range'; ELSIF glucosevaluemgdl BETWEEN 70 AND 180 THEN RETURN 'In Range'; ELSE RETURN 'Above Range'; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; |
Output:  |
Step 2: Apply the TIR UDF to CGM Data SQL: SELECT patientid, timeof, glucosevaluemgdl, cgm_time_range(glucosevaluemgdl) AS range_status FROM gv_healthcare.dexcom_staging_lvl1; |
Output:
 |
Now each glucose value is clinically interpretable in terms of risk exposure. |
Step 3: Calculate Time-in-Range Percentages per Patient SQL: SELECT patientid, ROUND( COUNT(*) FILTER (WHERE cgm_time_range(glucosevaluemgdl) = 'In Range') 100.0 / COUNT(), 2 ) AS time_in_range_pct,
ROUND( COUNT(*) FILTER (WHERE cgm_time_range(glucosevaluemgdl) = 'Above Range') 100.0 / COUNT(), 2 ) AS time_above_range_pct,
ROUND( COUNT(*) FILTER (WHERE cgm_time_range(glucosevaluemgdl) = 'Below Range') 100.0 / COUNT(), 2 ) AS time_below_range_pct FROM gv_healthcare.dexcom_staging_lvl1 |
Output:  |
This produces research-ready patient-level metrics. |
Why This Approach Works
Using PostgreSQL UDFs enables:
Reproducible clinical logic
Optimized query performance
Consistent definitions across teams
Simpler downstream analytics
Most importantly, it ensures that clinical definitions remain consistent across research, reporting, and production systems
Conclusion
PostgreSQL User Defined Functions are a powerful but often overlooked tool in healthcare analytics. By embedding glucose classification and Time-in-Range logic directly into the database, we can build scalable, auditable, and clinically meaningful analytics.
This real-world CGM example shows how UDFs can transform raw healthcare data into actionable insights—without duplicating logic or sacrificing performance.
Reference Links:
1.User Defined Functions - https://www.postgresql.org/docs/current/xfunc.html
2.PostgreSQL PL/pgSQL Documentation- https://www.postgresql.org/docs/current/plpgsql.html
3.PostgreSQL Function Volatility (IMMUTABLE, STABLE, VOLATILE) - https://www.postgresql.org/docs/current/sql-createfunction.html
4.Dataset link - Glycemic Variability Dataset
