support `filename` argument in duckdb_read_csv()

[tjmahr]

The duckdb function READ_CSV() supports a filename argument which will add the csv path(s) as a column called filename. This is useful when bulk reading in csvs. This argument is not supported in the duckdb_read_csv() function used by this package.

The duckdb documentation for filename says

Add path of the containing file to each row, as a string column named filename. Relative or absolute paths are returned depending on the path or glob pattern provided to read_csv, not just filenames. Since DuckDB v1.3.0, the filename column is added automatically as a virtual column and this option is only kept for compatibility reasons.

I am not sure what to make of the "virtual column" detail though.

Example of duckdb_read_csv() vs. `tbl(db, "read_csv()")

library(dplyr)
path <- readr::readr_example("mtcars.csv")
db <- DBI::dbConnect(duckdb::duckdb())


# No filename column in output by default (that's okay)

duckdb::duckdb_read_csv(db, "temp", path, temporary = TRUE)
tbl(db, "temp")
#> # Source:   table<"temp"> [?? x 11]
#> # Database: DuckDB 1.4.1 [Tristan@Windows 10 x64:R 4.5.1/:memory:]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <dbl> <int> <dbl> <int> <dbl> <dbl> <dbl> <int> <int> <int> <int>
#>  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4
#>  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
#>  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
#>  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
#>  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
#> # ℹ more rows


# `filename = TRUE` not accepted or forwarded (point of this issue)

duckdb::duckdb_read_csv(db, "temp2", path, temporary = TRUE, filename = TRUE)
#> Warning in duckdb::duckdb_read_csv(db, "temp2", path, temporary = TRUE, :
#> Arguments passed to ... are currently not used
#> Error in read.table(file = file, header = header, sep = sep, quote = quote, : unused argument (filename = TRUE)
tbl(db, "temp2")
#> Error in `db_query_fields.DBIConnection()`:
#> ! Can't query fields.
#> ℹ Using SQL: SELECT * FROM (FROM temp2) q01 WHERE (0 = 1)
#> Caused by error in `dbSendQuery()`:
#> ! Catalog Error: Table with name temp2 does not exist!
#> Did you mean "temp"?
#> 
#> LINE 2: FROM (FROM temp2) q01
#>                    ^
#> ℹ Context: rapi_prepare
#> ℹ Error type: CATALOG
#> ℹ Raw message: Table with name temp2 does not exist!
#> Did you mean "temp"?
#> 
#> LINE 2: FROM (FROM temp2) q01
#>                    ^


# Desired behavior

q <- sprintf("read_csv('%s', filename = TRUE)", path)
tbl(db, q)
#> # Source:   SQL [?? x 12]
#> # Database: DuckDB 1.4.1 [Tristan@Windows 10 x64:R 4.5.1/:memory:]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb filename   
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>      
#>  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4 C:/Users/T…
#>  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4 C:/Users/T…
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1 C:/Users/T…
#>  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1 C:/Users/T…
#>  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2 C:/Users/T…
#>  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1 C:/Users/T…
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4 C:/Users/T…
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2 C:/Users/T…
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2 C:/Users/T…
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4 C:/Users/T…
#> # ℹ more rows

^{Created on 2025-11-20 with reprex v2.1.1}

[tjmahr]

For what it's worth, readr::read_csv() handles this feature with an readr::read_csv(..., id) argument:

id The name of a column in which to store the file path. This is useful when reading multiple input files and there is data in the file paths, such as the data collection date. If NULL (the default) no extra column is created.

(That seems a little better than slapping a filename column on the table.)