Combining CREATE TABLE with CTE affects later formatting

[nassibnassar]

Thank you for this very nice tool. With version 4.4, I encountered an example where using CREATE TABLE together with a CTE appears to throw off later formatting.

Input file:

CREATE TABLE local.loans_renewal_count AS
WITH loan_count AS (
    SELECT
        item_id,
        count(DISTINCT id) AS num_loans,
        sum(renewal_count) AS num_renewals
    FROM
        circulation_loans
    GROUP BY
        item_id
)
SELECT
    CURRENT_DATE AS current_as_of_date,
    it.id AS item_id,
    COALESCE(lc.num_loans, 0) AS num_loans,
    COALESCE(lc.num_renewals, 0) AS num_renewals
FROM
    inventory_items AS it
    LEFT JOIN loan_count AS lc ON it.id = lc.item_id;

Output file (note the indentation—and case—of the COALESCE statements):

CREATE TABLE local.loans_renewal_count AS
WITH loan_count AS (
    SELECT
        item_id,
        count(DISTINCT id) AS num_loans,
        sum(renewal_count) AS num_renewals
    FROM
        circulation_loans
    GROUP BY
        item_id
)
SELECT
    CURRENT_DATE AS current_as_of_date,
    it.id AS item_id,
    COALESCE (
        lc.num_loans,
        0
) AS num_loans,
    COALESCE (
        lc.num_renewals,
        0
) AS num_renewals
FROM
    inventory_items AS it
    LEFT JOIN loan_count AS lc ON it.id = lc.item_id;

If the CREATE TABLE line is removed, it does not happen. Output:

WITH loan_count AS (
    SELECT
        item_id,
        count(DISTINCT id) AS num_loans,
        sum(renewal_count) AS num_renewals
    FROM
        circulation_loans
    GROUP BY
        item_id
)
SELECT
    CURRENT_DATE AS current_as_of_date,
    it.id AS item_id,
    coalesce(lc.num_loans, 0) AS num_loans,
    coalesce(lc.num_renewals, 0) AS num_renewals
FROM
    inventory_items AS it
    LEFT JOIN loan_count AS lc ON it.id = lc.item_id;

If the CTE is removed, it does not happen. Output:

CREATE TABLE local.loans_renewal_count AS
SELECT
    CURRENT_DATE AS current_as_of_date,
    it.id AS item_id,
    coalesce(lc.num_loans, 0) AS num_loans,
    coalesce(lc.num_renewals, 0) AS num_renewals
FROM
    inventory_items AS it
    LEFT JOIN loan_count AS lc ON it.id = lc.item_id;

The formatting configuration is:

# Obscure all literals in queries, use to hide confidential data before formatting.
anonymize=0

# In a parameters list, end or start with the comma. Default: end
comma=end

# In insert statement, add a newline after each comma.
comma-break=0

# Output format: text or html. Default: text.
format=text

# Add a newline between statements in transaction regroupement. Default is to group statements.
nogrouping=1

# Change the case of the reserved keyword. Default is uppercase: 2.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
keyword-case=2

# Change the case of the data type name. Default is lowercase: 1.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
type-case=1

# Change the case of the reserved keyword. Default is unchanged: 0.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
function-case=1

# Do not add an extra empty line at end of the output.
no-extra-line=0

# Maximum length of a query, it will be cutted above the given size. Default: no truncate.
maxlength=0

# Remove any comment from SQL code.
nocomment=0

# Statement numbering as a comment before each query.
numbering=0

# Define the filename for the output. Default: stdout.
output=

# Set regex to find code that must not be changed.
placeholder=

# Add RedShift keyworks to the list of SQL keyworks.
redshift=1

# Dynamic code separator, default to single quote.
separator=

# Change space indent, default 4 spaces.
spaces=4

# Try another formatting type for some statements.
format-type=0

# Use tabs instead of space characters, when used spaces is set to 1 whatever is its value
tabs=0

# Wrap queries at a certain length.
wrap-limit=0

# Number of column after which lists must be wrapped.
wrap-after=0

# with --wrap-limit, apply reformatting to comments.
wrap-comment=0

# Add a list of function to be formatted as PG internal functions
#extra-function=/opt/pgFormatter/functions.lst

[darold]

Thanks for the report, there is lot of formatting problems with CTE, see #202, #192 and #195. I need to rethink and rewrite lot of code to fix CTE. This works still not have been scheduled yet and I don't know when I could work on it for the moment.