NAME pg_format - PostgreSQL SQL syntax beautifier

DESCRIPTION This SQL formatter/beautifier supports keywords from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011 and PostgreSQL specifics keywords. Works with any other databases too.

pgFormatter can work as a console program or as a CGI. It will
automatically detect its environment and format output as text or as
HTML following the context. It can also return a JSON-formatted response
if used as CGI with 'Accept: application/json'.

Keywords highlighting will only be available in CGI context.

Terminal/console execution Usage: pg_format [options] file.sql

    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 not use
                            configuration file unless files ./.pg_format or
                            $HOME/.pg_format or the XDG Base Directory file
                            $XDG_CONFIG_HOME/pg_format/pg_format.conf exist.
    -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 PostgreSQL functions. 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.
                            Obsolete now, use --extra-keyword 'redshift' instead.
    -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      : don't read rc files automatically (./.pg_format or
                            $HOME/.pg_format or $XDG_CONFIG_HOME/pg_format).
                            The --config / -c option overrides it.
    --extra-function FILE : file containing a list of functions to use the same
                            formatting as PostgreSQL internal function.
    --extra-keyword FILE  : file containing a list of keywords to use the same
                            formatting as PostgreSQL internal keyword. Use
                            special value 'redshift' for support to Redshift
                            keywords defined internaly in pgFormatter.
    --no-space-function : remove space between function call and the open
                            parenthesis.
    --redundant-parenthesis: do not remove redundant parenthesis in DML.

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.

You need to install the Perl CGI and JSON modules first. If it is not
already the case do:

        yum install perl-cgi
        yum install perl-json
or
        apt install libcgi-pm-perl
        apt install libjson-perl

following your distribution.

INSTALLATION Following your Linux distribution you might need to install the autodie Perl module:

        sudo yum -y install perl-autodie

Download the tarball from github and unpack the archive as follow:

        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
        cd ../ && rm -rf v${version}.tar.gz && rm -rf pgFormatter-${version} #clean up

This will copy the Perl script pg_format in /usr/local/bin/pg_format
directory by default and the man page into
/usr/local/share/man/man1/pg_format.1. Those are the default
installation directory for 'site' install.

If you want to install all under /usr/ location, use INSTALLDIRS='perl'
as argument of Makefile.PL. The script will be installed into
/usr/bin/pg_format and the manpage into /usr/share/man/man1/pg_format.1.

For example, to install everything just like Debian does, proceed as
follow:

        perl Makefile.PL INSTALLDIRS=vendor

By default INSTALLDIRS is set to site.

Regression tests can be executed with the following command:

        make test

If you have docker installed you can build a pgFormatter image using:

        docker build -t darold.net/pgformatter .

then just use it as

        cat file.sql | docker run --rm -a stdin -a stdout -i darold.net/pgformatter -

SPECIAL FORMATTING Option -W, --wrap-after This option can be used to set number of column after which lists must be wrapped. By default pgFormatter puts every item on its own line. This format applies to SELECT and FROM list. For example the following query:

    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 This option wraps queries at a certain length whatever is the part of the query at the limit unless it is a comment. For example if the limit is reach in a text constant the text will be wrapped. Indentation is not included in the character count. This option is applied in all cases even if other options are used.

Option -C, --wrap-comment This option wraps comments at the length defined by -w, --wrap-limit whatever is the part of the comment. Indentation is not included in the character count.

Option -t, --format-type This option activate an alternative formatting that adds:

  * 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, --nogrouping By default pgFormatter groups all statements when they are in a transaction:

    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');
    ...

PgFormatter

Install / Use

README