0

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:

  1. Prepare statements for all different instruments
  2. Merge all instruments in one table with an additional field 'instrument' (not preferred)

But that dynamic solution would be highly preferred.

Shepmaster
  • 388,571
  • 95
  • 1,107
  • 1,366
ccptf
  • 1
  • 1
  • Are you sure you can parameterize the table name at all? Many Stack Overflow questions and answers indicate that you cannot [1](http://stackoverflow.com/q/1208442/155423) [2](http://stackoverflow.com/q/11312737/155423) [3](http://stackoverflow.com/q/25631006/155423). I don't think this is a Rust-specific issue. – Shepmaster May 04 '16 at 16:35
  • I'm not sure actually, thank you for that hint! – ccptf May 04 '16 at 18:36
  • I'm going to mark this as a duplicate; if you find resources that suggest that Postgres can support this, feel free to ping me to reopen (or go through the reopen queue). – Shepmaster May 04 '16 at 19:33

0 Answers0