.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "PG_FORMAT 1p" .TH PG_FORMAT 1p "2021-04-26" "perl v5.32.1" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" pg_format \- PostgreSQL SQL syntax beautifier .SH "DESCRIPTION" .IX Header "DESCRIPTION" This \s-1SQL\s0 formatter/beautifier supports keywords from \s-1SQL\-92, SQL\-99, SQL\-2003, SQL\-2008, SQL\-2011\s0 and PostgreSQL specifics keywords. Works with any other databases too. .PP pgFormatter can work as a console program or as a \s-1CGI.\s0 It will automatically detect its environment and format output as text or as \s-1HTML\s0 following the context. .PP Keywords highlighting will only be available in \s-1CGI\s0 context. .SS "Terminal/console execution" .IX Subsection "Terminal/console execution" Usage: pg_format [options] file.sql .PP .Vb 1 \& PostgreSQL SQL queries and PL/PGSQL code beautifier. .Ve .PP Arguments: .PP .Vb 1 \& 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. .Ve .PP Options: .PP .Vb 10 \& \-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 or ~/.pg_format if it 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 file with formatted content. \& \-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. .Ve .PP Examples: .PP .Vb 3 \& cat samples/ex1.sql | pg_format \- \& pg_format \-n samples/ex1.sql \& pg_format \-f 2 \-n \-o result.sql samples/ex1.sql .Ve .SS "\s-1CGI\s0 context" .IX Subsection "CGI context" Install pg_format into your cgi-bin folder, grant execution on it as a \s-1CGI\s0 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 \s-1CGI\s0 directory, pg_format.cgi look for them in the same repository. .PP You have a live example without limitation than ten thousand characters in your \s-1SQL\s0 query here: .PP .Vb 1 \& http://sqlformat.darold.net/ .Ve .PP pg_format will automatically detected that it is running in a \s-1CGI\s0 environment and will output all html code needed to run an online code formatter site. There's nothing more to do. .PP You need to install Perl \s-1CGI\s0 module first if it is not already the case: .PP .Vb 3 \& yum install perl\-cgi \&or \& apt\-get install libcgi\-pm\-perl .Ve .PP following your distribution. .SH "INSTALLATION" .IX Header "INSTALLATION" Download the tarball from github and unpack the archive as follow: .PP .Vb 4 \& tar xzf pgFormatter\-x.x.tar.gz \& cd pgFormatter\-x.x/ \& perl Makefile.PL \& make && sudo make install .Ve .PP 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. .PP 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. .PP For example, to install everything just like Debian does, proceed as follow: .PP .Vb 1 \& perl Makefile.PL INSTALLDIRS=vendor .Ve .PP By default \s-1INSTALLDIRS\s0 is set to site. .PP Regression tests can be executed with the following command: .PP .Vb 1 \& make test .Ve .PP If you have docker installed you can build a pgFormatter image using: .PP .Vb 1 \& docker build \-t darold.net/pgformatter . .Ve .PP then just use it as .PP .Vb 1 \& echo file.sql | docker run \-\-rm \-a stdin \-a stdout \-i darold.net/pgformatter \- .Ve .SH "SPECIAL FORMATTING" .IX Header "SPECIAL FORMATTING" .SS "Option \-W, \-\-wrap\-after" .IX Subsection "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 \s-1SELECT\s0 and \s-1FROM\s0 list. For example the following query: .PP .Vb 1 \& SELECT a, b, c, d FROM t_1, t_2, t3 WHERE a = 10 AND b = 10; .Ve .PP will be formatted into with \-W 4: .PP .Vb 4 \& SELECT a, b, c, d \& FROM t_1, t_2, t3 \& WHERE a = 10 \& AND b = 10; .Ve .PP Note this formatting doesn't fits well with sub queries in list. .SS "Option \-w, \-\-wrap\-limit" .IX Subsection "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. .SS "Option \-C, \-\-wrap\-comment" .IX Subsection "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. .SS "Option \-t, \-\-format\-type" .IX Subsection "Option -t, --format-type" This option activate an alternative formatting that adds: .PP .Vb 2 \& * newline in procedure/function parameter list \& * new line in PUBLICATION and POLICY DDL .Ve .PP Expect this list grow following alternative thoughts. .SS "Option \-g, \-\-no\-grouping" .IX Subsection "Option -g, --no-grouping" By default pgFormatter groups all statements when they are in a transaction: .PP .Vb 5 \& BEGIN; \& INSERT INTO foo VALUES (1, \*(Aqtext 1\*(Aq); \& INSERT INTO foo VALUES (2, \*(Aqtext 2\*(Aq); \& ... \& COMMIT; .Ve .PP By disabling grouping of statement pgFormatter will always add an extra newline characters between statements just like outside a transaction: .PP .Vb 1 \& BEGIN; \& \& INSERT INTO foo VALUES (1, \*(Aqtext 1\*(Aq); \& \& INSERT INTO foo VALUES (2, \*(Aqtext 2\*(Aq); \& ... \& \& COMMIT; .Ve .PP This might add readability to not \s-1DML\s0 transactions. .SS "Option \-L, \-\-no\-extra\-line" .IX Subsection "Option -L, --no-extra-line" By default pgFormatter always adds an empty line after the end of a statement when it is terminated by a ; except in a plpgsql code block. If the extra empty line at end of the output is useless, you can remove it by adding this option to the command line. .SS "Option \-\-extra\-function" .IX Subsection "Option --extra-function" pgFormatter applies some formatting to the PostgreSQL internal functions call but it can not detect user defined function. It is possible to defined a list of functions into a file (one function name per line) and give it to pgFormatter through the \-\-extra\-function option that will be formatter as PostgreSQL internal functions. .SH "HINTS" .IX Header "HINTS" .SS "Configuration" .IX Subsection "Configuration" If the default settings of pg_format doesn't fit all your needs you can customize the behavior of pg_format by using a configuration file instead of repeating the command line option. By default pgFormatter look for file ~/.pg_format but you can choose an alternate configuration file using command line option \-c | \-\-config .PP To customize the \s-1CGI\s0 pg_format.cgi look for a configuration file named pg_format.conf in the same directory as the \s-1CGI\s0 script. .PP For a sample configuration file see doc/pg_format.conf.sample .PP To prevent pg_format to look at ~/.pg_format file you can use the command line option \-X | \-\-no\-rcfile .SS "Formatting from stdin" .IX Subsection "Formatting from stdin" You can execute pg_format without any argument or \- to give the \s-1SQL\s0 code to format through stdin. .PP If you use the interactive mode you have to type `ctrl+d` after typing your \&\s-1SQL\s0 statement to format to end the typing. .PP .Vb 3 \& $ pg_format \& select * from customers; \& < ctrl+d > .Ve .PP You can use stdin in a one liner as follow: .PP .Vb 1 \& echo "select * from customers;" | pg_format .Ve .SS "Formatting from \s-1VI\s0" .IX Subsection "Formatting from VI" With pgFormatter, you can just add the following line to your ~/.vimrc file: .PP .Vb 1 \& au FileType sql setl formatprg=/usr/local/bin/pg_format\e \- .Ve .PP This lets your gq commands use pgFormatter automagically. For example if you are on the first line, typing: .PP .Vb 1 \& ESC+gq+G .Ve .PP will format the entire file. .PP .Vb 1 \& ESC+gq+2j .Ve .PP will format the next two line. .PP Thanks to David Fetter for the hint. .PP There is also the (Neo)vim plugin for formatting code for many file types that support pg_format to format \s-1SQL\s0 file type. Thanks to Anders Riutta for the patch to (Neo)vim. .SS "Formatting from Atom" .IX Subsection "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. .PP Features: .PP .Vb 2 \& * Format selected text or a whole file via keyboard shortcut or command. \& * Format SQL files on save. .Ve .PP Installation: .PP Search for pg-formatter in Atom \s-1UI\s0 or get it via command line: .PP .Vb 1 \& apm install pg\-formatter .Ve .PP Usage: .PP Hit Ctrl-Alt-F to format selected text (or a whole file) or define your shortcut: .PP .Vb 1 \& \*(Aqctrl\-alt\-p\*(Aq: \*(Aqpg\-formatter:format\*(Aq .Ve .PP Also, you can automatically format \s-1SQL\s0 files on save (disabled by default). .PP You can download the package from url: .PP .Vb 1 \& https://atom.io/packages/pg\-formatter .Ve .PP the sources are available at https://github.com/gajus/pg\-formatter .PP Thanks to Alex Fedoseev for the atom package. .SS "Formatting from Visual Studio" .IX Subsection "Formatting from Visual Studio" Thanks to Brady Holt a Visual Studio Code extension is available to formats PostgresSQL \s-1SQL\s0 using pgFormatter. .PP .Vb 1 \& https://marketplace.visualstudio.com/items?itemName=bradymholt.pgformatter .Ve .PP For installation and use have a look at \s-1URL\s0 above. .SS "Prevent replacing code snippets" .IX Subsection "Prevent replacing code snippets" Using \-p or \-\-placeholder command line option it is possible to keep code untouched by pgFormatter in your \s-1SQL\s0 queries. For example, in query like: .PP .Vb 2 \& SELECT * FROM projects WHERE projectnumber \& IN <> AND username = <>; .Ve .PP 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: .PP .Vb 1 \& pg_format samples/ex9.sql \-p \*(Aq<<(?:.*)?>>\*(Aq .Ve .PP will not format the bit-shift like operators. .PP 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: .PP .Vb 1 \& pg_format samples/ex62.sql \-C \-w 60 \-p \*(Aqhttps?:\e/\e/(www\e.)?[\-a\-zA\-Z0\-9@:%._\e+~#=]{1,256}\e.[a\-zA\-Z0\-9()]{1,6}\eb([\-a\-zA\-Z0\-9()@:%_\e+.~#?&//=]*)\*(Aq .Ve .PP will wrap the queries and the comments, but not the urls. .SS "Prevent dynamic code formatting" .IX Subsection "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 \s-1EXECUTE\s0 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. .SS "Node.js thin-wrapper" .IX Subsection "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 .SS "Customize \s-1CSS\s0 for the \s-1CGI\s0 output" .IX Subsection "Customize CSS for the CGI output" You can change the \s-1HTML\s0 style rendered through the default \s-1CSS\s0 style by creating a file named custom_css_file.css into the pgFormatter \s-1CGI\s0 script directory. The default \s-1CSS\s0 will be fully overridden by this custom file content. You have to look at the generated \s-1HTML\s0 output to get the default \&\s-1CSS\s0 code used. .SH "AUTHORS" .IX Header "AUTHORS" pgFormatter is an original work from Gilles Darold with major code refactoring by Hubert depesz Lubaczewski. .SH "COPYRIGHT" .IX Header "COPYRIGHT" Copyright 2012\-2020 Gilles Darold. All rights reserved. .SH "LICENSE" .IX Header "LICENSE" pgFormatter is free software distributed under the PostgreSQL Licence. .PP 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.