pg-formula

中文版

Go library that parses a subset of Microsoft Excel worksheet formulas and emits PostgreSQL-style scalar expressions. Parsing uses ANTLR 4; translation is implemented as a parse-tree visitor.

Module: github.com/SolaTyolo/pg-formula — repository: https://github.com/SolaTyolo/pg-formula.

In PostgreSQL generated column or expression-column settings, real table/column operands are written in the formula using {{…}} on input (e.g. {{a1}}, {{table.a1}}). Those markers compile to unquoted SQL fragments (a1, table.a1). A bare identifier such as table.a1 (no braces) is emitted as a single double-quoted PostgreSQL identifier ("table.a1"), i.e. one name that literally contains a dot—useful for literal labels in CONCAT, not for table + column a1.

Requirements

• Go 1.22+
• To regenerate the lexer/parser after editing grammar/ExcelFormula.g4:
  • Java runtime
  • ANTLR 4.13.2 JAR at tools/antlr-4.13.2-complete.jar
  • From repo root: go generate ./internal/parser/... (runs scripts/gen-antlr.sh)

API

out, err := formula.ToPostgres(`=IF({{orders.qty}}>0,SUM({{orders.amount}},{{orders.tax}}),0)`)

• Leading = is optional and stripped.
• {{name}} column refs (input): {{a1}} → SQL a1; {{table.a1}} → SQL table.a1 (qualified column). Nesting {{ / }} inside the name is rejected.
• Bare IDENT (input): table.a1 → SQL "table.a1" (one quoted identifier). Compare CONCAT({{table.a1}},2) → (table.a1::text)||(2::text) vs CONCAT(table.a1,2) → ("table.a1"::text)||(2::text).
• Excel string literals (double quotes in the formula): "orders.amount" → PostgreSQL 'orders.amount'.
• Go helpers: formula.BracedColumnContent (parse {{…}} text) and formula.SQLDoubleQuotedIdent (wrap a bare token—used internally for bare idents).

formulajs alignment

A curated list of Excel-style function names (see pkg/formula/formulajs_catalog.txt) seeds the registry (lowercase keys): common math, text, date, logical, and financial helpers that either lower to PostgreSQL or return ErrNotTranspilable. Unlisted names are unknown functions.

• Hand-tuned builtins (IF, SUM, CONCAT, …) override the generic emitters in pkg/formula/formulajs_registry.go.

Errors (caller-facing):

Error	Meaning
ErrUnknownFunction	Name not in the catalog (and not a hand-tuned override).
ErrArity	Known function, but argument count does not match the implemented signature.
ErrNotTranspilable	Name is in the catalog, but there is no safe PostgreSQL scalar lowering yet (e.g. NORM.DIST, FV / PV / PMT). This is not “unknown function”.

Regression coverage for ErrNotTranspilable vs ErrArity lives in pkg/formula/formulajs_test.go.

Supported features (v1)

• Literals: numbers, "strings" (Excel "" escape → PostgreSQL '' rules), TRUE / FALSE / NULL
• Operators: + - * / ^, unary + / -, comparisons = <> < > <= >=, string concat & (mapped to || with ::text where needed for CONCAT)
• Core worksheet functions (dedicated visitor paths): IF, AND, OR, NOT, SUM, AVERAGE, MIN, MAX, COUNT, CONCAT, TEXTJOIN (minimal delimiter join; second argument is accepted but not fully emulated)
• formulajs-backed emitters (see formulajs_emit*.go): common math/trig unary/binary helpers (ABS, POWER, ROUND, MOD, LOG, …), text (LEN, LOWER, MID, FIND, …), date/time fragments (DATE, YEAR, EDATE, …), variadic aggregates (PRODUCT, GCD, LCM, MEDIAN, STDEV.S / STDEV.P, …), extra math (INT, CEILING/FLOOR, ROUNDDOWN/ROUNDUP, CHOOSE), logical extras (IFS, SWITCH, XOR, IFNA, IFERROR with SQL approximations where noted in code), and a small financial subset (RRI, EFFECT, NOMINAL, SLN, SYD). Anything in the catalog without an emitter returns ErrNotTranspilable.

Semantics vs Excel

• NULL arithmetic: PostgreSQL null propagation differs from Excel; SUM is emitted as a flat + chain without COALESCE unless you extend the visitor.
• COUNT: implemented as a sum of CASE WHEN (expr) IS NULL THEN 0 ELSE 1 END per argument (count of non-null values in this encoding).
• AVERAGE: arithmetic mean of the argument list using n.0 as the divisor.

License

BSD-3-Clause (match ANTLR runtime and your org policy as needed).