Terminal/console execution¶

    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¶

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¶

        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¶

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¶

        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¶

Node.js thin-wrapper¶

Customize CSS for the CGI output¶