How to use postgres' "values" and postgres.js to respond with multiple rows

[bas080]

So this works:

const db = require('postgres')(/* ... */)

> db`values (${db([1, 2])})`.then(console.log)
8 values (1,2) [ dynamic: true ]
Promise {
  <pending>,
  [Symbol(async_id_symbol)]: 5060,
  [Symbol(trigger_async_id_symbol)]: 5055,
  [Symbol(destroyed)]: { destroyed: false }
}
> [ { column1: 1, column2: 2 }, count: 1, command: 'SELECT' ]

That seems to work well. Now I'll try to have the query respond with multiple rows. I'll use the multiple insert in the docs as an example.

> db`values (${db([[1, 2], [2, 3]])})`.then(console.log)
Promise {
  <pending>,
  [Symbol(async_id_symbol)]: 5377,
  [Symbol(trigger_async_id_symbol)]: 5376,
  [Symbol(destroyed)]: { destroyed: false }
}
> Uncaught TypeError: str[i].charCodeAt is not a function
    at escape (/home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/types.js:78:16)
    at /home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/index.js:416:59
    at Array.reduce (<anonymous>)
    at escapeHelper (/home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/index.js:416:15)
    at parseHelper (/home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/index.js:386:12)
    at parse (/home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/index.js:363:11)
    at send (/home/ant/projects/rezerv/packages/database/node_modules/postgres/lib/index.js:188:65)

This is a simple example. I ran into the issue when trying to do the following:

          insert into users
            select * (
              select *, row_number() over (order by (select 0)) as n
              from accounts;
              where
                email = ${email}
              limit ${users.length}
            ) a join (
              select *, row_number() over (order by (select 0)) as n
              from (values (${sql(users)}))
            ) b
            on (a.n = b.n)`

So why would I want to do this? I want to perform a single query with the least amount of back and forth between the server and db. I don't want that network penalty or race conditions if it isn't necessary. In this case it isn't necessary to first perform a select and then perform inserts.

I have also tried hacking my way around but no success.

> db`select * from ${db([db.json(1)])}`.then(console.log)
8 select * from [object Object] [ dynamic: true ]
Promise {
  <pending>,
  [Symbol(async_id_symbol)]: 5789,
  [Symbol(trigger_async_id_symbol)]: 5784,
  [Symbol(destroyed)]: { destroyed: false }
}
> Uncaught Error: syntax error at or near "["

[bas080]

I have found a workaround to get this to work with postgres.js. It uses the sql.array and then postgres' unnest.

select * from unnest(${sql.array(users.map(sql.json))})  as a(person)

Still think this should work:

select * from (${sql(users.map(sql.josn))}) as a(person)