Formatting PostgreSQL with SQLinForm
PostgreSQL is one of the most standards-compliant databases available, yet it also has unique constructs that make formatting it a genuine challenge. Here is what SQLinForm has to handle.
Challenges Formatting PostgreSQL SQL
PL/pgSQL vs. SQL procedures
PostgreSQL supports multiple procedural languages for stored procedures and functions: PL/pgSQL, PL/Python, PL/Perl, SQL, and others. The LANGUAGE clause at the end of a CREATE FUNCTION specifies which language the body is written in. SQLinForm currently formats LANGUAGE plpgsql and LANGUAGE sql bodies; other languages are passed through as-is.
Dollar quoting with custom tags
While standard dollar quoting uses $$, PostgreSQL allows custom tags like $body$ or $func$. The formatter must track the specific opening tag to correctly identify the matching closing tag. Nested dollar-quoted strings with different tags — a valid PostgreSQL pattern — require careful stack-based tracking.
DO blocks — anonymous procedures
PostgreSQL’s DO command executes an anonymous code block. The body is a string literal (typically dollar-quoted), containing a complete PL/pgSQL block. The formatter must detect that the string literal is procedural code and apply appropriate indentation within it, while still treating it as a string from the outer SQL perspective.
Exception handling with GET STACKED DIAGNOSTICS
PostgreSQL’s exception handling syntax includes GET STACKED DIAGNOSTICS for extracting error details, which has a multi-assignment syntax unlike most other languages. Formatting the DIAGNOSTICS clause consistently with the rest of the EXCEPTION block requires specific handling.
LATERAL joins and complex FROM clauses
PostgreSQL makes heavy use of LATERAL joins, which allow later items in the FROM clause to reference earlier ones. LATERAL subqueries and LATERAL function calls produce deeply nested FROM clauses that require careful indentation to remain readable. The formatter must recognize LATERAL as a join modifier and treat it accordingly.
JSON operators and the formatting ambiguity
PostgreSQL’s JSON operators (->>, ->, #>>, @>, <@, etc.) create ambiguity in expression formatting because these multi-character operators must be recognized as single tokens rather than individual symbols. Without this, a -> b could be incorrectly formatted with spaces in the wrong places. SQLinForm handles these as compound operators throughout the tokenization pass.
CASCADE and RESTRICT on DROP statements
PostgreSQL enforces referential integrity strictly, so DROP statements often include CASCADE or RESTRICT. The formatter treats these as optional trailing clauses — simple in isolation, but they must be correctly recognized to avoid misidentifying them as the start of a new clause or statement.
About SQLinForm: SQLinForm is a SQL formatter supporting 25+ database dialects with 200+ formatting options — available for VS Code, SSMS, JetBrains, Notepad++, DBeaver, and as an online formatter. Your SQL never leaves your machine.