Write query output to a writer with query parameters

[vhakulinen]

Is there any way to write the output of a query with query parameters directly to a io.Writer without parsing it first in some golang type? I've noticed that here is pgconn.Conn.CopyTo which is close what I want, but it doesn't support passing in any query parameters and would require to do string templating which leads into a shaky ground.

[jackc]

There is nothing that directly uses an io.Writer, but pgtype.UndecodedBytes can be used as a scan target that does no decoding. You can also use Rows.RawValues if you want the undecoded bytes for all columns.

[vhakulinen]

I was hoping to avoid loading all the results to the application memory and instead passing all the data directly to the (http) client. This would be handy especially since postgresql can construct json directly with json_build_object. E.g. the use case would be something like this:

func handler(w http.ResponseWriter, r *http.Request) {
    db := ...
    accountID := ...
    after := ...

    db.Conn().CopyTo(
        r.Context(),
        w, `
        COPY (SELECT json_agg(json_build_object('val', val, 'timestamp', timestamp))
        FROM large_table WHERE account_id = $1 AND timestamp > $2) TO STDOUT`,
        accountID, after
    )
}

Anyways, after a good night's sleep I realized that there ain't that much of a benefit in there, since slow reading client(s) can easily clog up the whole database pool.

[vhakulinen]

Is it (technically) possible to add prepared statement support for the CopyTo / underlying copy protocol?

I'm looking to do similar thing as above, but for exporting CSVs (either directly to the client io.Writer, or to a server side buffered writer).

[jackc]

No, the copy protocol and the extended protocol (prepared statements) do not overlap.