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?;