postgres: create typed prepared statements

[danburkert]

Motivation

When using prepared statements with Postgres, it's sometimes necessary to provide type hints in order to let the server know the type of statement parameters, particularly when it can not otherwise be inferred. In terms of the tokio-postgres API this corresponds to using prepare_typed instead of prepare.

For example, the following query prepared via sea_query will fail when using prepare:

let txn: tokio_postgres::Transaction<'_> = todo!();
let query = Query::select().expr(Expr::val(1_i32)).to_owned();
let (stmt, params) = query.build_postgres(PostgresQueryBuilder);
let stmt = txn.prepare(&stmt).await?;
dbg!(txn.query(&stmt, &params.as_params()).await)?;

fails with the following Debug formatted error:

Error {
    kind: Db,
    cause: Some(
        DbError {
            severity: "ERROR",
            parsed_severity: Some(
                Error,
            ),
            code: SqlState(
                E22021,
            ),
            message: "invalid byte sequence for encoding \"UTF8\": 0x00",
            detail: None,
            hint: None,
            position: None,
            where_: Some(
                "unnamed portal parameter $1",
            ),
            schema: None,
            table: None,
            column: None,
            datatype: None,
            constraint: None,
            file: Some(
                "mbutils.c",
            ),
            line: Some(
                1669,
            ),
            routine: Some(
                "report_invalid_encoding",
            ),
        },
    ),
},

My interpretation of this error is that the server is inferring the $1 slot as a string, and when the binary encoded integer is sent, the server rejects it as invalidly encoded (Postgres has incorrect but consistent behavior when handling null bytes in UTF8 strings).

This is a simplified example so it may not be obvious why this is important, but it's not too difficult to find real world examples where the server is unable to infer a parameter type correctly. The usual solution is to use prepare_typed(), but I do not believe it's currently possible to get the necessary &[postgres_types::Type] from the sea_query query.

Proposed Solutions

Add an API to retrieve the list of parameter types of a sea_query query. I suggest the function signature: sea_query_postgres::PostgresValues::as_types(&self) -> Vec<postgres_types::Type>.