Document PREPARE statements

[leoyvens]

DataFusion supports PREPARE statements, with flavors to suit all tastes such as explicit or inferred types, and positional or named arguments. I noticed docs for this feature are entirely missing from the SQL Reference (link).

[alamb]

Yes, thank you @leoyvens

@NGA-TRAN , @erratic-pattern and @jonahgao have done some great work in this area and indeed it would be great to get it documented

[dhegberg]

I've started to put together some basic documentation for prepared statements, but when generating examples I'm struggling to use named parameters.

Using SessionContext::sql() for example will hit this validation ensuring a $1 style placeholder is used rather than :name.

Creating a logical plan from the SQL string has a similar issue, preventing the user from later calling replace_params_with_values.

[alamb]

Thanks @dhegberg -- I am not sure if that is desired or not -- maybe it is worth a ticket

[jonahgao]

I've started to put together some basic documentation for prepared statements, but when generating examples I'm struggling to use named parameters.

Using SessionContext::sql() for example will hit this validation ensuring a $1 style placeholder is used rather than :name.

Creating a logical plan from the SQL string has a similar issue, preventing the user from later calling replace_params_with_values.

I think it should be supported because this test can be passed.

But it seems that we can't EXECUTE it by SQL.

DataFusion CLI v45.0.0
> prepare p1 as select $foo;
0 row(s) fetched.
Elapsed 0.003 seconds.

> execute p1(foo := 1);  🤔 Invalid statement: sql parser error: Expected: ), found: := at Line: 1, Column: 16

[dhegberg]

I think I'll post my documentation change without the named arguments. I'll then take a stab at adding support for named arguments with EXECUTE.

[dhegberg]

take

[dhegberg]

I was looking at how to support named parameters for PREPARE statements.

I expected the supported syntax in SQL dialects to looks something like:

PREPARE function_name AS SELECT * FROM example WHERE column_a > :a_param AND column_b < :b_param"

EXECUTE function_name USING :a_param = 10, :b_param = 20.5

That sort of syntax does not seem to be present in most dialects, with a few exceptions (like sqlite)

What is the expected behavior / syntax we're look for with named parameters in the context of a prepared statement?

[jonahgao]

Maybe we can follow DuckDB's syntax; it seems quite readable to me.

https://duckdb.org/docs/sql/query_syntax/prepared_statements.html#named-parameters-parameter