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.
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.
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?
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
Fantastic, that works perfectly.
Thank you for that.