Skip to content

Document PREPARE statements #13570

Open
Open

Description

@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).

Activity

  1. alamb commented on Nov 27, 2024

    @alamb
    Contributor

    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

  2. dhegberg commented on Feb 10, 2025

    @dhegberg
    Contributor

    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.

  3. alamb commented on Feb 10, 2025

    @alamb
    Contributor

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

  4. jonahgao commented on Feb 10, 2025

    @jonahgao
    Member

    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
  5. dhegberg commented on Feb 10, 2025

    @dhegberg
    Contributor

    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.

  6. dhegberg commented on Feb 10, 2025

    @dhegberg
    Contributor

    take

  7. dhegberg commented on Feb 19, 2025

    @dhegberg
    Contributor

    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?

  8. jonahgao commented on Feb 19, 2025

    @jonahgao
    Member

    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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

documentationImprovements or additions to documentation

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions