How do you create a list of VALUES to be used inside a complex sql query with pg-promise?

Question

I have a rather complex query that I'm trying to put togehter with pg-promise:

sqlfile.sql

WITH insert_and_return AS
  (
    INSERT INTO myTable
      (param_01, param_02, param_03, param_id)
    VALUES
      (${param_01}, ${param_02}, ${param_03}, uuid_generate_v4())  
    RETURNING
      param_id
  )

named_data (name, values) AS
  (
    VALUES
      ${pairs}
  )

INSERT INTO anotherTable
  SELECT ir.param_id, nd.name, nd.values
  FROM insert_and_return AS ir, named_data AS nd;

I need the ${pairs} to look like the output of the pgp.helpers.values, a series of values inside parenthesis (https://vitaly-t.github.io/pg-promise/helpers.html#.values)

Query execution looks like this:

const data = { one: ['one'], two: ['two']};
// transform to data_pairs
const data_pairs = [ {key: 'one', value: ['one']}, {key: 'two', value: ['tow']} ];

const pairs = pgp.helper.values(data_pairs, ['key', 'value']);
// ('one',array['one']),('two',array['two'])

query = pgp.as.format(sqlFile, { param_01, param_02, param_03, pairs })
await db.none(query)

The final query appears to put another set of quotes around pairs values.

// '(''one'',array[''one'']),(''two'',array[''two''])'

Can someone recommend a way to generate a set of values like pairs to be used inside a sql query file without the added quotes?

Answer 1

This is a classic double-escaping (double-formatting) mistake.

The value of pairs is already pre-formatted, so it should be used as ${pairs:raw} inside SQL, to avoid being escaped for the second time.

See Raw Text filter.

Also, instead of this code:

query = pgp.as.format(sqlFile, { param_01, param_02, param_03, pairs })
await db.none(query)

you can just write it like this:

await db.none(sqlFile, { param_01, param_02, param_03, pairs });