Formatting of CTEs

[sambrilleman]

Is your feature request related to a problem? Please describe

Thanks for this package! Is it possible to have the start of a CTE definition on a new line?

The current behaviour is that a CTE is a continuation of the same line, as in the following example:

print(
    sql_formatter.core.format_sql("""
    WITH

    first_cte AS (
        SELECT var 
        FROM table1
    ),

    second_cte AS (
        SELECT var 
        FROM table2
    )

    SELECT a.var
    FROM first_cte
    JOIN second_cte USING (var)
    """)
)

which leads to the following output:

with first_cte as (SELECT var
                   FROM   table1), second_cte as (SELECT var
                               FROM   table2)
SELECT a.var
FROM   first_cte join second_cte using (var)

Describe the solution you'd like

The desired output would be:

WITH

first_cte as (
    SELECT var
    FROM   table1
), 

second_cte as (
    SELECT var
    FROM   table2
)

SELECT a.var
FROM   first_cte join second_cte using (var)

That is:

• the WITH would be capitalised on its own line
• the name of the CTE would be on a new line (preferably with an empty line above)
• the SELECT statement inside the definition of the CTE would start on a new line, idented to represent a block nested within the parentheses ()

Describe alternatives you've considered

[ztane]

• I had a CTE inside CROSS JOIN LATERAL and right now it gets formatted as

    ...
    CROSS JOIN LATERAL ( WITH bar AS (
            SELECT 
                a,
                b,
            FROM foo_func(baz)),
            barf AS (
                SELECT ...
)
           SELECT ...
    ...

i.e. not only is the WITH hanging there on the right, but also the closing parenthesis of the 2nd CTE placed in column 1, even though contained within other parentheses and thoroughly indented otherwise...

[zachmayer]

I would also like to see this improved! The current version of the package makes my WITH statements unreadable

[ryanhaarmann]

I'd also like to see WITH formatting added