CopyTo parameter support

[Drahflow]

Is your feature request related to a problem? Please describe.

CopyTo does not take parameters, only a string.

Describe the solution you'd like

CopyTo should be taking arguments ...any just like Exec does.

Describe alternatives you've considered

Exporting a way to correctly quote parameters into an SQL string. (But it is not a good alternative.)

Additional context

This seems to be a backward compatible change. Also it is parallel to how other APIs (e.g. Exec) of this package work.

Documentation of COPY with libpq: https://www.postgresql.org/docs/current/libpq-copy.html

The overall process is that the application first issues the SQL COPY command via PQexec or one of the equivalent functions. The response to this (if there is no error in the command) will be a PGresult object bearing a status code of PGRES_COPY_OUT or PGRES_COPY_IN (depending on the specified copy direction).

This suggests PQexecParams should also be usable (and thus parameters can be passed on the protocol level).

[jackc]

I don't think this is possible. pgx handles parameters via the extended protocol / prepared statement interface (and when using the simple protocol it simulates that as closely as possible). But the COPY TO SQL string doesn't actually take any parameters as far as PostgreSQL is concerned.

[Drahflow]

Indeed. I just tried a raw pgConn.Prepare on COPY (SELECT 1, $1, 3) TO stdout and got:

SD: &{Name: SQL:COPY (SELECT 1, $1, 3) TO stdout ParamOIDs:[] Fields:[]} Err: <nil>

... indicating the server did prepare the statement, but did not consider anything in there to be a parameter. 😞

So sanitizeForSimpleQuery it is then. I can do

	res, err := conn.PgConn().CopyTo(ctx, os.Stdout, "COPY (SELECT 1, $1, 3) TO stdout", "'\"';")

with Drahflow/jackc-pgx@master...Drahflow:jackc-pgx:2025-02-07-issue-2247
(not yet a real PR, obviously) and get correctly quoted results.

Do you think you'd like a PR along those lines? If yes, any opinions on how to get a "good" pgtype.Map into the pgconn?