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
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 |