pg_format

Instead of passing many command-line arguments, you can use a configuration file. pg_format automatically looks for configuration files in the following order:

1. ./.pg_format (current directory)
2. $HOME/.pg_format
3. $XDG_CONFIG_HOME/pg_format/pg_format.conf

You can override this behavior using the -c | --config FILE option or disable automatic RC file reading with -X | --no-rcfile.

By default, pg_format looks for configuration files to apply persistent formatting rules. If the --no-rcfile or -X flag is used, this behavior is disabled.

pg_format searches for configuration files in the following order:

1. ./.pg_format (current directory)
2. $HOME/.pg_format (user home directory)
3. $USERPROFILE/.pg_format (Windows user profile)
4. $XDG_CONFIG_HOME/pg_format/pg_format.conf (XDG config directory)

Configuration File Format: The configuration file uses a simple key = value format. Lines starting with a letter are treated as settings. Note that command-line arguments always override settings found in these files.

Example config file content:

spaces = 2
keyword-case = 1
format = html

To install pg_format on a Linux system, download the tarball from GitHub, unpack it, and use the Perl Makefile.PL to build and install. By default, it installs to /usr/local/bin. To install under /usr/ (similar to Debian), use INSTALLDIRS='perl' during the Makefile.PL step or INSTALLDIRS=vendor.

version=5.3 #please use the latest release version from github
wget https://github.com/darold/pgFormatter/archive/refs/tags/v${version}.tar.gz
tar xzf v${version}.tar.gz
cd pgFormatter-${version}/
perl Makefile.PL
make && sudo make install

# To install like Debian (under /usr/):
perl Makefile.PL INSTALLDIRS=vendor

You can build a pgFormatter Docker image using the provided Dockerfile in the repository.

docker build -t darold.net/pgformatter .

# Use it by piping SQL to stdin:
cat file.sql | docker run --rm -a stdin -a stdout -i darold.net/pgformatter -

You can use pg_format as the formatting engine for SQL files in Vim by adding the following to your ~/.vimrc:

au FileType sql setl formatprg=/usr/local/bin/pg_format\ -

Once configured, you can use standard Vim commands like gq to format text (e.g., ESC+gq+G to format the whole file).

The pg_format command can format SQL files or read from stdin.

Basic Usage: pg_format [options] file.sql

Common Patterns:

• Format a file and output to stdout: pg_format file.sql
• Format a file and save to a specific file: pg_format -o result.sql file.sql
• Format via pipe: cat file.sql | pg_format -
• Format in-place (overwrites the input file): pg_format -i file.sql

cat samples/ex1.sql | pg_format -
pg_format -n samples/ex1.sql
pg_format -f 2 -n -o result.sql samples/ex1.sql

The pg_format utility is a PostgreSQL SQL and PL/pgSQL code beautifier. It can read SQL from files or from stdin and output the formatted result to stdout or a specified file.

Basic Usage Patterns:

• From stdin:

  cat samples/ex1.sql | pg_format -

• From a file (output to stdout):

  pg_format samples/ex1.sql

• From a file (output to a specific file):

  pg_format -o result.sql samples/ex1.sql

• In-place formatting (overwrites the input file):

  pg_format -i samples/ex1.sql

• Removing comments and formatting:

  pg_format -n samples/ex1.sql

Use the -p or --placeholder option with a Perl regular expression to protect specific parts of your SQL from being modified. This is useful for protecting bit-shift operators, URLs in comments, or custom placeholders.

Example: Protecting bit-shift operators pg_format samples/ex9.sql -p '<<(?:.*)?>>'

Example: Protecting URLs in comments while wrapping at 60 chars pg_format samples/ex62.sql -C -w 60 -p 'https?://\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{1,256}\.[a-zA-Z0-9()]{1,6}\b([-a-zA-Z0-9()@:%_\+~#?&//=]*)'

You can use pg_format in a pre-commit configuration to ensure SQL files are formatted before committing. Use the --inplace flag to modify the files directly.

- repo: https://github.com/darold/pgFormatter
    rev: "v5.8"
    hooks:
      - id: pg_format
        args: ["--inplace", "--wrap-limit", "120"]
        files: '.*\.sql$'

When running in a CGI environment, pg_format automatically detects the context and outputs HTML for an online formatter.

To use it as a programmatic API, set the Accept HTTP header to application/json. This will return a JSON-formatted response.

Requirements:

• Install Perl CGI and JSON modules (libcgi-pm-perl and libjson-perl on Debian/Ubuntu, or perl-cgi and perl-json on Yum/CentOS).
• Place pg_format.cgi in your cgi-bin folder.
• Copy logo_pgformatter.png and icon_pgformatter.ico into the same CGI directory.