Cannot use value interpolation within a COPY query

[MarcelCutts]

Hello!

Firstly, thank you for this amazing library. It has made my work life significantly more pleasant.

Issue

I want to be able to interpolate values through COPY. An example is given below

const userId = "1";
const copyExample = await sql`
  COPY (SELECT
    u.name
    from Users u
    where u.id = ${userId}) TO stdout`

Running this provides me with PostgresError: could not determine data type of parameter $1. Appending ::text does not seem to help here.

COPY issues have been raised previously (#532, #520) and I see last week some commits were made to solve the issue. Again, thanks 😁! However I find my problems persist even using the HEAD of master.

The copy query I want to run has a slightly larger SELECT statement which includes a JOIN but is otherwise functionally similar. I have tried all sorts to get it to work (nested queries, using sql.unsafe for the inner select only etc) but in the end the only route I have found to work is constructing the whole template literal, with the value pre-baked, and then doing the rather frightening:

  const readableStream = await sql.unsafe(copyQuery).readable();
  res.writeHead(200, { "Content-Type": "text/csv" });
  await pipeline(readableStream, res);
  res.end();

While copying is cool, I appreciate this may not be fixed soon given that it's a more niche use case.

[porsager]

Hi @MarcelCutts - thanks a lot for the kind words!

The problem with copy is that you cannot push any parameters to copy, through the protocol, like regular queries (they have to be simple queries).

What you can do to make it feel better is have sql.unsafe only for the user id parameter. That's still only possible in latest master, but that way you could ensure only id characters were allowed there. Keeping the unsafe part localized and understandable.

I'm not at my computer at the moment, so won't try writing an example on my phone 😋 Hope my explanation is enough

[MarcelCutts]

The explanation is plenty, thanks! I will close this issue.