Обсуждение: COPY support for parameters
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:
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.
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.
Adrian Phinney
Adrian Phinney <adrian.phinney+postgres@gmail.com> writes:
> Does Postgres support COPY with parameters?
No. In general you can only use parameters in DML statements
(SELECT/INSERT/UPDATE/DELETE); utility statements don't cope,
mainly because most of them lack expression eval capability
altogether.
Perhaps the special case of COPY from a SELECT could be made
to allow parameters inside the SELECT, but I don't think
anyone has tried.
regards, tom lane