A formatter for Postgres SQL files. One style, no options, so a diff shows what the query changed and never how it was typed.
go get -tool github.com/croaky/pgfmt/cmd/pgfmt
go tool pgfmt -c file.sql ... # check; exit 1 if any file would change
go tool pgfmt -w file.sql ... # format in place
go tool pgfmt file.sql ... # format to stdout
go tool pgfmt < file.sql # format stdin to stdout-c writes nothing and reports only formatting, never your edits, so a
clean working tree is not required. Without a flag, pgfmt prints every
named file, formatted, so pgfmt in.sql > out.sql always fills out.sql.
The library is the same thing without the flags:
out, err := pgfmt.Format(src)Keywords uppercase. Two spaces per nesting level. A clause keyword owns its line and its operands are indented under it, one per line, so adding a column is a one-line diff:
SELECT
id,
name
FROM
t
JOIN u
ON u.id = t.uid
WHERE
t.x = 1
ORDER BY
t.id DESC;CASE always expands. An expression that fits stays on its line; one
that does not wraps at its arguments. Header comments above a statement
pass through. A trailing -- comment does not: the printer joins tokens
onto a line, and a line comment would swallow what followed.
pgfmt reads the Postgres 17 and 18 grammar, and is tested against those
two servers. It accepts what they accept, so a file it formats can hold
a form an older server rejects: the underscore separator of 1_000_000
and the radix prefixes of 0x1f, 0o777, and 0b1010 all arrived in
Postgres 16.
pgfmt reads a plain '...' string with standard_conforming_strings
on, the Postgres default since 9.1. With the setting off, a backslash
before a quote ends a string at a different byte. Do not format SQL
for a server with the setting off.
Format lexes its own output and compares the token stream to the
input's, ignoring comments. A mismatch is an error rather than a written
file, so a bug in the printer costs a failed check and not a query that
means something else.
That check cannot see a token the lexer split, since both halves survive
the trip. So the lexer refuses instead where a split would hide: a
letter against the end of a number, such as 1e, is an error, and
Postgres rejects the same input.
The check also cannot see a token that ends at a different byte than
in Postgres. So the lexer ends each token where Postgres does. A name
continues through $, so like$1 is one name. In an E'...' string,
a backslash escapes the next byte. Block comments nest. A -- comment
ends at CR or LF. Postgres joins two strings that a newline separates,
and pgfmt refuses that form with an error.
An operator is the longest run of operator characters, so << and
<-> stay whole. A run that ends in + or - loses that tail, as in
Postgres, unless it holds one of ~ ! @ # ^ & | ` ? %. Each byte of
the tail is then one operator. A prefix against a quote is part of the
string: B'101', X'1F', N'abc', E'a', and U&'a' each stay one
literal, with the prefix in uppercase.
pgfmt refuses a ( that never closes and a ) with no (, as
Postgres does. It also refuses parens and CASE that nest deeper than
100 levels. An END closes only a CASE inside the same parens. Each
level costs stack, a deeper indent on each line, and one more pass to
measure the line. The limit keeps the output, memory, and time of
Format in proportion to the input, whatever the input. Hand-written
queries nest far less deep.
Development happens on sockeye, a
self-hosted review and CI server, which holds in progress branches.
GitHub receives main and the tags so go get works.
MIT