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/// Determine whether the value at the given column is SQL NULL.
335///
336/// Type-aware: matches on the column type to pick the right `Option<T>` so it works for
337/// non-text columns (where `Option<String>` would be a type mismatch rather than NULL).
338pub fn is_null(row: &tokio_postgres::Row, i: usize) -> bool {
339	use tokio_postgres::types::Type;
340
341	macro_rules! check {
342		($t:ty) => {
343			matches!(row.try_get::<_, Option<$t>>(i), Ok(None))
344		};
345	}
346
347	match row.columns().get(i).map(|c| c.type_()) {
348		Some(&Type::BOOL) => check!(bool),
349		Some(&Type::INT2) => check!(i16),
350		Some(&Type::INT4) => check!(i32),
351		Some(&Type::INT8) => check!(i64),
352		Some(&Type::FLOAT4) => check!(f32),
353		Some(&Type::FLOAT8) => check!(f64),
354		Some(&Type::NUMERIC) => check!(fraction::Decimal),
355		Some(&Type::BYTEA) => check!(Vec<u8>),
356		Some(&Type::TIMESTAMP) => check!(jiff::civil::DateTime),
357		Some(&Type::TIMESTAMPTZ) => check!(jiff::Timestamp),
358		Some(&Type::DATE) => check!(jiff::civil::Date),
359		Some(&Type::TIME) => check!(jiff::civil::Time),
360		_ => {
361			if let Ok(v) = row.try_get::<_, Option<String>>(i) {
362				v.is_none()
363			} else {
364				matches!(row.try_get::<_, Option<Vec<u8>>>(i), Ok(None))
365			}
366		}
367	}
368}
369
370/// Render a cell's already-stringified text as a SQL literal suitable for an INSERT.
371///
372/// Numeric and boolean types are emitted bare; everything else is single-quoted with
373/// embedded quotes doubled. NULLs become the `NULL` keyword.
374pub fn sql_quote(ty: &tokio_postgres::types::Type, text: &str, is_null: bool) -> String {
375	use tokio_postgres::types::Type;
376
377	if is_null {
378		return "NULL".to_string();
379	}
380
381	match ty {
382		&Type::INT2
383		| &Type::INT4
384		| &Type::INT8
385		| &Type::FLOAT4
386		| &Type::FLOAT8
387		| &Type::NUMERIC
388		| &Type::OID => text.to_string(),
389		&Type::BOOL => {
390			if text == "true" || text == "t" {
391				"TRUE".to_string()
392			} else {
393				"FALSE".to_string()
394			}
395		}
396		_ => format!("'{}'", text.replace('\'', "''")),
397	}
398}
399
400#[cfg(test)]
401mod tests {
402	use super::*;
403
404	#[tokio::test]
405	async fn test_void_type_handling() {
406		let connection_string =
407			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
408
409		let pool = crate::pool::create_pool(&connection_string, "test")
410			.await
411			.expect("Failed to create pool");
412
413		let client = pool.get().await.expect("Failed to get connection");
414
415		// Test void type - pg_sleep returns void
416		let rows = client
417			.query("SELECT pg_sleep(0)", &[])
418			.await
419			.expect("Query failed");
420
421		assert_eq!(rows.len(), 1);
422		let row = &rows[0];
423
424		// Check that void type can be printed
425		assert!(can_print(row, 0));
426
427		// Check that void type is formatted as "(void)"
428		let value = format_value(row, 0);
429		assert_eq!(value, "(void)");
430	}
431
432	#[tokio::test]
433	async fn test_float_handling() {
434		let connection_string =
435			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
436
437		let pool = crate::pool::create_pool(&connection_string, "test")
438			.await
439			.expect("Failed to create pool");
440
441		let client = pool.get().await.expect("Failed to get connection");
442
443		// Test float types
444		let rows = client
445			.query(
446				"SELECT 3.14::real as float4, 2.718281828::double precision as float8",
447				&[],
448			)
449			.await
450			.expect("Query failed");
451
452		assert_eq!(rows.len(), 1);
453		let row = &rows[0];
454
455		// Check that float types can be printed
456		assert!(can_print(row, 0));
457		assert!(can_print(row, 1));
458
459		// Check that float types are formatted
460		let value_f32 = format_value(row, 0);
461		let value_f64 = format_value(row, 1);
462
463		assert!(value_f32.contains("3.14"));
464		assert!(value_f64.contains("2.718"));
465	}
466
467	#[tokio::test]
468	async fn test_numeric_handling() {
469		let connection_string =
470			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
471
472		let pool = crate::pool::create_pool(&connection_string, "test")
473			.await
474			.expect("Failed to create pool");
475
476		let client = pool.get().await.expect("Failed to get connection");
477
478		// Test numeric type
479		let rows = client
480			.query("SELECT 123.456::numeric as num", &[])
481			.await
482			.expect("Query failed");
483
484		assert_eq!(rows.len(), 1);
485		let row = &rows[0];
486
487		// Numeric type should now be directly printable with fraction crate
488		assert!(can_print(row, 0));
489
490		// Check that the value can be formatted
491		let value = format_value(row, 0);
492		assert!(!value.is_empty());
493		assert_ne!(value, "(error)");
494		assert!(value.contains("123.456"));
495	}
496
497	#[tokio::test]
498	async fn test_numeric_arithmetic_with_text_cast() {
499		let connection_string =
500			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
501
502		let pool = crate::pool::create_pool(&connection_string, "test")
503			.await
504			.expect("Failed to create pool");
505
506		let client = pool.get().await.expect("Failed to get connection");
507
508		// Test numeric arithmetic with explicit text cast
509		let rows = client
510			.query("SELECT (12.34 + 37.28)::text as result", &[])
511			.await
512			.expect("Query failed");
513
514		assert_eq!(rows.len(), 1);
515		let row = &rows[0];
516
517		// With text cast, it should be printable
518		assert!(can_print(row, 0));
519
520		// Should be able to format the result
521		let value = format_value(row, 0);
522		assert!(!value.is_empty());
523		assert_ne!(value, "(error)");
524		assert!(value.starts_with("49.6"));
525	}
526
527	#[tokio::test]
528	async fn test_numeric_arithmetic_direct() {
529		let connection_string =
530			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
531
532		let pool = crate::pool::create_pool(&connection_string, "test")
533			.await
534			.expect("Failed to create pool");
535
536		let client = pool.get().await.expect("Failed to get connection");
537
538		// Test numeric arithmetic (the original failing case) - now should work directly
539		let rows = client
540			.query("SELECT 12.34 + 37.28", &[])
541			.await
542			.expect("Query failed");
543
544		assert_eq!(rows.len(), 1);
545		let row = &rows[0];
546
547		// With fraction crate, numeric should be directly printable
548		assert!(can_print(row, 0));
549
550		// Should be able to format the result
551		let value = format_value(row, 0);
552		assert!(!value.is_empty());
553		assert_ne!(value, "(error)");
554		assert!(value.starts_with("49.6"));
555	}
556
557	#[tokio::test]
558	async fn test_numeric_arithmetic_question_column() {
559		let connection_string =
560			std::env::var("DATABASE_URL").expect("DATABASE_URL must be set for this test");
561
562		let pool = crate::pool::create_pool(&connection_string, "test")
563			.await
564			.expect("Failed to create pool");
565
566		let client = pool.get().await.expect("Failed to get connection");
567
568		// Test numeric arithmetic with ?column? (this was failing in the REPL)
569		let rows = client
570			.query("SELECT 12.34 + 37.28", &[])
571			.await
572			.expect("Query failed");
573
574		assert_eq!(rows.len(), 1);
575		let row = &rows[0];
576
577		// Verify the column name is ?column?
578		assert_eq!(row.columns()[0].name(), "?column?");
579
580		// With fraction crate, numeric should now be directly printable
581		assert!(can_print(row, 0));
582
583		// Should be able to format directly without text casting
584		let value = format_value(row, 0);
585		assert!(!value.is_empty());
586		assert_ne!(value, "(error)");
587		assert!(value.starts_with("49.6"));
588	}
589}
bestool_postgres/stringify.rs

bestool_postgres/
stringify.rs
