postgres_scanner
postgres_scanner copied to clipboard
Support parameters in postgres_query
This PR adds parameters support to postgres_query() function. Parameters are only supported when using the Postgres text protocol.
Parameters are specified as a STRUCT, that can be created inline using row() function:
SET pg_use_text_protocol=true
SELECT * FROM postgres_query('s1', 'SELECT $1::INTEGER, $2::TEXT', params=row(42, 'foo'))
----
42 foo
When the whole DuckDB SQL statement, that contains postgres_query() function, is used with PREPARE + EXECUTE (for example, from Python or Java client), then the external client-provided parameters will be forwarded to SQLite:
PREPARE p1 AS SELECT * FROM postgres_query('s1', 'SELECT $1::INTEGER, $2::TEXT', params=row(?::INTEGER, ?::VARCHAR))
EXECUTE p1(42, 'foo')
----
42 foo
EXECUTE p1(43, 'bar')
----
43 bar
DEALLOCATE p1
Parameters of integer and floating-point types can be specified using corresponding DuckDB types and are passed in binary form. All other parameter types nees to be passed as VARCHAR.
Testing: new test added.