sqlx_json/
postgres.rs

1//! [`RowExt`](crate::RowExt) implementation for `PostgreSQL` rows.
2//!
3//! Type names are normalized to uppercase because sqlx may return either case
4//! depending on the query context. Integer types use size-specific Rust types
5//! (`i16`, `i32`, `i64`) because sqlx enforces strict type matching for
6//! `PostgreSQL`. Temporal types (`DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMPTZ`)
7//! are decoded via sqlx's `chrono` integration and serialized as RFC 3339
8//! strings; `TIMESTAMPTZ` is normalized to UTC and emitted with a `Z` suffix.
9//! `NUMERIC` is decoded via `BigDecimal` to preserve precision; `MONEY`
10//! arrives as text (sqlx uses simple-query for parameterless statements)
11//! and is parsed locale-aware then routed through the same shape rule.
12
13use std::str::FromStr;
14
15use base64::Engine as _;
16use base64::engine::general_purpose::STANDARD as BASE64;
17use bigdecimal::BigDecimal;
18use serde_json::{Map, Value};
19use sqlx::postgres::PgRow;
20use sqlx::types::chrono::{DateTime, NaiveDate, NaiveDateTime, NaiveTime, Utc};
21use sqlx::{Column, Row, TypeInfo, ValueRef};
22
23use crate::RowExt;
24use crate::numeric::bigdecimal_to_json;
25
26/// Parses a locale-formatted Postgres `MONEY` text value into a `BigDecimal`.
27///
28/// Strips currency symbol and grouping separators (everything except digits,
29/// `.`, and `-`), then parses what remains. Tuned for the en_US.UTF-8
30/// `lc_monetary` default — locales using `,` as decimal separator are not
31/// supported.
32fn parse_pg_money_text(text: &str) -> Option<BigDecimal> {
33    let cleaned: String = text.chars().filter(|c| matches!(c, '0'..='9' | '.' | '-')).collect();
34    BigDecimal::from_str(&cleaned).ok()
35}
36
37impl RowExt for PgRow {
38    fn to_json(&self) -> Value {
39        let columns = self.columns();
40        let mut map = Map::with_capacity(columns.len());
41
42        for column in columns {
43            let idx = column.ordinal();
44            let type_name = column.type_info().name().to_ascii_uppercase();
45
46            let value = if self.try_get_raw(idx).is_ok_and(|v| v.is_null()) {
47                Value::Null
48            } else {
49                match type_name.as_str() {
50                    "BOOL" => self.try_get::<bool, _>(idx).map_or(Value::Null, Value::Bool),
51
52                    "INT8" => self
53                        .try_get::<i64, _>(idx)
54                        .map_or(Value::Null, |v| Value::Number(v.into())),
55
56                    "INT4" | "OID" => self
57                        .try_get::<i32, _>(idx)
58                        .map_or(Value::Null, |v| Value::Number(i64::from(v).into())),
59
60                    "INT2" => self
61                        .try_get::<i16, _>(idx)
62                        .map_or(Value::Null, |v| Value::Number(i64::from(v).into())),
63
64                    "NUMERIC" => self
65                        .try_get::<BigDecimal, _>(idx)
66                        .map_or(Value::Null, |v| bigdecimal_to_json(&v)),
67
68                    // dbmcp passes raw `&str` queries → sqlx uses Postgres' simple-query
69                    // (text) protocol, where `PgMoney` errors out (binary-only). Parse the
70                    // locale-formatted text form ($1,234.56, -$99.99) directly — assumes the
71                    // en_US.UTF-8 lc_monetary default (`$` symbol, `.` decimal).
72                    "MONEY" => self
73                        .try_get_raw(idx)
74                        .ok()
75                        .and_then(|v| v.as_str().ok())
76                        .and_then(parse_pg_money_text)
77                        .map_or(Value::Null, |bd| bigdecimal_to_json(&bd)),
78
79                    "FLOAT4" => self.try_get::<f32, _>(idx).map_or(Value::Null, Value::from),
80
81                    "FLOAT8" => self.try_get::<f64, _>(idx).map_or(Value::Null, Value::from),
82
83                    "BYTEA" => self
84                        .try_get::<Vec<u8>, _>(idx)
85                        .map_or(Value::Null, |bytes| Value::String(BASE64.encode(&bytes))),
86
87                    "JSON" | "JSONB" => self.try_get::<Value, _>(idx).unwrap_or(Value::Null),
88
89                    "DATE" => self
90                        .try_get::<NaiveDate, _>(idx)
91                        .map_or(Value::Null, |v| Value::String(v.to_string())),
92
93                    "TIME" => self
94                        .try_get::<NaiveTime, _>(idx)
95                        .map_or(Value::Null, |v| Value::String(v.to_string())),
96
97                    "TIMESTAMP" => self
98                        .try_get::<NaiveDateTime, _>(idx)
99                        .map_or(Value::Null, |v| Value::String(format!("{}T{}", v.date(), v.time()))),
100
101                    "TIMESTAMPTZ" => self.try_get::<DateTime<Utc>, _>(idx).map_or(Value::Null, |v| {
102                        let n = v.naive_utc();
103                        Value::String(format!("{}T{}Z", n.date(), n.time()))
104                    }),
105
106                    _ => self.try_get::<String, _>(idx).map_or(Value::Null, Value::String),
107                }
108            };
109
110            map.insert(column.name().to_string(), value);
111        }
112
113        Value::Object(map)
114    }
115}
116
117#[cfg(test)]
118mod tests {
119    use super::parse_pg_money_text;
120    use bigdecimal::BigDecimal;
121    use std::str::FromStr;
122
123    fn dec(s: &str) -> BigDecimal {
124        BigDecimal::from_str(s).expect("valid decimal literal")
125    }
126
127    #[test]
128    fn parses_plain_money() {
129        assert_eq!(parse_pg_money_text("$123.45"), Some(dec("123.45")));
130    }
131
132    #[test]
133    fn parses_money_with_thousand_separators() {
134        // Postgres MONEY in en_US.UTF-8 emits grouping commas in output:
135        // `$1,234,567.89`. The filter drops everything but digits/`.`/`-`,
136        // so commas vanish before parsing.
137        assert_eq!(parse_pg_money_text("$1,234.56"), Some(dec("1234.56")));
138        assert_eq!(parse_pg_money_text("$1,234,567.89"), Some(dec("1234567.89")));
139    }
140
141    #[test]
142    fn parses_zero_money() {
143        assert_eq!(parse_pg_money_text("$0.00"), Some(dec("0")));
144    }
145
146    #[test]
147    fn parses_negative_money_leading_minus_outside_symbol() {
148        // Default en_US.UTF-8 form: `-$99.99`.
149        assert_eq!(parse_pg_money_text("-$99.99"), Some(dec("-99.99")));
150    }
151
152    #[test]
153    fn parses_negative_money_with_minus_after_symbol() {
154        // Some locales render as `$-99.99`; filter retains `-` so the parse
155        // still produces a negative value.
156        assert_eq!(parse_pg_money_text("$-99.99"), Some(dec("-99.99")));
157    }
158
159    #[test]
160    fn empty_string_returns_none() {
161        assert!(parse_pg_money_text("").is_none());
162    }
163
164    #[test]
165    fn unparseable_returns_none() {
166        // After filtering: `..` — bigdecimal rejects this.
167        assert!(parse_pg_money_text("$.").is_none());
168        assert!(parse_pg_money_text("abc").is_none());
169    }
170
171    #[test]
172    fn accounting_parens_misparsed_as_positive() {
173        // Documents a known limitation: locales that wrap negatives in
174        // parentheses ($99.99) lose the negative sign because the filter
175        // strips `(` and `)`. Postgres en_US.UTF-8 default does not use
176        // this form; if a deployment customises lc_monetary to one that
177        // does, the wire form will be wrong. Test pins behaviour so any
178        // future fix surfaces as an obvious diff.
179        assert_eq!(parse_pg_money_text("($99.99)"), Some(dec("99.99")));
180    }
181
182    #[test]
183    fn large_money_at_i64_max_cents() {
184        // $92,233,720,368,547,758.07 — the maximum positive Postgres MONEY,
185        // beyond f64's 15-digit safe range.
186        assert_eq!(
187            parse_pg_money_text("$92,233,720,368,547,758.07"),
188            Some(dec("92233720368547758.07"))
189        );
190    }
191}
sqlx_json/postgres.rs

sqlx_json/
postgres.rs
