Calling Stored Procedures setting parameters and returning parameters on PostgreSQL

help
1

I can now connect with my PostgreSQL from Rust, the problem is that every tutorial I've found only wants to run simple SQL statements rather than stored procedures.

So how do I call a PostgreSQL stored procedure from Rust, I know that a query with the "call" command will call stored procedures but that would need to hard code the parameters and I wouldn't be able to return values in the parameters.

If you just know the link to a good tutorial that would be good, I can't find any tutorial on this at all.

2

I've never used an SQL API that had support for directly returning stored procedure output parameters to the client language. I think you would likely need to query the output parameters with a normal select after CALL-ing the stored procedure.

3

It's easy in VBA, you just use parameters objects which are assigned to the output parameters of the stored procedure.

Does Rust have any parameter objects in any database object models?

4

I don't see a direct way to do it in the postgres C API. It's possible the VBA library you were using was doing something behind the scenes to make it work. It's also possible the C API has a way to do it that isn't well documented though.

The documentation for CALL indicates that output parameters are returned as a row (though weirdly you do have to include a value in the call expression for them still)

use std::error::Error;
use postgres::{types::ToSql, Client, NoTls};

fn main() -> Result<(), Box<dyn Error>> {
    let mut client = Client::connect(
        "host=localhost user=your_user password=your_password dbname=postgres",
        NoTls,
    )?;

    client.execute("\
CREATE OR REPLACE PROCEDURE EXAMPLE_PROCEDURE(one INT, two INT, o_one OUT INT, o_two OUT INT) LANGUAGE plpgsql AS $$
BEGIN
o_one := one + 1;
o_two := two + 1;
END;
$$;", &[])?;

    // The two NULLs are placeholders for the output parameters. 
    // You can technically write anything there, though the docs discourage anything other than NULL due to compatibility hazards
    let row = client.query_one(
        "CALL EXAMPLE_PROCEDURE($1, $2, NULL, NULL)",
        &[&1 as &(dyn ToSql + Sync), &2 as &(dyn ToSql + Sync)],
    )?;

    println!("{:?}", row.columns());
    println!("[{:?}, {:?}]", row.get::<_, i32>(0), row.get::<_, i32>(1));

    Ok(())
}

Output:

[Column { name: "o_one", type: Int4 }, Column { name: "o_two", type: Int4 }]
[2, 3]
2 Likes
5

Fantastic, that works perfectly.

Thank you for that.