How to enumerate over columns with tokio-postgres when the field types are unknown at compile-time?

Question

I would like a generic function that converts the result of a SQL query to JSON. I would like to build a JSON string manually (or use an external library). For that to happen, I need to be able to enumerate the columns in a row dynamically.

let rows = client
   .query("select * from ExampleTable;")
   .await?;

// This is how you read a string if you know the first column is a string type.
let thisValue: &str = rows[0].get(0);

Dynamic types are possible with Rust, but not with the tokio-postgres library API.

The row.get function of tokio-postgres is designed to require generic inference according to the source code

Without the right API, how can I enumerate rows and columns?

Answer 1

You need to enumerate the rows and columns, doing so you can get the column reference while enumerating, and from that get the postgresql-type. With the type information it's possible to have conditional logic to choose different sub-functions to both: i) get the strongly typed variable; and, ii) convert to a JSON value.

for (rowIndex, row) in rows.iter().enumerate() {
    for (colIndex, column) in row.columns().iter().enumerate() {
        let colType: string = col.type_().to_string();
        
        if colType == "int4" { //i32
            let value: i32 = row.get(colIndex);
            return value.to_string();
        }
        else if colType == "text" {
            let value: &str = row.get(colIndex);
            return value; //TODO: escape characters
        }
        //TODO: more type support
        else {
            //TODO: raise error
        }
    }
}

Bonus tips for tokio-postgres code maintainers

Ideally, tokio-postgres would include a direct API that returns a dyn any type. The internals of row.rs already use the database column type information to confirm that the supplied generic type is valid. Ideally a new API uses would use the internal column information quite directly with improved FromSQL API, but a simpler middle-ground exists:-

It would be possible for an extra function layer in row.rs that uses the same column type conditional logic used in this answer to then leverage the existing get function. If a user such as myself needs to handle this kind of conditional logic, I also need to maintain this code when new types are handled by tokio-postgresql, therefore, this kind of logic should be included inside the library where such functionality can be better maintained.