Formatting Features

1. Formatting the code from a parse tree

Based on the following example code:

SELECT column_a|:f()|, column_b 
  FROM table_a
 WHERE column_b = 'test'
 ORDER BY 2 DESC,
          1;

and the resulting parse tree:

...> ParseTree.
[{{select,[{fields,[{jp,<<"column_a">>,
                        {':',{'fun',<<"f">>,[]},empty}},
                    <<"column_b">>]},
           {from,[<<"table_a">>]},
           {where,{'=',<<"column_b">>,<<"'test'">>}},
           {'hierarchical query',{}},
           {'group by',[]},
           {having,{}},
           {'order by',[{<<"2">>,<<"desc">>},{<<"1">>,<<>>}]}]},
  {extra,<<>>}}]

the formatted code can be compiled as follows:

...> io:format(sqlparse_fold:top_down(sqlparse_format_pretty, ParseTree, [])).
SELECT
    column_a|:f()|, column_b
FROM
    table_a
WHERE
    column_b = 'test'
ORDER BY
    2 DESC, 1

2. Formatting options

The following options can be used to control the result of the formatting process:

variable name	possible values	default value	description
case_identifier	init_cap, keep_unchanged, lower, upper	keep_unchanged	init_cap: if the first character of a word is a letter, it is capitalized (words are delimited by underscore characters)
			keep_unchanged: capitalization remains unchanged
			lower: convert all letters to lower case letters
			upper: convert all letters to upper case letters
case_keyword	init_cap, lower, upper	upper	init_cap: if the first character of a word is a letter, it is capitalized (words are delimited by spaces and underscore characters)
			lower: convert all letters to lower case letters
			upper: convert all letters to upper case letters
indent_space	integer value between 1 and 8	4	number of spaces used per indent step
indent_with	space, tab	space	space: indent with spaces
			tab: indent with tabulator characters
line_break_after	integer value greater or equal to zero	80	0: no line breaks
			>0: Line breakup immediately after the specified column provided there is a suitable place before (see sections 3 and 4)
ws_operators	true, false	true	true: operators are surrounded by spaces
			false: operators are not surrounded by spaces

The sqlparse_layout:pretty/2 accepts either a list or a map with formatting options that differ from the default values.

3. Unbreakable language elements

Line breaks are not possible within the following language elements:

• inside case-sensitive identifiers and database links (double quotation marks),
• inside optimizer hints (/* ... */,
• inside JSON expressions (vertical bars),
• inside strings (single quotation marks).

4. Possible break points

Possible places for line breaks are before and after:

• a comma,
• an expression with double quotation marks,
• an opening or closing round bracket,
• a JSON expression,
• a set of blanks,
• a string.

5. Formatting Strategies

The structure of an SQL statement is expressed by the isolated representation of the structure-determining keywords:

SELECT
    Column_1, Column_2, Column_3
FROM
    Table_1
WHERE
    Column_1 = Column_2
GROUP BY
    Column_3, Column_4
HAVING
    Column_3 = Column_4
ORDER BY
    Column_3, Column_2

Each entry in a FROM clause appears on a separate line:

SELECT
    Column_1, Column_2, Column_3
FROM
    Table_1,
    :param_1 Alias_1,
    Schema_1.Table_3"@dblink_1"

A line break is generally made when a subquery occurs:

SELECT
    Column_1, Column_2,
    (SELECT
        COUNT(Colum_3)
    FROM
        Table_2) Column_3, Column_4, Column_5
FROM
    Table_1,
    Table_3
WHERE
    Column_1 = Column_2
    AND (SELECT
        COUNT(Colum_3)
    FROM
        Table_2) > 100
HAVING
    Column_1 = Column_2
    AND (SELECT
        COUNT(Colum_3)
    FROM
        Table_2) > 100
ORDER BY
    Column_1, (SELECT
        COUNT(Colum_3)
    FROM
        Table_2)

A line break is generally made when a 'case when then' expression occurs:

SELECT
    Column_1, Column_2,
    CASE
        WHEN Column_3 > Column_4
        THEN Column_3
        ELSE Column_4
    END Column_3, Column_5, Column_6
FROM
    Table_1,
    Table_3

The structure of a JOIN statement is expressed by the isolated representation of the join-determining expressions and keywords:

SELECT
    *
FROM
    Table_1
    LEFT OUTER JOIN
    Table_2
    USING (Column_1, Column_2, Column_3, Column_4),
    Table_3
    NATURAL JOIN
    Table_4,
    Schema_1.Table_1"@dblink_1"
    FULL OUTER JOIN
    Schema_2.Table_2"@dblink_2"
    PARTITION BY (Column_1)
    ON Column_1 = Column_2

The structure of the set operations is expressed by the isolated representation of the set keywords and the indentation of the set elements:

SELECT
    *
FROM
            ((SELECT
            *
        FROM
            Table_1)
    UNION
            ((SELECT
                *
            FROM
                Table_21)
        MINUS
            (SELECT
                *
            FROM
                Table_22)))