Terminal/console execution

Usage: pg_format [options] file.sql

    PostgreSQL SQL queries and PL/PGSQL code beautifier.

Arguments:

    file.sql can be a file or use - to read query from stdin.
    Returning the SQL formatted to stdout or into a file specified with
    the -o | --output option.

Options:

    -a | --anonymize      : obscure all literals in queries, useful to hide
                            confidential data before formatting.
    -b | --comma-start    : in a parameters list, start with the comma (see -e)
    -B | --comma-break    : in insert statement, add a newline after each comma
    -d | --debug          : enable debug mode. Disabled by default.
    -e | --comma-end      : in a parameters list, end with the comma (default)
    -f | --function-case N: Change the case of the reserved keyword. Default is
                            unchanged: 0. Values: 0=>unchanged, 1=>lowercase,
                            2=>uppercase, 3=>capitalize.
    -F | --format STR     : output format: text or html. Default: text.
    -h | --help           : show this message and exit.
    -m | --maxlength SIZE : maximum length of a query, it will be cutted above
                            the given size. Default: no truncate.
    -n | --nocomment      : remove any comment from SQL code.
    -o | --output file    : define the filename for the output. Default: stdout.
    -p | --placeholder re : set regex to find code that must not be changed.
    -s | --spaces size    : change space indent, default 4 spaces.
    -S | --separator STR  : dynamic code separator, default to single quote.
    -u | --keyword-case N : Change the case of the reserved keyword. Default is
                            uppercase: 2. Values: 0=>unchanged, 1=>lowercase,
                            2=>uppercase, 3=>capitalize.
    -v | --version        : show pg_format version and exit.

Examples:

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

CGI context

Install pg_format into your cgi-bin folder, grant execution on it as a CGI script (maybe you should add the .cgi extension) and get it from your favorite browser. Copy files logo_pgformatter.png and icon_pgformatter.ico in the CGI directory, pg_format.cgi look for them in the same repository.

You have a live example without limitation than ten thousand characters in your SQL query here:

        http://sqlformat.darold.net/

pg_format will automatically detected that it is running in a CGI environment and will output all html code needed to run an online code formatter site. There's nothing more to do.

Formatting from VI

With pgFormatter, you can just add the following line to your ~/.vimrc file:

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

This lets your gq commands use pgFormatter automagically. For example if you are on the first line, typing:

        ESC+gq+G

will format the entire file.

        ESC+gq+2j

will format the next two line.

Thanks to David Fetter for the hint.

Formatting from Atom

If you use atom as your favorite editor you can install the pg-formatter package which is a Node.js wrapper of pgFormatter.

Features:

  * Format selected text or a whole file via keyboard shortcut or command.
  * Format SQL files on save.

Installation:

Search for pg-formatter in Atom UI or get it via command line:

    apm install pg-formatter

Usage:

Hit Ctrl-Alt-F to format selected text (or a whole file) or define your shortcut:

    'ctrl-alt-p': 'pg-formatter:format'

Also, you can automatically format SQL files on save (disabled by default).

You can download the package from url:

        https://atom.io/packages/pg-formatter

the sources are available at https://github.com/gajus/pg-formatter

Thanks to Alex Fedoseev for the atom package.

Prevent replacing code snippets

Using -p or --placeholder command line option it is possible to keep code untouched by pgFormatter in your SQL queries. For example, in query like:

        SELECT * FROM projects WHERE projectnumber
                IN <<internalprojects>> AND username = <<loginname>>;

you may want pgFormatter to not interpret << and >> as bit-shift keywords and modify your code snippets. You can use a Perl regular expression to instruct pgFormatter to keep some part of the query untouched. For example:

        pg_format samples/ex9.sql -p '<<(?:.*)?>>'

will not format the bit-shift like operators.

Prevent dynamic code formatting

By default pgFormatter takes all code between single quote as string constant and do not perform any formatting on this code. It is common to use a string as code separator to avoid doubling single quote in dynamic code generation, in this case pgFormatter can fail to auto detect the code separator. By default it will search for any string after the EXECUTE keyword starting with dollar sign. If it can not auto detect your code separator you can use the command line option -S or --separator to set the code separator that must be used.

Node.js thin-wrapper

Gajus Kuizinas has written a Node.js wrapper for executing pgFormatter. You can find it at https://github.com/gajus/pg-formatter

Customize CSS for the CGI output

You can change the HTML style rendered through the default CSS style by creating a file named custom_css_file.css into the pgFormatter CGI script directory. The default CSS will be fully overridden by this custom file content. You have to look at the generated HTML output to get the default CSS code used.