Inline parameters values into query SQL string

[uasan]

Hello.

Many SQL commands do not support parameters in queries, for example:

COPY (SELECT * FROM t WHERE id = $1 ) TO STDOUT;

CREATE VIEW AS SELECT * FROM t WHERE id = $1;

I plan to implement my own function, serialize the values by described SQL string, and substitute them into the SQL string.

Maybe there is a better solution? Thanks.

[porsager]

Yeah, I've been having the same issue with DO statements where the protocol parameters doesn't work because they collide with the internal parameters. Perhaps the solution should be to allow nesting sql.unsafe too, so that it'd look like this?

const safeValue = '9f7a8c42-0382-4eb9-85a7-23e88ef77bd3'
sql`COPY (SELECT * FROM t WHERE id = ${ sql.unsafe(safeValue) } ) TO STDOUT;`

[uasan]

I also did this, but there were SQL requests that must be given to JSON in one case and to COPY to csv in another, I made such a decision:

const { types } = await sql.unsafe(queryString, values).describe();

for (let i = 0; i < types.length; i++)
  values[i] = serializers[types[i]](values[i]);

return await sql.unsafe(inlineValuesToQuery(values));

perhaps if many users need it, it is worth making a built-in helper in the sql function so that it does this

[ChristophP]

I'm having a similar problem where I want to pass the table name and columns to a copy query dynamically.

const query = sql`COPY ${tableName} ${columns} FROM STDIN`;

The solution to nest unsafe would be sufficient for me.

[porsager]

@ChristophP that is different and should be possible to do as things are currently. You should wrap your table name and columns in sql()

eg like this (not tested as i don't know what your variables contain ;) ):

const query = sql`COPY ${ sql(tableName) } ${ sql(columns) } FROM STDIN`;

[ChristophP]

@porsager I tried this

sql`COPY ${ sql('users') } ${ sql(['peter', 'assi']) } FROM STDIN`

but I got "Could not infer helper mode".

[ChristophP]

Nesting sql.unsafe() is not something that's supported yet is it? I tried it on a copy query like this.

const cols = `(col1, col2)`;
sql`COPY some_table ${sql.unsafe(cols)} FROM STDIN`;

But my DB gave errors and it appeared that it only the first part of the query was sent to the DB.

[porsager]

What version are you seeing that with? (the screenshot error doesn't match the code you posted)

[porsager]

sql`COPY ${ sql('users') } ${ sql(['peter', 'assi']) } FROM STDIN`

sql(['peter', 'assi']), won't work there, but I'm looking into fixing that...

[porsager]

nesting sql.unsafe works now, so closing this :)

[acehand]

@porsager - am trying to do the following in the Latest Master

await sql`copy (select * from events where customer_id = ${sql.unsafe(customer_id)}) to stdout`.readable()`

but it just doesnt seem to work. My customer_id is an uuid String.

My intention is to make both the tableName and condition value to be interpolated. Can you tell me how I can get this to work. Thanks

[porsager]

You need to single quote the customer id 😉

[acehand]

thanks @porsager . that worked.