1NAME
2 pg_format - PostgreSQL SQL syntax beautifier
3
4DESCRIPTION
5 This SQL formatter/beautifier supports keywords from SQL-92, SQL-99,
6 SQL-2003, SQL-2008, SQL-2011 and PostgreSQL specifics keywords. Works
7 with any other databases too.
8
9 pgFormatter can work as a console program or as a CGI. It will
10 automatically detect its environment and format output as text or as
11 HTML following the context.
12
13 Keywords highlighting will only be available in CGI context.
14
15 Terminal/console execution
16 Usage: pg_format [options] file.sql
17
18 PostgreSQL SQL queries and PL/PGSQL code beautifier.
19
20 Arguments:
21
22 file.sql can be a file or use - to read query from stdin.
23
24 Returning the SQL formatted to stdout or into a file specified with
25 the -o | --output option.
26
27 Options:
28
29 -a | --anonymize : obscure all literals in queries, useful to hide
30 confidential data before formatting.
31 -b | --comma-start : in a parameters list, start with the comma (see -e)
32 -B | --comma-break : in insert statement, add a newline after each comma.
33 -c | --config FILE : use a configuration file. Default is to use local
34 file .pg_format or ~/.pg_format if they exists.
35 -C | --wrap-comment : with --wrap-limit, apply reformatting to comments.
36 -d | --debug : enable debug mode. Disabled by default.
37 -e | --comma-end : in a parameters list, end with the comma (default)
38 -f | --function-case N: Change the case of the reserved keyword. Default is
39 unchanged: 0. Values: 0=>unchanged, 1=>lowercase,
40 2=>uppercase, 3=>capitalize.
41 -F | --format STR : output format: text or html. Default: text.
42 -g | --nogrouping : add a newline between statements in transaction
43 regroupement. Default is to group statements.
44 -h | --help : show this message and exit.
45 -i | --inplace : override input file with formatted content.
46 -k | --keep-newline : preserve empty line in plpgsql code.
47 -L | --no-extra-line : do not add an extra empty line at end of the output.
48 -m | --maxlength SIZE : maximum length of a query, it will be cutted above
49 the given size. Default: no truncate.
50 -M | --multiline : enable multi-line search for -p or --placeholder.
51 -n | --nocomment : remove any comment from SQL code.
52 -N | --numbering : statement numbering as a comment before each query.
53 -o | --output file : define the filename for the output. Default: stdout.
54 -p | --placeholder RE : set regex to find code that must not be changed.
55 -r | --redshift : add RedShift keyworks to the list of SQL keyworks.
56 -s | --spaces size : change space indent, default 4 spaces.
57 -S | --separator STR : dynamic code separator, default to single quote.
58 -t | --format-type : try another formatting type for some statements.
59 -T | --tabs : use tabs instead of space characters, when used
60 spaces is set to 1 whatever is the value set to -s.
61 -u | --keyword-case N : Change the case of the reserved keyword. Default is
62 uppercase: 2. Values: 0=>unchanged, 1=>lowercase,
63 2=>uppercase, 3=>capitalize.
64 -U | --type-case N : Change the case of the data type name. Default is
65 lowercase: 1. Values: 0=>unchanged, 1=>lowercase,
66 2=>uppercase, 3=>capitalize.
67 -v | --version : show pg_format version and exit.
68 -w | --wrap-limit N : wrap queries at a certain length.
69 -W | --wrap-after N : number of column after which lists must be wrapped.
70 Default: puts every item on its own line.
71 -X | --no-rcfile : do not read ~/.pg_format automatically. The
72 --config / -c option overrides it.
73 --extra-function FILE : file containing a list of function to use the same
74 formatting as PostgreSQL internal function.
75
76 Examples:
77
78 cat samples/ex1.sql | pg_format -
79 pg_format -n samples/ex1.sql
80 pg_format -f 2 -n -o result.sql samples/ex1.sql
81
82 CGI context
83 Install pg_format into your cgi-bin folder, grant execution on it as a
84 CGI script (maybe you should add the .cgi extension) and get it from
85 your favorite browser. Copy files logo_pgformatter.png and
86 icon_pgformatter.ico in the CGI directory, pg_format.cgi look for them
87 in the same repository.
88
89 You have a live example without limitation than ten thousand characters
90 in your SQL query here:
91
92 http://sqlformat.darold.net/
93
94 pg_format will automatically detected that it is running in a CGI
95 environment and will output all html code needed to run an online code
96 formatter site. There's nothing more to do.
97
98 You need to install Perl CGI module first if it is not already the case:
99
100 yum install perl-cgi
101 or
102 apt-get install libcgi-pm-perl
103
104 following your distribution.
105
106INSTALLATION
107 Download the tarball from github and unpack the archive as follow:
108
109 tar xzf pgFormatter-x.x.tar.gz
110 cd pgFormatter-x.x/
111 perl Makefile.PL
112 make && sudo make install
113
114 This will copy the Perl script pg_format in /usr/local/bin/pg_format
115 directory by default and the man page into
116 /usr/local/share/man/man1/pg_format.1. Those are the default
117 installation directory for 'site' install.
118
119 If you want to install all under /usr/ location, use INSTALLDIRS='perl'
120 as argument of Makefile.PL. The script will be installed into
121 /usr/bin/pg_format and the manpage into /usr/share/man/man1/pg_format.1.
122
123 For example, to install everything just like Debian does, proceed as
124 follow:
125
126 perl Makefile.PL INSTALLDIRS=vendor
127
128 By default INSTALLDIRS is set to site.
129
130 Regression tests can be executed with the following command:
131
132 make test
133
134 If you have docker installed you can build a pgFormatter image using:
135
136 docker build -t darold.net/pgformatter .
137
138 then just use it as
139
140 cat file.sql | docker run --rm -a stdin -a stdout -i darold.net/pgformatter -
141
142SPECIAL FORMATTING
143 Option -W, --wrap-after
144 This option can be used to set number of column after which lists must
145 be wrapped. By default pgFormatter puts every item on its own line. This
146 format applies to SELECT and FROM list. For example the following query:
147
148 SELECT a, b, c, d FROM t_1, t_2, t3 WHERE a = 10 AND b = 10;
149
150 will be formatted into with -W 4:
151
152 SELECT a, b, c, d
153 FROM t_1, t_2, t3
154 WHERE a = 10
155 AND b = 10;
156
157 Note this formatting doesn't fits well with sub queries in list.
158
159 Option -w, --wrap-limit
160 This option wraps queries at a certain length whatever is the part of
161 the query at the limit unless it is a comment. For example if the limit
162 is reach in a text constant the text will be wrapped. Indentation is not
163 included in the character count. This option is applied in all cases
164 even if other options are used.
165
166 Option -C, --wrap-comment
167 This option wraps comments at the length defined by -w, --wrap-limit
168 whatever is the part of the comment. Indentation is not included in the
169 character count.
170
171 Option -t, --format-type
172 This option activate an alternative formatting that adds:
173
174 * newline in procedure/function parameter list
175 * new line in PUBLICATION and POLICY DDL
176 * keep enumeration in GROUP BY clause on a single line
177
178 Expect this list grow following alternative thoughts.
179
180 Option -g, --no-grouping
181 By default pgFormatter groups all statements when they are in a
182 transaction:
183
184 BEGIN;
185 INSERT INTO foo VALUES (1, 'text 1');
186 INSERT INTO foo VALUES (2, 'text 2');
187 ...
188 COMMIT;
189
190 By disabling grouping of statement pgFormatter will always add an extra
191 newline characters between statements just like outside a transaction:
192
193 BEGIN;
194
195 INSERT INTO foo VALUES (1, 'text 1');
196
197 INSERT INTO foo VALUES (2, 'text 2');
198 ...
199
200 COMMIT;
201
202 This might add readability to not DML transactions.
203
204 Option -L, --no-extra-line
205 By default pgFormatter always adds an empty line after the end of a
206 statement when it is terminated by a ; except in a plpgsql code block.
207 If the extra empty line at end of the output is useless, you can remove
208 it by adding this option to the command line.
209
210 Option --extra-function
211 pgFormatter applies some formatting to the PostgreSQL internal functions
212 call but it can not detect user defined function. It is possible to
213 defined a list of functions into a file (one function name per line) and
214 give it to pgFormatter through the --extra-function option that will be
215 formatter as PostgreSQL internal functions.
216
217HINTS
218 Configuration
219 If the default settings of pg_format doesn't fit all your needs you can
220 customize the behavior of pg_format by using a configuration file
221 instead of repeating the command line option. By default pgFormatter
222 look for file ~/.pg_format but you can choose an alternate configuration
223 file using command line option -c | --config
224
225 To customize the CGI pg_format.cgi look for a configuration file named
226 pg_format.conf in the same directory as the CGI script.
227
228 For a sample configuration file see doc/pg_format.conf.sample
229
230 To prevent pg_format to look at ~/.pg_format file you can use the
231 command line option -X | --no-rcfile
232
233 Formatting from stdin
234 You can execute pg_format without any argument or - to give the SQL code
235 to format through stdin.
236
237 If you use the interactive mode you have to type `ctrl+d` after typing
238 your SQL statement to format to end the typing.
239
240 $ pg_format
241 select * from customers;
242 < ctrl+d >
243
244 You can use stdin in a one liner as follow:
245
246 echo "select * from customers;" | pg_format
247
248 Formatting from VI
249 With pgFormatter, you can just add the following line to your ~/.vimrc
250 file:
251
252 au FileType sql setl formatprg=/usr/local/bin/pg_format\ -
253
254 This lets your gq commands use pgFormatter automagically. For example if
255 you are on the first line, typing:
256
257 ESC+gq+G
258
259 will format the entire file.
260
261 ESC+gq+2j
262
263 will format the next two line.
264
265 Thanks to David Fetter for the hint.
266
267 There is also the (Neo)vim plugin for formatting code for many file
268 types that support pg_format to format SQL file type. Thanks to Anders
269 Riutta for the patch to (Neo)vim.
270
271 Formatting from Atom
272 If you use atom as your favorite editor you can install the pg-formatter
273 package which is a Node.js wrapper of pgFormatter.
274
275 Features:
276
277 * Format selected text or a whole file via keyboard shortcut or command.
278 * Format SQL files on save.
279
280 Installation:
281
282 Search for pg-formatter in Atom UI or get it via command line:
283
284 apm install pg-formatter
285
286 Usage:
287
288 Hit Ctrl-Alt-F to format selected text (or a whole file) or define your
289 shortcut:
290
291 'ctrl-alt-p': 'pg-formatter:format'
292
293 Also, you can automatically format SQL files on save (disabled by
294 default).
295
296 You can download the package from url:
297
298 https://atom.io/packages/pg-formatter
299
300 the sources are available at https://github.com/gajus/pg-formatter
301
302 Thanks to Alex Fedoseev for the atom package.
303
304 Formatting from Visual Studio
305 Thanks to Brady Holt a Visual Studio Code extension is available to
306 formats PostgresSQL SQL using pgFormatter.
307
308 https://marketplace.visualstudio.com/items?itemName=bradymholt.pgformatter
309
310 For installation and use have a look at URL above.
311
312 Prevent replacing code snippets
313 Using -p or --placeholder command line option it is possible to keep
314 code untouched by pgFormatter in your SQL queries. For example, in query
315 like:
316
317 SELECT * FROM projects WHERE projectnumber
318 IN <<internalprojects>> AND username = <<loginname>>;
319
320 you may want pgFormatter to not interpret << and >> as bit-shift
321 keywords and modify your code snippets. You can use a Perl regular
322 expression to instruct pgFormatter to keep some part of the query
323 untouched. For example:
324
325 pg_format samples/ex9.sql -p '<<(?:.*)?>>'
326
327 will not format the bit-shift like operators.
328
329 If you would like to wrap queries after 60 characters (-w 60) and to
330 apply that limit to comments as well (-C), then urls in comments may get
331 wrapped. If you would prefer not to wrap urls, you can use a regular
332 expression to avoid wrapping urls. For example:
333
334 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()@:%_\+.~#?&//=]*)'
335
336 will wrap the queries and the comments, but not the urls.
337
338 Prevent dynamic code formatting
339 By default pgFormatter takes all code between single quote as string
340 constant and do not perform any formatting on this code. It is common to
341 use a string as code separator to avoid doubling single quote in dynamic
342 code generation, in this case pgFormatter can fail to auto detect the
343 code separator. By default it will search for any string after the
344 EXECUTE keyword starting with dollar sign. If it can not auto detect
345 your code separator you can use the command line option -S or
346 --separator to set the code separator that must be used.
347
348 Node.js thin-wrapper
349 Gajus Kuizinas has written a Node.js wrapper for executing pgFormatter.
350 You can find it at https://github.com/gajus/pg-formatter
351
352 Customize CSS for the CGI output
353 You can change the HTML style rendered through the default CSS style by
354 creating a file named custom_css_file.css into the pgFormatter CGI
355 script directory. The default CSS will be fully overridden by this
356 custom file content. You have to look at the generated HTML output to
357 get the default CSS code used.
358
359AUTHORS
360 pgFormatter is an original work from Gilles Darold with major code
361 refactoring by Hubert depesz Lubaczewski.
362
363COPYRIGHT
364 Copyright 2012-2020 Gilles Darold. All rights reserved.
365
366LICENSE
367 pgFormatter is free software distributed under the PostgreSQL Licence.
368
369 A modified version of the SQL::Beautify Perl Module is embedded in
370 pgFormatter with copyright (C) 2009 by Jonas Kramer and is published
371 under the terms of the Artistic License 2.0.
372
373
