Possible to use a subscript in a named parameter reference?

[kmm]

I have an Postgres column that's an array of 3 timestamp types, and trying to fill them with data from an object that contains an array of 3 UNIX timestamps. Because they're UNIX timestamps I can't directly insert the array from the object, I have to convert them on insert using something like Postgres' builtin to_timestamp() function. The problem arises because to_timestamp doesn't have the capability to accept an array and output an array of converted values, so I'm trying to convert them piecemeal using subscripts and an ARRAY constructor like this:

var dataobj = { 
  report:{ 
    ts:[1677844801,1677844801,1677844801] 
  }
}

db.any('INSERT INTO my_table \
(timestamp_array_col) \
VALUES ( \
ARRAY[to_timestamp($<report.ts[0]>), \
to_timestamp($<report.ts[1]>), \
to_timestamp($<report.ts[2]>)] \
)', dataobj);

I was kinda expecting this to just work since the interpolator accepts dot subscripts like the .ts above, but when I log the generated query I just see an uninterpolated string inside to_timestamp for all the fields, like this:

to_timestamp($<report.ts[0]>)

What I want to see is this:

to_timestamp(1677844801)

These are example queries so there may be an error or two, they're just to illustrate what I'm trying to accomplish - fetch a subscript from an array that comes from an object that's referenced as a named parameter set.

Is there any way/a better way to do this, ideally without pre-chewing the data object inside node to convert the array of integer timestamps to Postgres-compatible timestamps? I at least like the idea of using PG's builtin conversion function for consistency since I'm using it to convert other timestamps that aren't inside arrays.

[vitaly-t]

For nested variables, only simple-property interpolation is currently supported,

i.e. replace

report.ts[0]
report.ts[1]
report.ts[2]

with

report.ts.0
report.ts.1
report.ts.2

And there's a definitely better way to do it, via Custom Type Formatting...

const tstamp = (t) => ({
    toPostgres: ()=> pgp.as.format('to_timestamp($1)', t),
    rawType: true
});

const dataObj = { 
  report:{ 
    ts: [1677844801,1677844801,1677844801].map(tstamp)
  }
}

Then you can use just '... VALUES(${report.ts})', dataObj), no need to manually format the query.

[kmm]

Thank you very much!