Skip to content
A PostgreSQL SQL syntax beautifier that can work as a console program or as a CGI. On-line demo site at http://sqlformat.darold.net/
Branch: master
Clone or download
Gilles Darold
Latest commit 8ddd2bd Dec 21, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
doc Add information about Perl CGI module requirement. Thanks to Huan Rua… Dec 21, 2018
lib/pgFormatter Remove extra concat entry Dec 14, 2018
samples Fix bad identation after IF EXISTS clause. Thanks to jostem fro the r… Dec 14, 2018
.gitignore Allow to override default CSS with a custom style in an external file. Dec 22, 2012
ChangeLog
LICENSE Change year in copyright. Feb 28, 2018
Makefile.PL
README Add information about Perl CGI module requirement. Thanks to Huan Rua… Dec 21, 2018
icon_pgformatter.ico
logo_pgformatter.png Add logo to the project and change title and style of the CGI part. Aug 27, 2018
pg_format Update ChangeLog and version to 3.3. Nov 20, 2018
regress_test.pl

README

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. May
    works with any other databases too.

    pgFormatter can work as a console program or as a CGI. It will
    automatically detect his environment and output as text or as HTML
    following the context.

    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 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.
        -t | --format-type    : try another formatting type for some statements.
        -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.

    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.

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

            tar xzf pgFormatter-x.x.tar.gz
            cd pgFormatter-x.x/
            perl Makefile.PL
            make && sudo make install

    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.

HINTS
  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.

  Formatting from Visual Studio
    Thanks to Brady Holt a Visual Studio Code extension is available to
    formats PostgresSQL SQL using pgFormatter.

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

    For installation and use have a look at URL above.

  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.

AUTHORS
    pgFormatter is an original work from Gilles Darold with major code
    refactoring by Hubert depesz Lubaczewski.

COPYRIGHT
    Copyright 2012-2018 Gilles Darold. All rights reserved.

LICENSE
    pgFormatter is free software distributed under the PostgreSQL Licence.

    A modified version of the SQL::Beautify Perl Module is embedded in
    pgFormatter with copyright (C) 2009 by Jonas Kramer and is published
    under the terms of the Artistic License 2.0.

You can’t perform that action at this time.