sqlx FromRow to specific type

[sdrap]

Disclaimer: my rust journey is about 10 hours away from running Hello World. So this might sound very naive.

To simplify: I have a query (postgresql) returning

• array_floats: arrays FLOAT8[]
• array_int: arrays Int8[]

SELECT
    CAST(array[number01, number02] as FLOAT8[]) as array_floats,
    CAST(array[index01, index02] as INT8[]) as array_int
FROM
   some_table

For scientific computing, I need to get the rows into the following struct with ndarrays (the integers are unsigned):

struct Data {
    array_floats: Array1::<f64>,
    array_int: Array1::<u64>
}

ndarray provides construction from vectors

use ndarray::Array;

let array = Array1::from_vec(vec![1., 2., 3., 4.]);

Working so far:

use sqlx::postgres::{PgPoolOptions};

#[derive(Debug)]
#[derive(sqlx::FromRow)]
pub struct Data{
    array_floats: Vec<f64>,
    array_int: Vec<i64>
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Load the environment for the database
    dotenv().expect("Failed to read .env");
    // Create connection pool
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&env::var("DATABASE_URL").expect("FAILED")).await?;
    
    // query
    let myquery = r#"
    SELECT
        CAST(array[number01, number02] as FLOAT8[]) as array_floats,
        CAST(array[index01, index02] as INT8[]) as array_int
    FROM
       some_table
        "#;
    
    // query_as to map into the struct Data
    let select_query = sqlx::query_as::<_, Data>(myquery);
    // Collect the rows into a vector of Data
    let rows: Vec<Data> = select_query.fetch_all(&pool).await?;
    
    Ok(())
}

I can get my struct with Vec<f64> and Vec<i64>. from there I can build up a new struct transferring Vec<i64> to Vec<u64> and then to a new struct with Array1::<f64> and Array1::<u64>.
This is however cumbersome and probably not the way to go.

Using cargo expand, I could have a look at the automatically derived code for the vector case above, but with many tries I can not figure out how to implement it for ndarrays (even the Vec<i64> to Vec<u64> is a mystery).

I truly spent some time trying to understand how to get it done but miserably failed (I got it for local timestampz conversion with serde). I kind of don't understand the documentation on how to map types between postgressql and special structs.

I would be very interested into understanding how to map this problem correctly in particular the underlying flow of functions bringing one structure into another. I am ready to write some documentation about it afterwards, sqlx is truly a nice piece of library.

[sdrap]

Discovered cargo expand so could figure out the boiler plate.

From:

...
#[derive(Debug)]
#[derive(sqlx::FromRow)]
pub struct Data{
    array_floats: Vec<f64>,
    array_int: Vec<i64>
}
...

To:

...
use ndarray::Array;

#[derive(Debug)]
pub struct Data{
    array_floats: Array1::<f64>,
    array_int: Array1::<i64>
}

// Implementation for the decode and type

impl<'a, R: ::sqlx::Row> ::sqlx::FromRow<'a, R> for Lob2
where
    &'a ::std::primitive::str: ::sqlx::ColumnIndex<R>,
    Vec<f64>: ::sqlx::decode::Decode<'a, R::Database>,
    Vec<f64>: ::sqlx::types::Type<R::Database>,
    Vec<i64>: ::sqlx::decode::Decode<'a, R::Database>,
    Vec<i64>: ::sqlx::types::Type<R::Database>,
{
    fn from_row(row: &'a R) -> ::sqlx::Result<Self> {
        // The following two lines are changed from the derived code of the previous example  for the conversion into the arrays
        let pb: Array1::<f64> = Array::from_vec(row.try_get("pb")?);
        let vb: Array1::<i64> = Array::from_vec(row.try_get("vb")?);
        ::std::result::Result::Ok(Lob2 {
            instrumentid,
            trade_time,
            pb,
            vb,
        })
    }
}
...