pgForma...Beautify(3)

📦
version 5.11 · 2 versions
, in nixpkgs unstable

💖 NAME 💖

pgFormatter::Beautify — Library for pretty-printing SQL queries User Contributed Perl Documentation · manual page pgFormatter::Beautify(3)

okay so bestie, this is the library for making SQL look cute and readable. It pretty-prints queries. That’s the whole main-character job. ✨

✨ VERSION ✨

Version 5.11

🌀 SYNOPSIS 🌀

This module can be used to reformat a given SQL query, and it can optionally anonymize parameters. 🫶

The output can be either plain text, or it can be HTML with appropriate styles so that it can be displayed on a web page.

Example usage:

my $beautifier = pgFormatter::Beautify->new();
$beautifier->query( 'select a,b,c from d where e = f' );

$beautifier->beautify();
my $nice_txt = $beautifier->content();

$beautifier->format('html');
$beautifier->beautify();
my $nice_html = $beautifier->content();

$beautifier->format('html');
$beautifier->anonymize();
$beautifier->beautify();
my $nice_anonymized_html = $beautifier->content();

$beautifier->format();
$beautifier->beautify();
$beautifier->wrap_lines()
my $wrapped_txt = $beautifier->content();

🌸 FUNCTIONS 🌸

Here’s the whole function roster, bestie. Every single one is invited to the party. 💅

🫶 new

Generic constructor — it creates the object, sets defaults, and reads config from the given hash with options.

Takes options as hash. Following options are recognized:

  • break — the string used for linebreaks. Default is "\n".
  • colorize — if set to false, CSS style will not be applied to html output. It’s used internally to display errors in CGI mode withour style. 😭
  • comma — set comma at beginning or end of a line in a parameter list.
    • end — put comma at end of the list (default)
    • start — put comma at beginning of the list
  • comma_break — add new-line after each comma in INSERT statements.
  • format — set beautify format to apply to the content (default: text).
    • text — output content as plain/text (command line mode default)
    • html — output text/html with CSS style applied to content (CGI mode default)
  • functions — list (arrayref) of strings that are function names.
  • keywords — list (arrayref) of strings that are keywords.
  • multiline — use multi-line search for placeholder regex, see placeholder.
  • no_comments — if set to true, comments will be removed from query.
  • no_grouping — if set to true, statements will not be grouped in a transaction, and an extra newline character will be added between statements like outside a transaction.
  • placeholder — use the specified regex to find code that must not be changed in the query.
  • query — query to beautify.
  • rules — hash of rules — uses rule semantics from SQL::Beautify.
  • space — character(s) to be used as space for indentation.
  • spaces — how many spaces to use for indentation.
  • uc_functions — what to do with function names:
    • 0 — do not change
    • 1 — change to lower case
    • 2 — change to upper case
    • 3 — change to Capitalized
  • separator — string used as dynamic code separator, default is single quote.
  • uc_keywords — what to do with keywords — meaning of value like with uc_functions.
  • uc_types — what to do with data types — meaning of value like with uc_functions.
  • wrap — wraps given keywords in pre- and post- markup. Specific docs in SQL::Beautify.
  • format_type — try an other formatting.
  • wrap_limit — wrap queries at a certain length.
  • wrap_after — number of column after which lists must be wrapped.
  • wrap_comment — apply wrapping to comments starting with --.
  • numbering — statement numbering as a comment before each query.
  • redshift — add Redshift keywords (obsolete, use --extra-keyword).
  • no_extra_line — do not add an extra empty line at end of the output.
  • keep_newline — preserve empty line in plpgsql code.
  • no_space_function — remove space before function call and open parenthesis.
  • redundant_parenthesis — do not eliminate redundant parenthesis in DML queries.
  • vertical_align — vertically align CREATE TABLE column definitions.

For defaults, please check function set_defaults.

🫶 query

Accessor to query string. Both reads:

$object->query()

, and writes:

$object->query( $something )

🫶 content

Accessor to content of results. Must be called after $object->beautify().

This can be either plain text or html following the format asked by the client with the $object->format() method.

🫶 highlight_code

Makes result html with styles set for highlighting.

🫶 tokenize_sql

Splits input SQL into tokens.

Code lifted from SQL::Beautify

🫶 _parse_create_table_column

