2023-04-29

PostgreSQL multidimensional arrays as parameters in Rust

I recently wanted to use a multidimensional array as a parameter to a query using rust-postgres and rust-postgres-array.

The “documentation” is lacking, so I went trawling Stack Overflow for an example of how the API’s supposed to work and didn’t find much––so I thought I’d throw this up in the hopes that I can help someone save a bit of time.

Without further ado, here’s the Cargo.toml

[package]
name = "scratch"
version = "0.1.0"
edition = "2021"

[dependencies]
postgres_array = "0.11.1"
postgres = "0.19"

Here’s the code for src/main.rs:

use postgres::{Client, NoTls};
use postgres_array::{Array, Dimension};

fn main() {
    let params = "host=localhost user=pg dbname=pg";
    let mut client = Client::connect(params, NoTls).unwrap();

    // Imagine you have a set of schema-qualified tables you want to check that the connected user
    // has `SELECT` privileges for.
    let tables = vec![["other", "t"], ["public", "t"]];

    let tables_len = tables.len();

    // `Array::from_parts` expects flattened structures, where the `dimensions` arguments describes
    // how to re-impose the dimensions on the values.
    let tables = Array::from_parts(
        tables.into_iter().map(|i| i.to_vec()).flatten().collect(),
        vec![
            // Outer dimension is the number of elements in our outer vector.
            Dimension {
                len: i32::try_from(tables_len).expect("fewer than i32::MAX tables"),
                // n.b. PG's indexing is 1-based, as is the argument in this struct.
                lower_bound: 1,
            },
            // Inner dimension is always 2. PG arrays should always be "rectilinear", i.e. they
            // cannot have interior "jagged" edges on any dimension; all lengths in each dimension
            // must be equal.
            Dimension {
                len: 2,
                // n.b. PG's indexing is 1-based, as is the argument in this struct.
                lower_bound: 1,
            },
        ],
    );

    let privileges_per_table = client
        .query(
            "
            WITH
                data AS (SELECT $1::text[] AS arr)
            SELECT
                t, has_table_privilege('pg', t, 'select') AS p
            FROM
                (
                    SELECT
                        -- This is by far the simplest way of returning
                        -- schema-qualified table names.
                        format('%I.%I', arr[i][1], arr[i][2]) AS t
                    FROM
                        data, ROWS FROM (generate_subscripts((SELECT arr FROM data), 1)) AS i
                )
                    AS o (t);",
            &[&tables],
        )
        .unwrap()
        .into_iter()
        .map(|row| (row.get("t"), row.get("p")))
        .collect::<Vec<(String, bool)>>();

    println!("Table privileges {:?}", privileges_per_table);
}