pgsf postgres_sql_formatter v0.20.0

A guided tour for someone new to pgsf (postgres_sql_formatter): what problem it solves, the one idea you need to hold in your head, the day-to-day loop, and how it fits with the rest of the schema toolchain. For the exhaustive option-by-option reference, see the README; this guide is the "why" and the "how it fits together."

The problem it solves

PostgreSQL doesn't care how your SQL looks. CREATE, create, two spaces or a tab, OR REPLACE or not — all parse to the same thing. That freedom is the problem. Hand-written DDL drifts. A schema dumped from pg_dump comes out in PostgreSQL's house style, not yours. Two developers writing the "same" function produce diffs that are all whitespace and keyword case. Code review burns on layout instead of logic, and a schema you've exported to a file tree never quite matches the next export.

The fix is the same one Go and Rust settled on: one canonical form, applied mechanically. gofmt and cargo fmt ended the formatting argument by making the formatter — not a style guide PDF — the source of truth. pgsf does that for PostgreSQL SQL. There is no config file and no set of stylistic knobs to bikeshed: the house style is fixed, and pgsf is its executable form. You don't format SQL to a style; you run it through pgsf and get the style.

The one idea: format is an idempotent fixed point

The single property everything else rests on is idempotency:

format(format(x)) == format(x)

Run pgsf on any SQL and you get its canonical form. Run pgsf on that and nothing changes — it's already canonical. The output is a fixed point: a stable target every input maps onto, and stays at.

That one guarantee is what makes the tool trustworthy in the ways that matter:

• It's safe in a loop. Format-on-save, a pre-commit hook, a CI gate — none of them can fight each other, because once a file is canonical, formatting it again is a no-op.
• It enables a check mode. "Is this file already formatted?" is a real, decidable question (format(x) == x?) precisely because the canonical form is unique. That's what --check answers.
• It survives round-trips. Export a schema, format it, re-export, re-format — the formatted form is the one both passes converge on, so the tree on disk stays byte-stable across regenerations.

Idempotency isn't a nice-to-have here; it's the executable spec the formatter is built and fuzz-tested to satisfy. Alongside it sits token conservation — every significant token survives the transform except for a small, registered set of meaning-preserving rewrites (keyword case-folding, OR REPLACE stripping, type-alias normalization, and a handful more). The formatter restyles your SQL; it never silently loses a piece of it.

Getting started

just build                 # compile target/release/pgsf
sudo just install          # install to /usr/local/bin/pgsf (sudo: that dir is root-owned)
just install ~/.local/bin  # …or a PATH dir you own — no sudo, no rebuild

build and install are separate recipes, so you compile unprivileged and only sudo the copy; install never recompiles.

The simplest possible use — format a file and look at the result on stdout, touching nothing:

pgsf input.sql             # canonical form → stdout

Run pgsf with no arguments to print help.

The everyday loop

pgsf reads from a file argument, a directory, or stdin, and writes to stdout by default. The flags choose what it reads and whether it writes back.

Look before you leap — format to stdout and eyeball (or pipe into a diff):

pgsf input.sql                       # → stdout, original untouched
pgsf input.sql | diff input.sql -    # see exactly what would change
cat snippet.sql | pgsf               # read from stdin

Format in place once you trust it — -i rewrites the file:

pgsf -i input.sql                    # rewrite the file in canonical form

Format a whole tree — -d walks a directory recursively, formats every .sql file in parallel across your cores, and writes each back (it implies -i):

pgsf -d schema/                      # format every .sql under schema/, in place

Gate it in CI — --check writes nothing, prints the path of every file that isn't already canonical, and exits non-zero if any drift, exactly like gofmt -l / cargo fmt --check:

pgsf --check input.sql               # one file
pgsf --check -d schema/              # a whole tree — fail the pipeline on any drift
cat file.sql | pgsf --check          # stdin

A passing --check run is silent on stdout and exits 0. Under -d the drifting paths are printed sorted, so the output is stable across runs and easy to diff. The one rule for check mode: pass the same body/strip flags your canonical write uses, so the check enforces the very form the write produces.

Bodies: the two flags that change what gets formatted

By default pgsf formats everything — the statement, and the SQL or PL/pgSQL inside a function or procedure body. Two flags adjust that, and they're the only behavioral knobs the tool has. (Every example below is real, verified pgsf output — identifiers quote, and headers lay out on the tool's tab-column grid; don't expect plain, unquoted SQL back.)

-p, --preserve-body formats only the header of a CREATE FUNCTION / CREATE PROCEDURE (the create … as $delimiter$ framing) and leaves the body's content exactly as written. Use it when the body is hand-tuned and you want the canonical wrapper without touching the logic inside:

