I'm creating a piece of software in Rust to collect data from an API and store it in a PostgreSQL database.
I have one thread that is solely responsible for executing the SQL queries and storing the data. For this, I use the rust-postgres crate.
My database is set up to have different tables for different instruments. I'm attempting to prepare one single statement with a dynamic table name and dynamic values. This is my code for the prepared statement:
let dburl = "postgres://user:pw@localhost/dbname";
let dbconn = Connection::connect(dburl, SslMode::None);
if dbconn.is_err() {
println!("ERROR || Generic: Could not connect to postgres DB: {}",
dbconn.unwrap_err());
drop(receiver);
return;
}
let dbconn = dbconn.unwrap();
let stmt = dbconn.prepare("INSERT INTO $1 (timestmp, hours, minutes, seconds, nanoseconds, \
ask, bid, weekday, status) VALUES ($2::varchar, $3::int, $4::int, \
$5::int, $6::int, $7, $8, $9, $10::bool)");
if stmt.is_err() {
println!("ERROR || Generic: Failed to prepare SQL statement: {}",
stmt.unwrap_err());
drop(receiver);
return;
}
let stmt = stmt.unwrap();
let mut instr: String = "scheme.".to_owned();
loop {
let msg = receiver.recv();
if msg.is_err() {
break; // no more sender attached to channel
}
let msg = msg.unwrap();
instr.push_str(&msg.instrument);
let stmt_res = stmt.execute(&[&msg.timestmp,
&msg.hours,
&msg.minutes,
&msg.seconds,
&msg.nanoseconds,
&msg.ask,
&msg.bid,
&msg.weekday,
&msg.status]);
if stmt_res.is_err() {
println!("ERROR || Generic: Failed to execute prepared SQL statement: {}",
stmt_res.unwrap_err());
drop(receiver);
return;
}
let stmt_res = stmt_res.unwrap();
println!(">> {} line affected by SQL query.", stmt_res);
let instr: String = "scheme.".to_owned();
}
This results in the following error:
Error reported by Postgres: ERROR: syntax error at or near "$1"
I tried to use $1::regclass
as I thought my syntax is correct.
If this isn't feasible or easy to achieve, I thought about these options as well:
- Prepare statements for all different instruments
- Merge all instruments in one table with an additional field 'instrument' (not preferred)
But that dynamic solution would be highly preferred.