COPY support for parameters

From: Adrian Phinney <adrian(dot)phinney+postgres(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: COPY support for parameters
Date: 2019-02-14 15:11:45
Message-ID: CAB6u-ei4sCoUQnkXA1fL=1g=6cX7djWh_xSTHqz0fnngkuJ5mQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I'm trying to add support for specifying parameters when using a COPY
command to Npgsql (.NET's Postgres provider):
https://github.com/npgsql/npgsql/pull/2332

I've used the extended query protocol to send the COPY command. When I send
a COPY command without parameters, the backend issues the
appropriate CopyOutResponse/CopyInResponse/CopyData:

> COPY (select generate_series(1, 5)) TO STDOUT

When I add parameters, the backend will issue an ErrorResponse message
after issuing the ParseComplete and BindComplete messages:

> COPY (select generate_series(1, $1)) TO STDOUT
> Error: 42P02: there is no parameter $1

The owner of Npgsql confirmed that my use of the protocol seems correct
(parameters going over the wire, etc) but Postgres doesn't seem to be
resolving the parameters. Does Postgres support COPY with parameters?

More background on my use case: I'd like to be able to use COPY to
efficiently generate a CSV from our database with parameters are specified.
For example, generating a CSV of users recently created:

COPY (SELECT id, name, email FROM USERS where date_created > $1) TO STDOUT
WITH (DELIMITER ',', FORMAT CSV, HEADER true, ENCODING 'UTF8')

If COPY doesn't support parameters, we're required to build the SELECT
using quote_literal() or format() with the L format specifier -- both of
which are less safe than using a parameterized query when the parameter
comes from a user.

Thanks,

Adrian Phinney

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2019-02-14 15:14:14 Re: Ryu floating point output patch
Previous Message Tom Lane 2019-02-14 15:11:29 Re: Early WIP/PoC for inlining CTEs