bestool_postgres/
stringify.rs

1use fraction::ToPrimitive;
2
3/// Convert a PostgreSQL row column to a JSON value
4///
5/// This function handles common PostgreSQL types and converts them to appropriate
6/// serde_json::Value representations. Unsupported types are converted to JSON strings
7/// using their text representation.
8pub fn postgres_to_json_value(row: &tokio_postgres::Row, idx: usize) -> serde_json::Value {
9	use tokio_postgres::types::Type;
10
11	let column = &row.columns()[idx];
12	match column.type_() {
13		&Type::BOOL => row
14			.try_get::<_, Option<bool>>(idx)
15			.ok()
16			.flatten()
17			.map(serde_json::Value::Bool)
18			.unwrap_or(serde_json::Value::Null),
19		&Type::INT2 => row
20			.try_get::<_, Option<i16>>(idx)
21			.ok()
22			.flatten()
23			.map(|v| serde_json::Value::Number(v.into()))
24			.unwrap_or(serde_json::Value::Null),
25		&Type::INT4 => row
26			.try_get::<_, Option<i32>>(idx)
27			.ok()
28			.flatten()
29			.map(|v| serde_json::Value::Number(v.into()))
30			.unwrap_or(serde_json::Value::Null),
31		&Type::INT8 => row
32			.try_get::<_, Option<i64>>(idx)
33			.ok()
34			.flatten()
35			.map(|v| serde_json::Value::Number(v.into()))
36			.unwrap_or(serde_json::Value::Null),
37		&Type::FLOAT4 => row
38			.try_get::<_, Option<f32>>(idx)
39			.ok()
40			.flatten()
41			.and_then(|v| serde_json::Number::from_f64(v as f64))
42			.map(serde_json::Value::Number)
43			.unwrap_or(serde_json::Value::Null),
44		&Type::FLOAT8 => row
45			.try_get::<_, Option<f64>>(idx)
46			.ok()
47			.flatten()
48			.and_then(serde_json::Number::from_f64)
49			.map(serde_json::Value::Number)
50			.unwrap_or(serde_json::Value::Null),
51		&Type::NUMERIC => row
52			.try_get::<_, Option<fraction::Decimal>>(idx)
53			.ok()
54			.flatten()
55			.and_then(|v| {
56				// Convert Decimal to f64, then to JSON number
57				let float_val = v.to_f64()?;
58				serde_json::Number::from_f64(float_val)
59			})
60			.map(serde_json::Value::Number)
61			.unwrap_or(serde_json::Value::Null),
62		&Type::TEXT | &Type::VARCHAR | &Type::BPCHAR | &Type::NAME => row
63			.try_get::<_, Option<String>>(idx)
64			.ok()
65			.flatten()
66			.map(serde_json::Value::String)
67			.unwrap_or(serde_json::Value::Null),
68		&Type::JSON | &Type::JSONB => {
69			let val: Option<serde_json::Value> = row.get(idx);
70			val.unwrap_or(serde_json::Value::Null)
71		}
72		&Type::TIMESTAMP => row
73			.try_get::<_, Option<jiff::civil::DateTime>>(idx)
74			.ok()
75			.flatten()
76			.map(|dt| serde_json::Value::String(dt.to_string()))
77			.unwrap_or(serde_json::Value::Null),
78		&Type::TIMESTAMPTZ => row
79			.try_get::<_, Option<jiff::Timestamp>>(idx)
80			.ok()
81			.flatten()
82			.map(|ts| serde_json::Value::String(ts.to_string()))
83			.unwrap_or(serde_json::Value::Null),
84		&Type::DATE => row
85			.try_get::<_, Option<jiff::civil::Date>>(idx)
86			.ok()
87			.flatten()
88			.map(|d| serde_json::Value::String(d.to_string()))
89			.unwrap_or(serde_json::Value::Null),
90		&Type::TIME => row
91			.try_get::<_, Option<jiff::civil::Time>>(idx)
92			.ok()
93			.flatten()
94			.map(|t| serde_json::Value::String(t.to_string()))
95			.unwrap_or(serde_json::Value::Null),
96		&Type::BYTEA => row
97			.try_get::<_, Option<Vec<u8>>>(idx)
98			.ok()
99			.flatten()
100			.map(|v| serde_json::Value::String(format!("\\x{}", hex::encode(v))))
101			.unwrap_or(serde_json::Value::Null),
102		// Array types
103		&Type::TEXT_ARRAY | &Type::VARCHAR_ARRAY => row
104			.try_get::<_, Option<Vec<String>>>(idx)
105			.ok()
106			.flatten()
107			.map(|v| {
108				serde_json::Value::Array(v.into_iter().map(serde_json::Value::String).collect())
109			})
110			.unwrap_or(serde_json::Value::Null),
111		&Type::INT2_ARRAY => row
112			.try_get::<_, Option<Vec<i16>>>(idx)
113			.ok()
114			.flatten()
115			.map(|v| {
116				serde_json::Value::Array(
117					v.into_iter()
118						.map(|n| serde_json::Value::Number(n.into()))
119						.collect(),
120				)
121			})
122			.unwrap_or(serde_json::Value::Null),
123		&Type::INT4_ARRAY => row
124			.try_get::<_, Option<Vec<i32>>>(idx)
125			.ok()
126			.flatten()
127			.map(|v| {
128				serde_json::Value::Array(
129					v.into_iter()
130						.map(|n| serde_json::Value::Number(n.into()))
131						.collect(),
132				)
133			})
134			.unwrap_or(serde_json::Value::Null),
135		&Type::INT8_ARRAY => row
136			.try_get::<_, Option<Vec<i64>>>(idx)
137			.ok()
138			.flatten()
139			.map(|v| {
140				serde_json::Value::Array(
141					v.into_iter()
142						.map(|n| serde_json::Value::Number(n.into()))
143						.collect(),
144				)
145			})
146			.unwrap_or(serde_json::Value::Null),
147		&Type::FLOAT4_ARRAY => row
148			.try_get::<_, Option<Vec<f32>>>(idx)
149			.ok()
150			.flatten()
151			.map(|v| {
152				serde_json::Value::Array(
153					v.into_iter()
154						.filter_map(|n| {
155							serde_json::Number::from_f64(n as f64).map(serde_json::Value::Number)
156						})
157						.collect(),
158				)
159			})
160			.unwrap_or(serde_json::Value::Null),
161		&Type::FLOAT8_ARRAY => row
162			.try_get::<_, Option<Vec<f64>>>(idx)
163			.ok()
164			.flatten()
165			.map(|v| {
166				serde_json::Value::Array(
167					v.into_iter()
168						.filter_map(|n| {
169							serde_json::Number::from_f64(n).map(serde_json::Value::Number)
170						})
171						.collect(),
172				)
173			})
174			.unwrap_or(serde_json::Value::Null),
175		&Type::BOOL_ARRAY => row
176			.try_get::<_, Option<Vec<bool>>>(idx)
177			.ok()
178			.flatten()
179			.map(|v| serde_json::Value::Array(v.into_iter().map(serde_json::Value::Bool).collect()))
180			.unwrap_or(serde_json::Value::Null),
181		// For unknown types, try to get as string
182		_ => row
183			.try_get::<_, Option<String>>(idx)
184			.ok()
185			.flatten()
186			.map(serde_json::Value::String)
187			.unwrap_or(serde_json::Value::Null),
188	}
189}
190
191pub fn get_value(
192	row: &tokio_postgres::Row,
193	column_index: usize,
194	unprintable_columns: &[usize],
195) -> String {
196	if !unprintable_columns.contains(&column_index) {
197		return format_value(row, column_index);
198	}
199
200	// For unprintable columns without async context, show a placeholder
201	// The actual text casting happens in the display layer which is async
202	"(binary data)".to_string()
203}
204
205pub fn format_value(row: &tokio_postgres::Row, i: usize) -> String {
206	// Check for void type first
207	let column = row.columns().get(i);
208	if let Some(col) = column
209		&& col.type_().name() == "void"
210	{
211		return "(void)".to_string();
212	}
213
214	// Try numeric type with fraction crate
215	if let Ok(v) = row.try_get::<_, fraction::Decimal>(i) {
216		v.to_string()
217	} else if let Ok(v) = row.try_get::<_, String>(i) {
218		v
219	} else if let Ok(v) = row.try_get::<_, i16>(i) {
220		v.to_string()
221	} else if let Ok(v) = row.try_get::<_, i32>(i) {
222		v.to_string()
223	} else if let Ok(v) = row.try_get::<_, i64>(i) {
224		v.to_string()
225	} else if let Ok(v) = row.try_get::<_, f32>(i) {
226		format!("{}", v)
227	} else if let Ok(v) = row.try_get::<_, f64>(i) {
228		format!("{}", v)
229	} else if let Ok(v) = row.try_get::<_, bool>(i) {
230		v.to_string()
231	} else if let Ok(v) = row.try_get::<_, Vec<u8>>(i) {
232		format!("\\x{encoded}", encoded = hex::encode(v))
233	} else if let Ok(v) = row.try_get::<_, jiff::Timestamp>(i) {
234		v.to_string()
235	} else if let Ok(v) = row.try_get::<_, jiff::civil::Date>(i) {
236		v.to_string()
237	} else if let Ok(v) = row.try_get::<_, jiff::civil::Time>(i) {
238		v.to_string()
239	} else if let Ok(v) = row.try_get::<_, jiff::civil::DateTime>(i) {
240		v.to_string()
241	} else if let Ok(v) = row.try_get::<_, serde_json::Value>(i) {
242		v.to_string()
243	} else if let Ok(v) = row.try_get::<_, Vec<String>>(i) {
244		format!("{{{}}}", v.join(","))
245	} else if let Ok(v) = row.try_get::<_, Vec<i32>>(i) {
246		format!(
247			"{{{}}}",
248			v.iter()
249				.map(|x| x.to_string())
250				.collect::<Vec<_>>()
251				.join(",")
252		)
253	} else if let Ok(v) = row.try_get::<_, Vec<i64>>(i) {
254		format!(
255			"{{{}}}",
256			v.iter()
257				.map(|x| x.to_string())
258				.collect::<Vec<_>>()
259				.join(",")
260		)
261	} else if let Ok(v) = row.try_get::<_, Vec<f32>>(i) {
262		format!(
263			"{{{}}}",
264			v.iter()
265				.map(|x| x.to_string())
266				.collect::<Vec<_>>()
267				.join(",")
268		)
269	} else if let Ok(v) = row.try_get::<_, Vec<f64>>(i) {
270		format!(
271			"{{{}}}",
272			v.iter()
273				.map(|x| x.to_string())
274				.collect::<Vec<_>>()
275				.join(",")
276		)
277	} else if let Ok(v) = row.try_get::<_, Vec<bool>>(i) {
278		format!(
279			"{{{}}}",
280			v.iter()
281				.map(|x| x.to_string())
282				.collect::<Vec<_>>()
283				.join(",")
284		)
285	} else {
286		// Try to get as string - many types can be retrieved as text
287		match row.try_get::<_, String>(i) {
288			Ok(v) => v,
289			Err(_) => match row.try_get::<_, Option<String>>(i) {
290				Ok(None) => "NULL".to_string(),
291				Ok(Some(v)) => v,
292				Err(_) => "NULL".to_string(),
293			},
294		}
295	}
296}
297
298pub fn can_print(row: &tokio_postgres::Row, i: usize) -> bool {
299	// Check for void type
300	let column = row.columns().get(i);
301	if let Some(col) = column
302		&& col.type_().name() == "void"
303	{
304		return true;
305	}
306
307	if row.try_get::<_, fraction::Decimal>(i).is_ok()
308		|| row.try_get::<_, String>(i).is_ok()
309		|| row.try_get::<_, i16>(i).is_ok()
310		|| row.try_get::<_, i32>(i).is_ok()
311		|| row.try_get::<_, i64>(i).is_ok()
312		|| row.try_get::<_, f32>(i).is_ok()
313		|| row.try_get::<_, f64>(i).is_ok()
314		|| row.try_get::<_, bool>(i).is_ok()
315		|| row.try_get::<_, Vec<u8>>(i).is_ok()
316		|| row.try_get::<_, jiff::Timestamp>(i).is_ok()
317		|| row.try_get::<_, jiff::civil::Date>(i).is_ok()
318		|| row.try_get::<_, jiff::civil::Time>(i).is_ok()
319		|| row.try_get::<_, jiff::civil::DateTime>(i).is_ok()
320		|| row.try_get::<_, serde_json::Value>(i).is_ok()
321		|| row.try_get::<_, Vec<String>>(i).is_ok()
322		|| row.try_get::<_, Vec<i32>>(i).is_ok()
323		|| row.try_get::<_, Vec<i64>>(i).is_ok()
324		|| row.try_get::<_, Vec<f32>>(i).is_ok()
325		|| row.try_get::<_, Vec<f64>>(i).is_ok()
326		|| row.try_get::<_, Vec<bool>>(i).is_ok()
327	{
328		return true;
329	}
330
331	matches!(row.try_get::<_, Option<String>>(i), Ok(None))
332}
333
334#[cfg(test)]
335mod tests {
336	use super::*;
337
338	#[tokio::test]
339	async fn test_void_type_handling() {
340		let connection_string =
341			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
342
343		let pool = crate::pool::create_pool(&connection_string, "test")
344			.await
345			.expect("Failed to create pool");
346
347		let client = pool.get().await.expect("Failed to get connection");
348
349		// Test void type - pg_sleep returns void
350		let rows = client
351			.query("SELECT pg_sleep(0)", &[])
352			.await
353			.expect("Query failed");
354
355		assert_eq!(rows.len(), 1);
356		let row = &rows[0];
357
358		// Check that void type can be printed
359		assert!(can_print(row, 0));
360
361		// Check that void type is formatted as "(void)"
362		let value = format_value(row, 0);
363		assert_eq!(value, "(void)");
364	}
365
366	#[tokio::test]
367	async fn test_float_handling() {
368		let connection_string =
369			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
370
371		let pool = crate::pool::create_pool(&connection_string, "test")
372			.await
373			.expect("Failed to create pool");
374
375		let client = pool.get().await.expect("Failed to get connection");
376
377		// Test float types
378		let rows = client
379			.query(
380				"SELECT 3.14::real as float4, 2.718281828::double precision as float8",
381				&[],
382			)
383			.await
384			.expect("Query failed");
385
386		assert_eq!(rows.len(), 1);
387		let row = &rows[0];
388
389		// Check that float types can be printed
390		assert!(can_print(row, 0));
391		assert!(can_print(row, 1));
392
393		// Check that float types are formatted
394		let value_f32 = format_value(row, 0);
395		let value_f64 = format_value(row, 1);
396
397		assert!(value_f32.contains("3.14"));
398		assert!(value_f64.contains("2.718"));
399	}
400
401	#[tokio::test]
402	async fn test_numeric_handling() {
403		let connection_string =
404			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
405
406		let pool = crate::pool::create_pool(&connection_string, "test")
407			.await
408			.expect("Failed to create pool");
409
410		let client = pool.get().await.expect("Failed to get connection");
411
412		// Test numeric type
413		let rows = client
414			.query("SELECT 123.456::numeric as num", &[])
415			.await
416			.expect("Query failed");
417
418		assert_eq!(rows.len(), 1);
419		let row = &rows[0];
420
421		// Numeric type should now be directly printable with fraction crate
422		assert!(can_print(row, 0));
423
424		// Check that the value can be formatted
425		let value = format_value(row, 0);
426		assert!(!value.is_empty());
427		assert_ne!(value, "(error)");
428		assert!(value.contains("123.456"));
429	}
430
431	#[tokio::test]
432	async fn test_numeric_arithmetic_with_text_cast() {
433		let connection_string =
434			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
435
436		let pool = crate::pool::create_pool(&connection_string, "test")
437			.await
438			.expect("Failed to create pool");
439
440		let client = pool.get().await.expect("Failed to get connection");
441
442		// Test numeric arithmetic with explicit text cast
443		let rows = client
444			.query("SELECT (12.34 + 37.28)::text as result", &[])
445			.await
446			.expect("Query failed");
447
448		assert_eq!(rows.len(), 1);
449		let row = &rows[0];
450
451		// With text cast, it should be printable
452		assert!(can_print(row, 0));
453
454		// Should be able to format the result
455		let value = format_value(row, 0);
456		assert!(!value.is_empty());
457		assert_ne!(value, "(error)");
458		assert!(value.starts_with("49.6"));
459	}
460
461	#[tokio::test]
462	async fn test_numeric_arithmetic_direct() {
463		let connection_string =
464			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
465
466		let pool = crate::pool::create_pool(&connection_string, "test")
467			.await
468			.expect("Failed to create pool");
469
470		let client = pool.get().await.expect("Failed to get connection");
471
472		// Test numeric arithmetic (the original failing case) - now should work directly
473		let rows = client
474			.query("SELECT 12.34 + 37.28", &[])
475			.await
476			.expect("Query failed");
477
478		assert_eq!(rows.len(), 1);
479		let row = &rows[0];
480
481		// With fraction crate, numeric should be directly printable
482		assert!(can_print(row, 0));
483
484		// Should be able to format the result
485		let value = format_value(row, 0);
486		assert!(!value.is_empty());
487		assert_ne!(value, "(error)");
488		assert!(value.starts_with("49.6"));
489	}
490
491	#[tokio::test]
492	async fn test_numeric_arithmetic_question_column() {
493		let connection_string =
494			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
495
496		let pool = crate::pool::create_pool(&connection_string, "test")
497			.await
498			.expect("Failed to create pool");
499
500		let client = pool.get().await.expect("Failed to get connection");
501
502		// Test numeric arithmetic with ?column? (this was failing in the REPL)
503		let rows = client
504			.query("SELECT 12.34 + 37.28", &[])
505			.await
506			.expect("Query failed");
507
508		assert_eq!(rows.len(), 1);
509		let row = &rows[0];
510
511		// Verify the column name is ?column?
512		assert_eq!(row.columns()[0].name(), "?column?");
513
514		// With fraction crate, numeric should now be directly printable
515		assert!(can_print(row, 0));
516
517		// Should be able to format directly without text casting
518		let value = format_value(row, 0);
519		assert!(!value.is_empty());
520		assert_ne!(value, "(error)");
521		assert!(value.starts_with("49.6"));
522	}
523}
bestool_postgres/stringify.rs

bestool_postgres/
stringify.rs
