Source snapshot

packages/query-compiler/src/postgres.ts
Source revision 05d0bf2c2e56 · build details
Source revision: 05d0bf2c2e56.
TypeScript input SHA-256: aabe6cfcc4172b870d5e272142958e9ea8d8784c2aa23133156e5a7ee633318e
Generated 2026-09-04T23:58:25.590Z with TypeScript 6.0.3. Public TypeScript checks and declaration emit passed. Package runtime tests are separate.
1// The Postgres dialect leaf (§6.1/§6.2) — the product target. The walker is shared with the2// SQLite oracle; this file supplies only the Postgres-specific leaves:3//4//  - JSON assembly via `jsonb_build_object` / `jsonb_agg` / `'[]'::jsonb` (the design's §6.15//    choice — the direct analogue of `rindle-d2s`'s `json_object`, NOT z2s's `row_to_json`);6//  - explicit `ASC NULLS FIRST` / `DESC NULLS LAST` (§8 — Postgres's default is the opposite7//    of the engine's null-low order);8//  - the outbound projection reconciliation (temporal → epoch-ms);9//  - the inbound filter/paging cast strategy `$N::text::<type>`, ported verbatim from z2s's10//    `sql.ts` — the `::text` intermediate pins the driver's parameter description to text, and11//    the `::<type>` half is value-model↔native reconciliation (§6.2). Values are parameterized.12//13// NOTE: `$N` numbering is per-occurrence (no de-duplication of repeated values). z2s reuses a14// placeholder for a repeated (value,type); that is a plan-cache optimization, not correctness —15// deferred (203 §7 posture).1617import type { Dir } from "./ast.ts";18import type { ColumnType } from "./catalog.ts";19import type { Dialect, LitScalar } from "./dialect.ts";20import {21  formatTypeForLookup,22  isPgNativeStringType,23  isPgNumberType,24  isPgStringType,25  isPgTextRepresentedType,26  isTemporalType,27  needsTimeOfDayNormalization,28} from "./pg-types.ts";2930function quoteIdent(name: string): string {31  return `"${name.replace(/"/g, '""')}"`;32}3334/** The Postgres cast for a literal whose type is inferred from its JS value (no column). */35function pgTypeForLiteralType(litType: "boolean" | "number" | "string"): string {36  // `double precision` is IEEE-754 like a JS number, so it round-trips any zql number exactly.37  return litType === "boolean" ? "boolean" : litType === "number" ? "double precision" : "text";38}3940/**41 * The inbound value↔native cast for a filter/paging value against a known column type — the42 * verbatim port of z2s's `formatCommonToSingularAndPlural` (`sql.ts:194`). `vp` is the value43 * placeholder (`$N`, or `value` inside an array unnest). `isComparison` widens strings to bare44 * `text` and numbers to `double precision` so neither is forced to the column's width/precision.45 */46function pgCast(vp: string, type: string, isEnum: boolean, isComparison: boolean): string {47  const t = formatTypeForLookup(type);48  // Temporal: epoch-ms ↔ native. The zone-naive spellings get `AT TIME ZONE 'UTC'`.49  if (t === "timestamptz" || t === "timestamp with time zone") {50    return `to_timestamp(${vp}::text::numeric / 1000.0)`;51  }52  if (t === "date" || t === "timestamp" || t === "timestamp without time zone") {53    return `to_timestamp(${vp}::text::numeric / 1000.0) AT TIME ZONE 'UTC'`;54  }55  if (t === "timetz" || t === "time with time zone") {56    return `(${vp}::text::int * interval'1ms')::time`;57  }58  if (t === "time" || t === "time without time zone") {59    return `(${vp}::text::int * interval'1ms')::time AT TIME ZONE 'UTC'`;60  }61  if (t === "uuid") return `${vp}::text::uuid`;62  if (isEnum) return `${vp}::text::"${type}"`;63  if (isPgNativeStringType(type)) return isComparison ? `${vp}::text` : `${vp}::text::${type}`;64  if (isPgTextRepresentedType(type)) return `${vp}::text::${type}`;65  if (isPgNumberType(type)) {66    return isComparison ? `${vp}::text::double precision` : `${vp}::text::${type}`;67  }68  return `${vp}::text::${type}`;69}7071/** The text-pinned serialization of a bound value — z2s's `stringify` (`sql.ts:120`). Strings72 *  and enum/string columns bind their raw text; everything else binds its JSON text form so the73 *  `$N::text::…` cast re-parses it authoritatively. */74function stringifyForColumn(value: LitScalar, col: ColumnType): string | null {75  if (value === null) return null;76  if (col.isArray) return JSON.stringify(value);77  if (col.isEnum || isPgStringType(col.type)) return String(value);78  return JSON.stringify(value);79}8081function bindValue(82  params: unknown[],83  value: LitScalar,84  col: ColumnType | null,85  isComparison: boolean,86): string {87  const idx = params.length + 1;8889  // Free literal (no column context): infer the cast from the JS type.90  if (col === null) {91    if (value === null) {92      params.push(null);93      return `$${idx}`;94    }95    const litType = typeof value as "boolean" | "number" | "string";96    params.push(litType === "string" ? value : JSON.stringify(value));97    return `$${idx}::text::${pgTypeForLiteralType(litType)}`;98  }99100  params.push(stringifyForColumn(value, col));101  if (value === null && col.isArray) return `$${idx}`;102  if (col.isArray) {103    // Unnest a JSON array param, casting each element (z2s's `formatPlural`, `sql.ts:260`).104    return `ARRAY(SELECT ${pgCast("value", col.type, col.isEnum, isComparison)} FROM jsonb_array_elements_text($${idx}::text::jsonb))`;105  }106  return pgCast(`$${idx}`, col.type, col.isEnum, isComparison);107}108109/**110 * Project a stored column into the value model's representation (§6.2 outbound half). Temporal111 * columns become **integer epoch-ms** — the value model always snaps to whole milliseconds, so112 * `::bigint` rounds off the native µs resolution (unlike z2s, which projects the non-normalized113 * path as an unsnapped float). Time-of-day-with-offset types additionally wrap `mod 1 day`.114 * Enums (their label), uuid, numeric, json, and the scalar types project raw. See115 * `needsTimeOfDayNormalization` for the deliberate divergence from z2s on `timestamptz`.116 *117 * The rounding mode (`::bigint` = round-half-to-even) must match pg-source's projector for exact118 * parity — it is the one shared value-model contract (§10), not an independent choice here.119 */120function projectColumn(colRef: string, col: ColumnType | null): string {121  if (!col || col.isEnum || !isTemporalType(col.type)) return colRef;122  const normalize = needsTimeOfDayNormalization(col.type);123  const toMs = (epoch: string): string =>124    normalize ? `((${epoch})::bigint + 86400000) % 86400000` : `(${epoch})::bigint`;125  if (col.isArray) {126    return `CASE WHEN ${colRef} IS NULL THEN NULL ELSE ARRAY(SELECT ${toMs(`EXTRACT(EPOCH FROM unnest(${colRef})) * 1000`)}) END`;127  }128  return toMs(`EXTRACT(EPOCH FROM ${colRef}) * 1000`);129}130131function orderDir(dir: Dir): string {132  // Engine order is null-low; Postgres's default is the opposite, so make it explicit (§8).133  return dir === "asc" ? "ASC NULLS FIRST" : "DESC NULLS LAST";134}135136/**137 * The Postgres dialect — the BYO-Postgres server-mutator read target. Emits `(sql, params)`138 * with `$N::text::<type>` parameters and never imports a driver (Invariant 7).139 */140export const postgresDialect: Dialect = {141  name: "postgres",142  objectBuild: (parts) => `jsonb_build_object(${parts.join(", ")})`,143  groupArray: (elem, orderSql) => `jsonb_agg(${elem}${orderSql})`,144  emptyArray: "'[]'::jsonb",145  reassertJson: (x) => x, // jsonb is a real type; no re-assert needed146  trueLit: "TRUE",147  falseLit: "FALSE",148  orderDir,149  projectColumn,150  bindValue,151  quoteIdent,152};153