Parse one single-line CREATE TABLE column definition into the parts needed by vertical alignment. A trailing comment is stored separately from the SQL. Returns undef for table constraints, embedded comments, multiline input, and definitions that do not contain both a column name and a data type.

This method only analyzes input. It does not change the formatted SQL.

🫶 _render_sql_tokens

Render a list of SQL tokens as one compact, single-line SQL fragment.

This helper is intentionally limited to the fragments produced by _parse_create_table_column(). It preserves token order and only decides where spaces belong. It does not perform alignment or change keyword casing.

🫶 _pad_right

Pad a string with spaces until it reaches the requested width.

The helper returns the original string unchanged when it is already at least as wide as the requested width. Alignment uses spaces deliberately: tabs would make the result depend on the tab width configured by the editor displaying the SQL.

🫶 _align_create_table_column_keyword

Align a top-level keyword within the remainder of parsed CREATE TABLE columns.

For every matching row, the tokens before the keyword are treated as a prefix. Shorter prefixes are padded so the keyword starts in the same output column. Keywords nested inside parentheses or array brackets are ignored.

The method updates the rendered remainder stored in each matching row. It does not reorder tokens or affect rows that do not contain the requested keyword.

🫶 _parenthesis_delta

Return the net parenthesis depth change for one SQL line.

The existing SQL tokenizer is reused so parentheses inside quoted strings and comments are not mistaken for structural parentheses.

🫶 _create_table_start

Inspect one formatted line for the beginning of a regular CREATE TABLE body.

The return value is a three-element list: whether the line begins a supported CREATE TABLE statement, whether its opening parenthesis is present, and the net parenthesis depth on that line. When the opening parenthesis is absent it may occur on the following line.

CREATE TABLE AS, PARTITION OF, and typed-table forms are deliberately skipped because their parentheses do not necessarily contain ordinary column definitions.

🫶 _align_create_table_body

Align eligible top-level lines from one already formatted CREATE TABLE body.

Only lines that begin and end at the table body's direct parenthesis depth are sent to the column-group aligner. This excludes continuation lines belonging to multiline CHECK expressions or other nested constructs while still allowing single-line type modifiers and function calls.

🫶 _align_create_table_columns

Apply vertical column alignment to regular CREATE TABLE statements in a fully formatted SQL document.

The method is a final text-only rendering pass. It preserves all lines outside supported table bodies and delegates actual row formatting to the smaller parser, renderer, and group-alignment helpers.

🫶 _align_create_table_column_comments

Append trailing comments at a shared output column.

The SQL portion of every supported column is measured after name, declaration, and DEFAULT alignment. Only rows containing comments receive padding, so rows without comments do not gain trailing whitespace. Unsupported lines are ignored when calculating the comment column.

🫶 _align_create_table_column_group

Align a group of single-line CREATE TABLE column definitions.

The method aligns the column names and the beginning of each remainder, while preserving indentation, token order, trailing commas, and unsupported lines. It only returns rendered lines; it does not modify the formatter output.

🫶 beautify

Beautify SQL.

After calling this function, $object->content() will contain nicely indented result.

Code lifted from SQL::Beautify

🫶 _add_token

Add a token to the beautified string.

Code lifted from SQL::Beautify

🫶 _over

Increase the indentation level.

Code lifted from SQL::Beautify

🫶 _back

Decrease the indentation level.

Code lifted from SQL::Beautify

🫶 _indent

Return a string of spaces according to the current indentation level and the spaces setting for indenting.

Code lifted from SQL::Beautify

🫶 _new_line

Add a line break, but make sure there are no empty lines.

Code lifted from SQL::Beautify

🫶 _next_token

Have a look at the token that's coming up next.

Code lifted from SQL::Beautify

🫶 _next_token_skip_comment

Have a look at the token that's coming up next omitting comments.

Code lifted from SQL::Beautify

🫶 _token

Get the next token, removing it from the list of remaining tokens.

Code lifted from SQL::Beautify

🫶 _is_keyword

Check if a token is a known SQL keyword.

Code lifted from SQL::Beautify

🫶 _is_type

Check if a token is a known SQL type.

🫶 _is_comment

Check if a token is a SQL or C style comment.

🫶 _is_function

Check if a token is a known SQL function.

Code lifted from SQL::Beautify and rewritten to check one long regexp instead of a lot of small ones.

🫶 add_keywords

Add new keywords to highlight.

Code lifted from SQL::Beautify

🫶 _re_from_list

