[VSCODE] Multi-character operators in PostgreSQL syntax are not recognized

[inferrinizzard]

<Transferred from inferrinizzard#54>

Describe the bug

Multi-character operators in PostreSQL syntax are not recognized.

I prefer to have the denseOperators config set to false so that I can have nice spacing in my SQL for readability. Unfortunately with that configuration set, JSON and JSONB operators go from ->> to - > > which causes PostgreSQL to throw an error stating unexpected >.

More about those operators here: PostgreSQL Docs

Expected Output

Given the following SQL for PostgreSQL:

select
    id,
    data->>'name'
from
    table_with_jsonb_column
;

I expect to format as:

select
    id,
    data ->> 'name'
from
    table_with_jsonb_column
;

Actual Output

What is currently formatted as:

select
    id,
    data - > > 'name'
from
    table_with_jsonb_column
;

Usage

• How are you calling / using the script? (Please provide a code snippet, if applicable)
• What SQL language(s) does this apply to?
• What Node version? (If applicable)

I am using this through the VS Code extension which uses ^5.0.0-beta.1 of this package. I tried to confirm that it was not an issue with the extension as best I could, but apologies if it turns out this is a bug with the extension and not this package.

I believe these operators are likely only for PostgreSQL dialects of SQL.

Additional context

Depends on microsoft/vscode-mssql#17182,
Workaround currently exists with the SQLFlavourOverride option for those affected

[nene]

This is fixed in sql-formatter 6.x.

@inferrinizzard you upgraded the vscode plugin to use 6.x, but haven't released the new version.

[inferrinizzard]

@nene the issue here is not of the Postgres operators, which was already fixed in 5.x, it is of detecting whether the current language in VSCode is Postgres, which the plugin cannot reliably do without some communication with the Microsoft pgsql extension

[nene]

Aha, I see. Should change the title to better reflect the actual issue here.

[jldevezas]

So what is the solution here?

[nene]

If I understand this issue correctly, the extension (sometimes? always?) fails to do SQL flavor detection. Looks like this depends on other SQL-related extensions used.

A workaround is to tell the extension which SQL dialect you're formatting by using the SQLFlavour Override setting.

@inferrinizzard please correct me if I'm wrong.

[jldevezas]

Great, that worked! Just added "Prettier-SQL.SQLFlavourOverride": "postgresql" to settings.json (via the UI). Finally an SQL formatting extension that works on vscode! I was having a hard time formatting long queries, but not anymore (I hope). Thanks!

[inferrinizzard]

This is correct, this issue only occurs with Postgres and MSSQL on VSCode since they each have their own respective Microsoft Language Extensions that don't provide a VSCode language context so it's currently impossible for us to detect if the SQL language is Postgres or MSSQL