Function AutoQuotePostgreSQLIdentifiers

Quotes mixed-case identifiers in a raw SQL string so PostgreSQL preserves their case.

MJ has a great deal of hand-written SQL — in resolvers, engines, dashboard components, and codegen templates — that references PascalCase columns and views unquoted (FROM __mj.vwAIAgentRuns, WHERE TestRun IS NULL). SQL Server resolves those case-insensitively; PostgreSQL folds an unquoted identifier to lowercase and then fails to find the mixed-case column codegen actually created. This function bridges that gap.

Keywords are recognized ONLY in ALL-CAPS

The keyword set is matched case-SENSITIVELY. This is the crux of the design, and it replaces a case-insensitive denylist that was wrong by construction: the set of SQL keywords and the set of MJ column names overlap (Name, Values, Length, Precision, Log, Rank, Action, Columns, Language, Month, Text are all real columns AND all keywords/type names/functions). Under case-insensitive matching every name in that intersection was emitted unquoted, folded to lowercase on PG, and failed with column "..." does not exist — while SQL Server, being case-insensitive, hid the defect from T-SQL-first authoring entirely.

Case-sensitive matching resolves the overlap cleanly because SQL dialects always emit keywords in upper case, so the keyword form and the column form are textually distinct: TEXT is the type, Text is the column.

An ALL-CAPS word that is NOT a keyword is still an identifier. ID and URL are all-caps by nature, so the predicate is !(isAllUpper && isKeyword) rather than a pure case rule — a pure case rule would fold them to id/url.

The rule

Applied to each bare word, in this order (order is load-bearing — the keyword branch runs before the function-call branch so VALUES( stays a keyword):

1. ALL-CAPS and in PostgreSQLQuotingKeywords → keyword/type → do not quote
2. In PostgreSQLStructuralKeywords (any case) → do not quote 2a. In PostgreSQLContextualStructuralKeywords AND followed by one of its permitted next words (Order By, Left Join) → do not quote. Anywhere else, an identifier.
3. Immediately followed by ( and not preceded by . → function call → do not quote
4. All-lowercase, or __mj_-prefixed → unchanged → do not quote
5. Starts uppercase, or is preceded by . → identifier → QUOTE

Rule 3 keeps mixed-case function spellings (Coalesce(, IsNull() working now that keyword matching is case-sensitive, and additionally fixes ALL-CAPS functions that were simply missing from the set (JSONB_BUILD_OBJECT( used to be quoted, and broke). The .-guard exists because MJ creates its stored procedures with quoted mixed-case names, so hand-written __mj.spCreateFoo(...) must still be quoted — a dot-qualified callable is far more likely to be an MJ object than a built-in. Rule 5's . clause is what makes __mj.vwAIAgentRuns work (MJ's vwXxx view convention starts lowercase).

Known caveat of rule 3: INSERT INTO Target(Name) with no space leaves Target bare, because a bare word before ( is indistinguishable from a call. There are no such occurrences in this repo, and the spaced form INSERT INTO Target (Name) quotes correctly. Related: x::Text now yields x::"Text"; write the cast as ::text or ::TEXT.

What is skipped

String literals (with '' escapes and E/N/U& prefixes), -- line comments, /* */ block comments (nested, as PostgreSQL specifies), dollar-quoted blocks ($$/$tag$), already-quoted identifiers (with "" escapes), square-bracketed SQL-Server-style identifiers, @-prefixed parameters, and PG positional parameters ($1). Skipping already-quoted identifiers is what makes this function idempotent — f(f(x)) === f(x).

Why comments are skipped rather than tolerated

Comment handling is not cosmetic. The scanner is a parity machine: an apostrophe inside an unrecognized comment opens a string-literal scan that runs to the next ', which is the OPENING quote of a real literal. From there every literal and every code region swaps roles. Against this repository's own shipped query SQL that rewrote literal VALUES — WHERE "StepType" = 'Prompt' became = '"Prompt"', and the jsonb_build_object keys in get-conversation-complete.pg.sql became '"ID"' — because line 10 of calculate-ai-agent-run-cost.pg.sql contains the word doesn't in a comment. Nothing throws; the query simply returns the wrong rows. postgresqlAutoQuote.shippedQueries.test.ts pins that whole file set as a no-op so it cannot come back.