[FORMATTING] Inconsistent formatting for queries with $$

[karlhorky]

As I've learned in Sec-ant/prettier-plugin-embed#45 (comment), there is actually a small bug in sql-formatter with queries including $$:

The lines are not consistently indented - the leading space is stripped from the first line, but the others are untouched, by design.

Input data

Which SQL and options did you provide as input?

Note the leading spaces on the first line:

            create function dup (int) returns dup_result as $$ 
              select
                  $1,
                  cast($1 as text)||' is text'
            $$ language sql;

Expected Output

create function dup (int) returns dup_result as $$ 
  select
      $1,
      cast($1 as text)||' is text'
$$ language sql;

Alternative (no indentation formatting, including first line):

            create function dup (int) returns dup_result as $$ 
              select
                  $1,
                  cast($1 as text)||' is text'
            $$ language sql;

Actual Output

Leading spaces on the first line are removed, but indentation on the following lines are untouched, by design

create function dup (int) returns dup_result as $$ 
              select
                  $1,
                  cast($1 as text)||' is text'
            $$ language sql;

Usage

• How are you calling / using the library? For Prettier formatting, via prettier-plugin-sql and prettier-plugin-embed
• What SQL language(s) does this apply to? PostgreSQL
• Which SQL Formatter version are you using? 14.1.0-beta.2

This appears to be causing the unstable formatting with prettier-plugin-embed outlined in the following issue:

• Unstable formatting on multi-line dollar-quoted strings with sql-formatter dialect option Sec-ant/prettier-plugin-embed#45

Kapture.2023-12-03.at.19.08.00.mp4

Suggested Solution

Either of the following would resolve this issue:

1. for queries with $$, format the rest of the string with indentation matching the delta from the first line (in this example, -12) à la dedent
2. avoid any type of indentation with any queries including $$

Related

• Formatting issue for DO $$ block #183
• [FORMATTING] Inner SQL don't format when using $$ #626
• Feature Request: SQL in SQL prettification #680

[nene]

The formatting works as designed. It's just that the contents of $$-quoted string aren't indented, a feature requested in #680 which is not implemented. So in that sense this bug report is a duplicate of that feature request.

[karlhorky]

If there is a $$ in the query, could the indentation be left alone on the first line too? (marked as "Alternative" above)

[nene]

That would go against the core formatting logic, which is that the formatter discards all non-significant whitespace.

The whitespace inside strings is assumed to be significant and therefore not modified. For example one could write a python script inside $$-quoted string. Whitespace in Python is significant and modifying it would lead to broken code.

[karlhorky]

Understood, then maybe this is something for prettier-plugin-embed to fix / implement after all (eg. running dedent on strings to remove the extra indentation from the template literal before formatting further)

[karlhorky]

Since this seems to be a dead end for any kind of implementation in sql-formatter, this issue can be closed?