GitHub user michalc edited a discussion: Querying PostgreSQL
generate_subscripts from Python with a dimension from bound parameters
Running the following in Python for a PostgreSQL database, querying
generate_subscripts with the dimension parameter from a bound argument:
```python
import adbc_driver_postgresql.dbapi
with
adbc_driver_postgresql.dbapi.connect("postgresql://postgres:[email protected]:5432/")
as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[],
$1)", [1,])
```
results in an error:
```
Traceback (most recent call last):
File "[...]", line 5, in <module>
cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1)",
[1,])
File "[...]/python3.12/site-packages/adbc_driver_manager/dbapi.py", line 730,
in execute
handle, self._rowcount = _blocking_call(
^^^^^^^^^^^^^^^
File "adbc_driver_manager/_lib.pyx", line 1606, in
adbc_driver_manager._lib._blocking_call_impl
File "adbc_driver_manager/_lib.pyx", line 1599, in
adbc_driver_manager._lib._blocking_call_impl
File "adbc_driver_manager/_lib.pyx", line 1241, in
adbc_driver_manager._lib.AdbcStatement.execute_query
File "adbc_driver_manager/_lib.pyx", line 260, in
adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: Failed to prepare
query: ERROR: function generate_subscripts(integer[], bigint) does not exist
LINE 1: SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1)
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
Query was:SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], $1). SQLSTATE:
42883
```
But when I query without the bound parameter
```python
import adbc_driver_postgresql.dbapi
with
adbc_driver_postgresql.dbapi.connect("postgresql://postgres:[email protected]:5432/")
as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[],
1)")
```
there is no error.
Or when I use a bound parameter with psycopg (that apparently uses server-side
binding and so presumably has to assign a PostgreSQL type to parameters?)
```python
import psycopg
with psycopg.connect("postgresql://postgres:[email protected]:5432/") as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT generate_subscripts('{NULL,1,NULL,2}'::int[],
%s)", [1,])
```
there is no error as well
It looks like adbc_driver_postgresql might be assuming that the `1` parameter
is a BIGINT, and since there isn't a matching generate_subscripts function
https://www.postgresql.org/docs/current/functions-srf.html, it errors? How can
I get ADBC to assume that it's just an INT? I know I can put a cast into the
SQL itself, but...
The context is that I'm making a SQLAlchemy Dialect for PostgreSQL/ADBC
https://github.com/michalc/pgarrow, and querying generate_subscripts is
actually coming from SQLAlchemy's base PostgreSQL dialect that I'm extending
from
https://github.com/sqlalchemy/sqlalchemy/blob/4e4c5a76fce2c13891cb405fe6d2e06c5d3406c1/lib/sqlalchemy/dialects/postgresql/base.py#L4126.
Ideally "it would just work" and I wouldn't have to override it to put in some
casts.
GitHub link: https://github.com/apache/arrow-adbc/discussions/2865
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
