postgres_scanner icon indicating copy to clipboard operation
postgres_scanner copied to clipboard

Support parameters in postgres_query

Open staticlibs opened this issue 2 months ago • 0 comments

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.

staticlibs avatar Oct 21 '25 22:10 staticlibs