c/driver/postgresql: Support paramterized queries that return a result set

[paleolimbot]

What feature or improvement would you like to see?

Currently, any select query that is parameterized will fail unless it is an insert. This isn't critical for many applications but is annoying because it means that the ADBC driver can't be a drop-in replacement for an existing driver. Parameterized queries are a part of many existing workflows (although doesn't typically affect SQL generation tools like ibis or dbplyr, which do the preparation/inlining of parameters themselves). I have one anecdotal story of somebody who took a look at ADBC and ran across this error immediately and stopped investigating as well.

library(adbcdrivermanager)
#> Warning: package 'adbcdrivermanager' was built under R version 4.3.3

con <- adbc_database_init(
  adbcpostgresql::adbcpostgresql(), 
  uri = "postgresql://localhost:5432/postgres?user=postgres&password=password"
) |> 
  adbc_connection_init()

# Paramterized queries work for things like insert
write_adbc(data.frame(x = 1:5), con, "integers")
execute_adbc(con, "INSERT INTO integers VALUES ($1);", bind = data.frame(6:10))
read_adbc(con, "SELECT * from integers") |> 
  tibble::as_tibble()
#> # A tibble: 10 × 1
#>        x
#>    <int>
#>  1     1
#>  2     2
#>  3     3
#>  4     4
#>  5     5
#>  6     6
#>  7     7
#>  8     8
#>  9     9
#> 10    10

# Parameterized queries don't work for selects
read_adbc(con, "SELECT * from integers where x > $1", bind = data.frame(5L))
#> Error in adbc_statement_execute_query(stmt, stream, stream_join_parent = TRUE): NOT_IMPLEMENTED: [libpq] Prepared statements returning result sets are not implemented

This unfortunately may require a separate set of infrastructure from the COPY reader and might not be able to support all types as well; however, it is probably not hard to support some basic set of types once the infrastructure is in place.

[lidavidm]

There's also #855 which is implicitly the same thing (from the description)

[paleolimbot]

Ah I missed that!

[paleolimbot]

Reopening this since #2029 didn't quite cover the second half of this issue, which is wiring up the BindStream to the new reader. Working on this next!