pg-promise update where in custom array

Question

How can the following postgresql query be written using the npm pg-promise package?

update schedule
set student_id = 'a1ef71bc6d02124977d4'
where teacher_id = '6b33092f503a3ddcc34' and (start_day_of_week, start_time) in (VALUES ('M', (cast('17:00:00' as time))), ('T', (cast('19:00:00' as time))));

I didn't see anything in the formatter namespace that can help accomplish this. https://vitaly-t.github.io/pg-promise/formatting.html

I cannot inject the 'cast' piece into the '17:00:00' value without it being considered part of the time string itself.

The first piece of the query is easy. It's the part after VALUES that i can't figure out.

First piece:

var query = `update schedule
set student_id = $1
where teacher_id = $2 and (start_day_of_week, start_time) in (VALUES $3)`;

var inserts = [studentId, teacherId, values];

I'm using this messiness right now for $3 (not working yet), but it completely bypasses all escaping/security built into pg-promise:

const buildPreparedParams = function(arr, colNames){
  let newArr = [];
  let rowNumber = 0
  arr.forEach((row) => {
  const rowVal = (rowNumber > 0 ? ', ' : '') + 
  `('${row.startDayOfWeek}', (cast('${row.startTime}' as time)))`;
    newArr.push(rowVal);
  });
  return newArr;
};

The structure I am trying to convert into this sql query is:

[{
  "startTime":"17:00:00",
  "startDayOfWeek":"U"
 },
 {
  "startTime":"16:00:00",
  "startDayOfWeek":"T"
}]

Answer 1

Use CSV Filter for the last part: IN (VALUES $3:csv).

And to make each item in the array format itself correctly, apply Custom Type Formatting:

const data = [{
    startTime: '17:00:00',
    startDayOfWeek: 'U'
  },
  {
    startTime: '16:00:00',
    startDayOfWeek: 'T'
  }];

const values = data.map(d => ({
    toPostgres: () => pgp.as.format('(${startDayOfWeek},(cast(${startTime} as time))', d),
    rawType: true
}));

Now passing in values for $3:csv will format your values correctly:

('U',(cast('17:00:00' as time)),('T',(cast('16:00:00' as time))