Terminal/console execution

    PostgreSQL SQL queries and PL/PGSQL code beautifier.

Arguments:

    file.sql can be a file, multiple files 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.
    -c | --config FILE    : use a configuration file. Default is to use local
                            file .pg_format or ~/.pg_format if they exists.
    -C | --wrap-comment   : with --wrap-limit, apply reformatting to comments.
    -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.
    -g | --nogrouping     : add a newline between statements in transaction
                            regroupement. Default is to group statements.
    -h | --help           : show this message and exit.
    -i | --inplace        : override input files with formatted content.
    -k | --keep-newline   : preserve empty line in plpgsql code.
    -L | --no-extra-line  : do not add an extra empty line at end of the output.
    -m | --maxlength SIZE : maximum length of a query, it will be cutted above
                            the given size. Default: no truncate.
    -M | --multiline      : enable multi-line search for -p or --placeholder.
    -n | --nocomment      : remove any comment from SQL code.
    -N | --numbering      : statement numbering as a comment before each query.
    -o | --output file    : define the filename for the output. Default: stdout.
    -p | --placeholder RE : set regex to find code that must not be changed.
    -r | --redshift       : add RedShift keyworks to the list of SQL keyworks.
    -s | --spaces size    : change space indent, default 4 spaces.
    -S | --separator STR  : dynamic code separator, default to single quote.
    -t | --format-type    : try another formatting type for some statements.
    -T | --tabs           : use tabs instead of space characters, when used
                            spaces is set to 1 whatever is the value set to -s.
    -u | --keyword-case N : Change the case of the reserved keyword. Default is
                            uppercase: 2. Values: 0=>unchanged, 1=>lowercase,
                            2=>uppercase, 3=>capitalize.
    -U | --type-case N    : Change the case of the data type name. Default is
                            lowercase: 1. Values: 0=>unchanged, 1=>lowercase,
                            2=>uppercase, 3=>capitalize.
    -v | --version        : show pg_format version and exit.
    -w | --wrap-limit N   : wrap queries at a certain length.
    -W | --wrap-after N   : number of column after which lists must be wrapped.
                            Default: puts every item on its own line.
    -X | --no-rcfile      : do not read ~/.pg_format automatically. The
                            --config / -c option overrides it.
    --extra-function FILE : file containing a list of function to use the same
                            formatting as PostgreSQL internal function.

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.

You need to install Perl CGI module first if it is not already the case:

        yum install perl-cgi
or
        apt-get install libcgi-pm-perl

following your distribution.

Option -W, --wrap-after

    SELECT a, b, c, d FROM t_1, t_2, t3 WHERE a = 10 AND b = 10;

will be formatted into with -W 4:

    SELECT a, b, c, d
    FROM t_1, t_2, t3
    WHERE a = 10
        AND b = 10;

Note this formatting doesn't fits well with sub queries in list.

Option -w, --wrap-limit

Option -C, --wrap-comment

Option -t, --format-type

  * newline in procedure/function parameter list
  * new line in PUBLICATION and POLICY DDL
  * keep enumeration in GROUP BY clause on a single line

Expect this list grow following alternative thoughts.

Option -g, --no-grouping

    BEGIN;
    INSERT INTO foo VALUES (1, 'text 1');
    INSERT INTO foo VALUES (2, 'text 2');
    ...
    COMMIT;

By disabling grouping of statement pgFormatter will always add an extra newline characters between statements just like outside a transaction:

    BEGIN;

    INSERT INTO foo VALUES (1, 'text 1');

    INSERT INTO foo VALUES (2, 'text 2');
    ...

    COMMIT;

This might add readability to not DML transactions.

Option -L, --no-extra-line

Option --extra-function

Configuration

To customize the CGI pg_format.cgi look for a configuration file named pg_format.conf in the same directory as the CGI script.

For a sample configuration file see doc/pg_format.conf.sample

To prevent pg_format to look at ~/.pg_format file you can use the command line option -X | --no-rcfile

Formatting from stdin

If you use the interactive mode you have to type `ctrl+d` after typing your SQL statement to format to end the typing.

        $ pg_format
        select * from customers;
        < ctrl+d >

You can use stdin in a one liner as follow:

        echo "select * from customers;" | pg_format

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.

There is also the (Neo)vim plugin for formatting code for many file types that support pg_format to format SQL file type. Thanks to Anders Riutta for the patch to (Neo)vim.

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.

Formatting from Visual Studio

        https://marketplace.visualstudio.com/items?itemName=bradymholt.pgformatter

For installation and use have a look at URL above.

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.

If you would like to wrap queries after 60 characters (-w 60) and to apply that limit to comments as well (-C), then urls in comments may get wrapped. If you would prefer not to wrap urls, you can use a regular expression to avoid wrapping urls. For example:

        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()@:%_\+.~#?&//=]*)'

will wrap the queries and the comments, but not the urls.

Prevent dynamic code formatting

Node.js thin-wrapper

Customize CSS for the CGI output