.\" -*- mode: troff; coding: utf-8 -*-
.\" Automatically generated by Pod::Man v6.0.2 (Pod::Simple 3.45)
.\"
.\" 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
..
.\" \*(C` and \*(C' are quotes in nroff, nothing in troff, for use with C<>.
.ie n \{\
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    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
.\"
.\" Required to disable full justification in groff 1.23.0.
.if n .ds AD l
.\" ========================================================================
.\"
.IX Title "pgFormatter::Beautify 3"
.TH pgFormatter::Beautify 3 2025-09-15 "perl v5.42.0" "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
pgFormatter::Beautify \- Library for pretty\-printing SQL queries
.SH VERSION
.IX Header "VERSION"
Version 5.8
.SH SYNOPSIS
.IX Header "SYNOPSIS"
This module can be used to reformat given SQL query, optionally anonymizing parameters.
.PP
Output can be either plain text, or it can be HTML with appropriate styles so that it can be displayed on a web page.
.PP
Example usage:
.PP
.Vb 2
\&    my $beautifier = pgFormatter::Beautify\->new();
\&    $beautifier\->query( \*(Aqselect a,b,c from d where e = f\*(Aq );
\&
\&    $beautifier\->beautify();
\&    my $nice_txt = $beautifier\->content();
\&
\&    $beautifier\->format(\*(Aqhtml\*(Aq);
\&    $beautifier\->beautify();
\&    my $nice_html = $beautifier\->content();
\&
\&    $beautifier\->format(\*(Aqhtml\*(Aq);
\&    $beautifier\->anonymize();
\&    $beautifier\->beautify();
\&    my $nice_anonymized_html = $beautifier\->content();
\&
\&    $beautifier\->format();
\&    $beautifier\->beautify();
\&    $beautifier\->wrap_lines()
\&    my $wrapped_txt = $beautifier\->content();
.Ve
.SH FUNCTIONS
.IX Header "FUNCTIONS"
.SS new
.IX Subsection "new"
Generic constructor \- creates object, sets defaults, and reads config from given hash with options.
.PP
Takes options as hash. Following options are recognized:
.IP \(bu 4
break \- String that is used for linebreaks. Default is "\en".
.IP \(bu 4
colorize \- if set to false CSS style will not be applied to html output. Used internally to display errors in CGI mode withour style.
.IP \(bu 4
comma \- set comma at beginning or end of a line in a parameter list
.RS 4
.IP "end \- put comma at end of the list (default)" 4
.IX Item "end - put comma at end of the list (default)"
.PD 0
.IP "start \- put comma at beginning of the list" 4
.IX Item "start - put comma at beginning of the list"
.PD
.RE
.RS 4
.RE
.IP \(bu 4
comma_break \- add new\-line after each comma in INSERT statements
.IP \(bu 4
format \- set beautify format to apply to the content (default: text)
.RS 4
.IP "text \- output content as plain/text (command line mode default)" 4
.IX Item "text - output content as plain/text (command line mode default)"
.PD 0
.IP "html \- output text/html with CSS style applied to content (CGI mode default)" 4
.IX Item "html - output text/html with CSS style applied to content (CGI mode default)"
.PD
.RE
.RS 4
.RE
.IP \(bu 4
functions \- list (arrayref) of strings that are function names
.IP \(bu 4
keywords \- list (arrayref) of strings that are keywords
.IP \(bu 4
multiline \- use multi\-line search for placeholder regex, see placeholder.
.IP \(bu 4
no_comments \- if set to true comments will be removed from query
.IP \(bu 4
no_grouping \- if set to true statements will not be grouped in a transaction, an extra newline character will be added between statements like outside a transaction.
.IP \(bu 4
placeholder \- use the specified regex to find code that must not be changed in the query.
.IP \(bu 4
query \- query to beautify
.IP \(bu 4
rules \- hash of rules \- uses rule semantics from SQL::Beautify
.IP \(bu 4
space \- character(s) to be used as space for indentation
.IP \(bu 4
spaces \- how many spaces to use for indentation
.IP \(bu 4
uc_functions \- what to do with function names:
.RS 4
.IP "0 \- do not change" 4
.IX Item "0 - do not change"
.PD 0
.IP "1 \- change to lower case" 4
.IX Item "1 - change to lower case"
.IP "2 \- change to upper case" 4
.IX Item "2 - change to upper case"
.IP "3 \- change to Capitalized" 4
.IX Item "3 - change to Capitalized"
.PD
.RE
.RS 4
.RE
.IP \(bu 4
separator \- string used as dynamic code separator, default is single quote
.IP \(bu 4
uc_keywords \- what to do with keywords \- meaning of value like with uc_functions
.IP \(bu 4
uc_types \- what to do with data types \- meaning of value like with uc_functions
.IP \(bu 4
wrap \- wraps given keywords in pre\- and post\- markup. Specific docs in SQL::Beautify
.IP \(bu 4
format_type \- try an other formatting
.IP \(bu 4
wrap_limit \- wrap queries at a certain length
.IP \(bu 4
wrap_after \- number of column after which lists must be wrapped
.IP \(bu 4
wrap_comment \- apply wrapping to comments starting with \-\-
.IP \(bu 4
numbering \- statement numbering as a comment before each query
.IP \(bu 4
redshift \- add Redshift keywords (obsolete, use \-\-extra\-keyword)
.IP \(bu 4
no_extra_line \- do not add an extra empty line at end of the output
.IP \(bu 4
keep_newline \- preserve empty line in plpgsql code
.IP \(bu 4
no_space_function \- remove space before function call and open parenthesis
.IP \(bu 4
redundant_parenthesis \- do not eliminate redundant parenthesis in DML queries
.PP
For defaults, please check function set_defaults.
.SS query
.IX Subsection "query"
Accessor to query string. Both reads:
.PP
.Vb 1
\&    $object\->query()
.Ve
.PP
, and writes
.PP
.Vb 1
\&    $object\->query( $something )
.Ve
.SS content
.IX Subsection "content"
Accessor to content of results. Must be called after \f(CW$object\fR\->\fBbeautify()\fR.
.PP
This can be either plain text or html following the format asked by the
client with the \f(CW$object\fR\->\fBformat()\fR method.
.SS highlight_code
.IX Subsection "highlight_code"
Makes result html with styles set for highlighting.
.SS tokenize_sql
.IX Subsection "tokenize_sql"
Splits input SQL into tokens
.PP
Code lifted from SQL::Beautify
.SS beautify
.IX Subsection "beautify"
Beautify SQL.
.PP
After calling this function, \f(CW$object\fR\->\fBcontent()\fR will contain nicely indented result.
.PP
Code lifted from SQL::Beautify
.SS _add_token
.IX Subsection "_add_token"
Add a token to the beautified string.
.PP
Code lifted from SQL::Beautify
.SS _over
.IX Subsection "_over"
Increase the indentation level.
.PP
Code lifted from SQL::Beautify
.SS _back
.IX Subsection "_back"
Decrease the indentation level.
.PP
Code lifted from SQL::Beautify
.SS _indent
.IX Subsection "_indent"
Return a string of spaces according to the current indentation level and the
spaces setting for indenting.
.PP
Code lifted from SQL::Beautify
.SS _new_line
.IX Subsection "_new_line"
Add a line break, but make sure there are no empty lines.
.PP
Code lifted from SQL::Beautify
.SS _next_token
.IX Subsection "_next_token"
Have a look at the token that\*(Aqs coming up next.
.PP
Code lifted from SQL::Beautify
.SS _next_token_skip_comment
.IX Subsection "_next_token_skip_comment"
Have a look at the token that\*(Aqs coming up next omitting comments.
.PP
Code lifted from SQL::Beautify
.SS _token
.IX Subsection "_token"
Get the next token, removing it from the list of remaining tokens.
.PP
Code lifted from SQL::Beautify
.SS _is_keyword
.IX Subsection "_is_keyword"
Check if a token is a known SQL keyword.
.PP
Code lifted from SQL::Beautify
.SS _is_type
.IX Subsection "_is_type"
Check if a token is a known SQL type
.SS _is_comment
.IX Subsection "_is_comment"
Check if a token is a SQL or C style comment
.SS _is_function
.IX Subsection "_is_function"
Check if a token is a known SQL function.
.PP
Code lifted from SQL::Beautify and rewritten to check one long regexp instead of a lot of small ones.
.SS add_keywords
.IX Subsection "add_keywords"
Add new keywords to highlight.
.PP
Code lifted from SQL::Beautify
.SS _re_from_list
.IX Subsection "_re_from_list"
Create compiled regexp from prefix, suffix and and a list of values to match.
.SS _refresh_functions_re
.IX Subsection "_refresh_functions_re"
Refresh compiled regexp for functions.
.SS add_functions
.IX Subsection "add_functions"
Add new functions to highlight.
.PP
Code lifted from SQL::Beautify
.SS add_rule
.IX Subsection "add_rule"
Add new rules.
.PP
Code lifted from SQL::Beautify
.SS _get_rule
.IX Subsection "_get_rule"
Find custom rule for a token.
.PP
Code lifted from SQL::Beautify
.SS _process_rule
.IX Subsection "_process_rule"
Applies defined rule.
.PP
Code lifted from SQL::Beautify
.SS _is_constant
.IX Subsection "_is_constant"
Check if a token is a constant.
.PP
Code lifted from SQL::Beautify
.SS _is_punctuation
.IX Subsection "_is_punctuation"
Check if a token is punctuation.
.PP
Code lifted from SQL::Beautify
.SS _generate_anonymized_string
.IX Subsection "_generate_anonymized_string"
Simply generate a random string, thanks to Perlmonks.
.PP
Returns original in certain cases which don\*(Aqt require anonymization, like
timestamps, or intervals.
.SS anonymize
.IX Subsection "anonymize"
Anonymize litteral in SQL queries by replacing parameters with fake values
.SS set_defaults
.IX Subsection "set_defaults"
Sets defaults for newly created objects.
.PP
Currently defined defaults:
.IP "spaces => 4" 4
.IX Item "spaces => 4"
.PD 0
.IP "space => \*(Aq \*(Aq" 4
.IX Item "space => ' '"
.IP "break => ""\en""" 4
.IX Item "break => ""n"""
.IP "uc_keywords => 2" 4
.IX Item "uc_keywords => 2"
.IP "uc_functions => 0" 4
.IX Item "uc_functions => 0"
.IP "uc_types => 1" 4
.IX Item "uc_types => 1"
.IP "no_comments => 0" 4
.IX Item "no_comments => 0"
.IP "no_grouping => 0" 4
.IX Item "no_grouping => 0"
.IP "placeholder => \*(Aq\*(Aq" 4
.IX Item "placeholder => ''"
.IP "multiline => 0" 4
.IX Item "multiline => 0"
.IP "separator => \*(Aq\*(Aq" 4
.IX Item "separator => ''"
.IP "comma => \*(Aqend\*(Aq" 4
.IX Item "comma => 'end'"
.IP "format => \*(Aqtext\*(Aq" 4
.IX Item "format => 'text'"
.IP "colorize => 1" 4
.IX Item "colorize => 1"
.IP "format_type => 0" 4
.IX Item "format_type => 0"
.IP "wrap_limit => 0" 4
.IX Item "wrap_limit => 0"
.IP "wrap_after => 0" 4
.IX Item "wrap_after => 0"
.IP "wrap_comment => 0" 4
.IX Item "wrap_comment => 0"
.IP "no_extra_line => 0" 4
.IX Item "no_extra_line => 0"
.IP "keep_newline => 0" 4
.IX Item "keep_newline => 0"
.IP "no_space_function => 0" 4
.IX Item "no_space_function => 0"
.IP "redundant_parenthesis => 0" 4
.IX Item "redundant_parenthesis => 0"
.PD
.SS format
.IX Subsection "format"
Set output format \- possible values: \*(Aqtext\*(Aq and \*(Aqhtml\*(Aq
.PP
Default is text output. Returns 0 in case or wrong format and use default.
.SS set_dicts
.IX Subsection "set_dicts"
Sets various dictionaries (lists of keywords, functions, symbols, and the like)
.PP
This was moved to separate function, so it can be put at the very end of module
so it will be easier to read the rest of the code.
.SS _remove_dynamic_code
.IX Subsection "_remove_dynamic_code"
Internal function used to hide dynamic code in plpgsql to the parser.
The original values are restored with function \fB_restore_dynamic_code()\fR.
.SS _restore_dynamic_code
.IX Subsection "_restore_dynamic_code"
Internal function used to restore plpgsql dynamic code in plpgsql
that was removed by the \fB_remove_dynamic_code()\fR method.
.SS _quote_operator
.IX Subsection "_quote_operator"
Internal function used to quote operator with multiple character
to be tokenized as a single word.
The original values are restored with function \fB_restore_operator()\fR.
.SS _restore_operator
.IX Subsection "_restore_operator"
Internal function used to restore operator that was removed
by the \fB_quote_operator()\fR method.
.SS _quote_comment_stmt
.IX Subsection "_quote_comment_stmt"
Internal function used to replace constant in a COMMENT statement
to be tokenized as a single word.
The original values are restored with function \fB_restore_comment_stmt()\fR.
.SS _restore_comment_stmt
.IX Subsection "_restore_comment_stmt"
Internal function used to restore comment string that was removed
by the \fB_quote_comment_stmt()\fR method.
.SS _remove_comments
.IX Subsection "_remove_comments"
Internal function used to remove comments in SQL code
to simplify the work of the wrap_lines. Comments must be
restored with the \fB_restore_comments()\fR method.
.SS _restore_comments
.IX Subsection "_restore_comments"
Internal function used to restore comments in SQL code
that was removed by the \fB_remove_comments()\fR method.
.SS wrap_lines
.IX Subsection "wrap_lines"
Internal function used to wrap line at a certain length.
.SH AUTHOR
.IX Header "AUTHOR"
pgFormatter is an original work from Gilles Darold
.SH BUGS
.IX Header "BUGS"
Please report any bugs or feature requests to: https://github.com/darold/pgFormatter/issues
.SH COPYRIGHT
.IX Header "COPYRIGHT"
Copyright 2012\-2025 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.