Prepared Statements¶
The PREPARE statement allows for the creation and storage of a SQL statement with placeholder arguments.
The prepared statements can then be executed repeatedly in an efficient manner.
SQL Example
Create a prepared statement greater_than that selects all records where column “a” is greater than the parameter:
PREPARE greater_than(INT) AS SELECT * FROM example WHERE a > $1;
The prepared statement can then be executed with parameters as needed:
EXECUTE greater_than(20);
Rust Example
use datafusion::prelude::*;
#[tokio::main]
async fn main() -> datafusion::error::Result<()> {
// Register the table
let ctx = SessionContext::new();
ctx.register_csv("example", "tests/data/example.csv", CsvReadOptions::new()).await?;
// Create the prepared statement `greater_than`
let prepare_sql = "PREPARE greater_than(INT) AS SELECT * FROM example WHERE a > $1";
ctx.sql(prepare_sql).await?;
// Execute the prepared statement `greater_than`
let execute_sql = "EXECUTE greater_than(20)";
let df = ctx.sql(execute_sql).await?;
// Execute and print results
df.show().await?;
Ok(())
}
Inferred Types¶
If the parameter type is not specified, it can be inferred at execution time:
SQL Example
Create the prepared statement greater_than
PREPARE greater_than AS SELECT * FROM example WHERE a > $1;
Execute the prepared statement greater_than
EXECUTE greater_than(20);
Rust Example
// Create the prepared statement `greater_than`
let prepare_sql = "PREPARE greater_than AS SELECT * FROM example WHERE a > $1";
ctx.sql(prepare_sql).await?;
// Execute the prepared statement `greater_than`
let execute_sql = "EXECUTE greater_than(20)";
let df = ctx.sql(execute_sql).await?;
Positional Arguments¶
In the case of multiple parameters, prepared statements can use positional arguments:
SQL Example
Create the prepared statement greater_than
PREPARE greater_than(INT, DOUBLE) AS SELECT * FROM example WHERE a > $1 AND b > $2;
Execute the prepared statement greater_than
EXECUTE greater_than(20, 23.3);
Rust Example
// Create the prepared statement `greater_than`
let prepare_sql = "PREPARE greater_than(INT, DOUBLE) AS SELECT * FROM example WHERE a > $1 AND b > $2";
ctx.sql(prepare_sql).await?;
// Execute the prepared statement `greater_than`
let execute_sql = "EXECUTE greater_than(20, 23.3)";
let df = ctx.sql(execute_sql).await?;