1
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
use ::futures::prelude::*;
use ::serde_pgrow::v0_2::prelude::*;
#[derive(Debug, ::serde::Deserialize)]
struct Record {
id: i64,
sub_ids: Vec<i64>,
notes: Vec<String>,
}
#[tokio::test]
#[ignore]
async fn test_flatten() {
let (client, connection) = ::tokio_postgres::connect(
"host=127.0.0.1 user=tests password=tests dbname=tests",
::tokio_postgres::NoTls,
)
.await
.unwrap();
let rows = async move {
client
.query(
/*
tests=# SELECT
tests-# R.id,
tests-# array_agg(R.sub_id) sub_ids,
tests-# array_agg(R.note) notes
tests-# FROM (
tests(# SELECT 1 :: BIGINT id, 1 :: BIGINT sub_id, '1.1' note
tests(# UNION ALL
tests(# SELECT 1 :: BIGINT, 2 :: BIGINT, '1.2'
tests(# UNION ALL
tests(# SELECT 2 :: BIGINT, 1 :: BIGINT, '2.1'
tests(# UNION ALL
tests(# SELECT 2 :: BIGINT, 2 :: BIGINT, '2.2'
tests(# ) R
tests-# GROUP BY
tests-# R.id
tests-# ORDER BY
tests-# R.id;
id | sub_ids | notes
----+---------+-----------
1 | {1,2} | {1.1,1.2}
2 | {1,2} | {2.1,2.2}
(2 rows)
*/
r#"
SELECT
R.id,
array_agg(R.sub_id) sub_ids,
array_agg(R.note) notes
FROM (
SELECT 1 :: BIGINT id, 1 :: BIGINT sub_id, '1.1' note
UNION ALL
SELECT 1 :: BIGINT, 2 :: BIGINT, '1.2'
UNION ALL
SELECT 2 :: BIGINT, 1 :: BIGINT, '2.1'
UNION ALL
SELECT 2 :: BIGINT, 2 :: BIGINT, '2.2'
) R
GROUP BY
R.id
ORDER BY
R.id
"#,
&[],
)
.await
.unwrap()
};
let (rows, _) = future::join(rows, connection).await;
let values = rows.cast::<Record>().unwrap();
println!("values: {:#?}", values);
}