Can't pass parameter to function

[mhanberg]

Description

I am trying to dynamically load parquet files into a query. I am able to do this on the duckdb cli, but using duckdbex, I seem to get an error

Repro

file = "some.parquet"
{:ok, db} = Duckdbex.open()
{:ok, conn} = Duckdbex.connection(db)
Duckdbex.query(conn, "INSTALL 'parquet';")
Duckdbex.query(conn, "INSTALL 'core_functions';")
Duckdbex.query(conn, "LOAD 'parquet'; LOAD 'core_functions'")

{:ok, result_ref} =
  Duckdbex.query(
    conn,
    """
    SELECT
      *
    FROM
      read_parquet([$1]);
    """,
    [file]
  )

results =
  Duckdbex.fetch_all(result_ref)

results
|> IO.inspect()

yields the error

** (MatchError) no match of right hand side value: {:error, "Invalid Input Error: Values were not provided for the following prepared statement parameters: 1"}

CLI

Running a similar query on the CLI seems to work.

[willfore]

I'm unclear about what you mean by passing parameters when using Duckdbex. If you want to dynamically pass in parquet files in your query what I do is populate a variable with a list of the files dynamically in the format of 's3://myfile', 's3://myfile'. Then in my code i subsitue that variable inside the brackets.

file = "'some.parquet', 'someother.parquet'" <- dynamically populate this
{:ok, db} = Duckdbex.open()
{:ok, conn} = Duckdbex.connection(db)
Duckdbex.query(conn, "INSTALL 'parquet';")
Duckdbex.query(conn, "INSTALL 'core_functions';")
Duckdbex.query(conn, "LOAD 'parquet'; LOAD 'core_functions'")

{:ok, result_ref} =
  Duckdbex.query(
    conn,
    """
    SELECT
      *
    FROM
      read_parquet([#{file}]);
    """
  )

results =
  Duckdbex.fetch_all(result_ref)

results
|> IO.inspect()

[mhanberg]

I'm unclear about what you mean by passing parameters when using Duckdbex

To clarify, I wan to pass positional parameters. In my description you can see I am able to accomplish this on the CLI. I would like to not rely on string interpolation.

The odd thing I noticed is that when i try to supply a parameter to the read_parquet function, it doesn't work. If I use a parameter for something like a where clause, then it does work.

[AlexR2D2]

Hi, thanks!
Currently DuckDB C++ API did't return expected parameters for table functions (read_parquet and other) and i don't know the type to convert the Erlang term (param) to, so as workaround i can propose to wrap function parameter to CAST:

Duckdbex.query(conn, "SELECT * FROM read_parquet(CAST($1 AS VARCHAR));", [file])
or 
Duckdbex.query(conn, "SELECT bin(CAST($1 AS VARCHAR));", ["goose"])