Create compiled regexp from prefix, suffix and and a list of values to match.

🫶 _refresh_functions_re

Refresh compiled regexp for functions.

🫶 add_functions

Add new functions to highlight.

Code lifted from SQL::Beautify

🫶 add_rule

Add new rules.

Code lifted from SQL::Beautify

🫶 _get_rule

Find custom rule for a token.

Code lifted from SQL::Beautify

🫶 _process_rule

Applies defined rule.

Code lifted from SQL::Beautify

🫶 _is_constant

Check if a token is a constant.

Code lifted from SQL::Beautify

🫶 _is_punctuation

Check if a token is punctuation.

🫶 _generate_anonymized_string

Simply generate a random string, thanks to Perlmonks.

Returns original in certain cases which don't require anonymization, like timestamps, or intervals.

🫶 anonymize

Anonymize litteral in SQL queries by replacing parameters with fake values.

🫶 set_defaults

Sets defaults for newly created objects.

Currently defined defaults:

So here's the rest of the default-value lineup, still part of the FUNCTIONS section — every single one, no skips, because we don't do that here 💅

  • spaces => 4 — four spaces, that's the vibe
  • space => ' ' — one literal space character
  • break => "\n" — a newline
  • uc_keywords => 2
  • uc_functions => 0
  • uc_types => 1
  • no_comments => 0
  • no_grouping => 0
  • placeholder => '' — empty string, yep
  • multiline => 0
  • separator => '' — also empty
  • comma => 'end' — the string end
  • format => 'text'
  • colorize => 1
  • format_type => 0
  • wrap_limit => 0
  • wrap_after => 0
  • wrap_comment => 0
  • no_extra_line => 0
  • keep_newline => 0
  • no_space_function => 0
  • redundant_parenthesis => 0
  • vertical_align => 0

That's the whole roster 💖 twenty-three lil defaults, all accounted for.

🌈 format

Sets the output format, bestie. Possible values are 'text' and 'html'.

Default is text output. And if the format is wrong, it returns 0 and just uses the default — so no chaos, it self-corrects 🫶

🎀 set_dicts

Sets all the various dictionaries — you know, the lists of keywords, functions, symbols, and the like.

It got moved into its own separate function so it can live at the very end of the module, which makes the rest of the code way easier to read. Main character organization, honestly ✨

🦋 _remove_dynamic_code

Internal function, used to hide dynamic code in plpgsql from the parser.

The original values get restored with _restore_dynamic_code() — it's giving "hide and seek but make it comp-sci" 🙈

🧚 _restore_dynamic_code

Internal function used to restore plpgsql dynamic code that was removed by _remove_dynamic_code().

The comeback arc we all deserved 👑

💜 _quote_operator

Internal function used to quote an operator with multiple characters so it gets tokenized as a single word.

The original values get restored with _restore_operator() — nothing stays quoted forever, periodt.

🌸 _restore_operator

Internal function used to restore the operator that was removed by _quote_operator().

Put it back, put it back 🫶

🍓 _quote_comment_stmt

Internal function used to replace a constant in a COMMENT statement so it gets tokenized as a single word.

The original values get restored with _restore_comment_stmt() — again, temporary era only.

⭐ _restore_comment_stmt

Internal function used to restore the comment string that was removed by _quote_comment_stmt().

The comment said "I'll be back" and it meant it 😭

🔥 _remove_comments

Internal function used to remove comments in SQL code, and the reason is so cute — it just simplifies the work of wrap_lines.

Heads up though: those comments must be restored with _restore_comments() — don't leave them deleted, bestie 💀

🌈 _restore_comments

Internal function used to restore comments in SQL code that was removed by _remove_comments().

Full circle moment, we love to see it ✨

👑 wrap_lines

Internal function used to wrap a line at a certain length.

Short, sweet, iconic, no notes 🎀

💖 AUTHOR

pgFormatter is an original work from Gilles Darold — founder energy, we stan 🌟

🐛 BUGS

Please report any bugs or feature requests to: https://github.com/darold/pgFormatter/issues

(Yes, actually tell them — they want to know, fr fr 🙏)

Copyright 2012-2026 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.

Okay that's the whole page, bestie — and shoutout to the footer for the lore: this one's from perl v5.42.3, dated 1980-01-01, and the page itself is pgForma...Beautify(3). Slay 💅✨
📦 where this page comes from