-- pgsf -p           (header canonicalized, body verbatim)
create function		"logic"."example" ()
	returns		setof text
	language	sql
	stable
	not leakproof
	called		on null input
	security	definer
	parallel	unsafe
as $function$
SELECT name FROM users
$function$;

-- pgsf              (no -p: the body is formatted too)
create function		"logic"."example" ()
	returns		setof text
	language	sql
	stable
	not leakproof
	called		on null input
	security	definer
	parallel	unsafe
as $function$
select			"name"
	from		"users"
$function$;

A few things happen to the header either way, -p or not: keywords lowercase, every identifier quotes, or replace is always dropped (see below), and the function's implicit default attributes — here called on null input and parallel unsafe — are made explicit even though the input never wrote them.

Functions and procedures always drop OR REPLACE, regardless of -s. The canonical function/procedure header never carries it — that's not what -s controls. A declared function is created, not replaced, full stop; both examples above came from a create or replace function input and neither kept the words.

-s, --strip-or-replace is what actually controls OR REPLACE — for the object types where it's optional: CREATE VIEW and CREATE TRIGGER.

-- pgsf               (no -s: or replace is kept)
create or replace view	"logic"."active_users"
	as		(
				select			"id",
							"name"
					from		"users"
					where		"active"
			);

-- pgsf -s            (or replace dropped)
create view		"logic"."active_users"
	as		(
				select			"id",
							"name"
					from		"users"
					where		"active"
			);

This is the schema-export form — a declared view is created, not replaced.

Both flags compose, and each does its own job across a tree: pgsf -p -s -d schema/ preserves function/procedure bodies and strips OR REPLACE from views and triggers; add --check to gate that exact form in CI.

Comments follow a deliberate two-tier rule that mirrors this body/header split: a statement-level inline comment is dropped (the canonical form documents objects with comment on statements), while a comment inside a function body is developer documentation and is kept. A comment-free body lays out canonically (each clause on its own aligned line); a comment-bearing one keeps its original statement layout and line breaks instead — re-laying it out could separate a comment from the code it documents — but its tokens still normalize (identifiers quote, keywords lowercase) same as anywhere else.

Where it fits: formatting a pgstd export

pgsf is one half of a two-tool schema pipeline, and the division of labor is clean: pgstd (postgres_schema_to_directory) extracts a live database into a per-schema, per-object-type file tree of plain SQL; pgsf then pretty-prints that tree in place, post-hoc:

pgstd -d develop -U postgres … schema/    # 1. extract plain SQL into a file tree
pgsf -d schema/                           # 2. canonicalize the tree in place

The two tools share no build coupling — pgstd emits SQL, any formatter could canonicalize it, and pgsf is the one that does. Because pgstd already emits separate constraint clauses and the redundant-cast policy is shared across both, the post-hoc format is byte-identical to what an integrated pass would produce, and idempotency makes the regenerated tree byte-stable: re-extract, re-format, and a clean tree diffs empty. That stability is exactly what lets --check -d serve as a CI gate against the committed schema.

Using it as a library

pgsf is a Rust library as well as a CLI; reach for the crate when you're building a tool (like a schema exporter) that needs to canonicalize SQL in-process rather than shelling out:

use postgres_sql_formatter::{format_sql, format_function_or_procedure, FormatOptions};

let options = FormatOptions {
    preserve_body: true,       // the -p flag
    strip_or_replace: true,    // the -s flag
};

let formatted = format_function_or_procedure(&raw_ddl, &options);  // strip_or_replace has no effect here — a function/procedure header always drops OR REPLACE
let formatted = format_sql(&sql, &options);   // dispatches by statement; strip_or_replace applies to CREATE VIEW / CREATE TRIGGER
let formatted = format_general_sql(&sql);     // non-function SQL

FormatOptions carries the same two behavioral choices the -p / -s flags expose, so the library and the CLI produce identical output for identical inputs.

Where to go next

• The README is the full reference — every option, the installation recipes, the git hooks, library usage, and worked before/after examples.
• design/formatting-contract.md states the contract for humans: idempotency, and the fact that the style is fixed and specified separately with pgsf as its executable form.
• design/invariants.md is the executable spec — the idempotency and token-/comment-conservation invariants as a corpus fuzz gate, and the allow-list of meaning-preserving canonical transforms.
• technical.md is the architecture: the tokenizer, the block-based recursive layout, PL/pgSQL handling, and the per-construct formatting rules.
• The design/ index covers the rest, including the lex → minimize → render refactor that makes the invariants hold by construction.