Select from values with dynamic number of values

[ansavchenco]

I want a query like this

SELECT
  ts_from,
  ts_to
FROM (VALUES
  ('2024-08-10 21:45:00'::TIMESTAMP, '2024-08-11 21:45:00'::TIMESTAMP),
  ('2024-08-11 21:45:00'::TIMESTAMP, '2024-08-12 21:45:00'::TIMESTAMP),
  ('2024-08-12 21:45:00'::TIMESTAMP, '2024-08-13 21:45:00'::TIMESTAMP)
) AS t(ts_from, ts_to)

but with a dynamic number of values coming from an array of arrays: [['2024-08-10 21:45:00', '2024-08-11 21:45:00'], ...] or array of objects.

I found in the docs here how this can be done statically

Here is what i tried

import { sql } from "kysely";

const intervals: [from: string, to: string][] = [
  ["one", "two"],
  ["three", "four"],
];

const intervalsStr = intervals
  .map(([from, to]) => `(${from}, ${to})`)
  .join(",");

const values = sql<{
  a: string;
  b: string;
}>`(values ${sql`${intervalsStr}`})`.as<"t">(sql`t(a,b)`);

const rows = await db
  .selectFrom(values)
  .select(["t.a", "t.b"])
  .execute();

but it escapes the array as one value

SELECT "t"."a", "t"."b"
FROM (VALUES $1) AS t (a, b)

and not sure how to make produce this instead:

SELECT "t"."a", "t"."b"
FROM (VALUES ($1, $2), ($3, $4), ...) AS t (a, b)

How could i achieve this?

[koskimas]

See this example https://kysely.dev/docs/recipes/extending-kysely#a-more-complex-example