Formatting IF/END IF

[Jimex]

The IF/END IF block wasn’t formatted correctly.

IF v_count = 0 THEN
RAISE_APPLICATION_ERROR (-20001, 'Error');
END IF;

Expected Output

IF v_count = 0 THEN
       RAISE_APPLICATION_ERROR (-20001, 'Error');
END IF;

Actual Output

IF v_count = 0 THEN RAISE_APPLICATION_ERROR (-20001, 'Error');


END IF;

Usage

• What SQL language(s) does this apply to?
• PLSQL
• Which SQL Formatter version are you using? (The exact version number.)
• 15.6.6

[nene]

Unfortunately this formatter does not really handle procedural SQL. That's unlikely to get fixed as the architecture of the formatter is not really suited for it. There might be some hacks I could do to improve it, but it would remain fundamentally broken in that regard.

I really should rename the plsql dialect to oracle or something like that. For historical reasons it's been named plsql, but it's really misleading.

[Jimex]

Thank you for the clarification. I really appreciate your formatter, it’s the only one I’ve found that aligns keywords to the right so well, and I find that feature extremely valuable.

I hope you’ll consider expanding it in the future to support stored procedural SQL formatting as well.

[nene]

I'm sorry to inform you that the feature that aligns keywords to the right is deprecated :(

My hope for the future is currently in prettier-plugin-sql-cst. It has a proper architecture to make it possible to support procedural SQL. Though for now it doesn't yet support any procedural SQL (well, maybe a little) and it doesn't support Oracle. And adding additional dialects to it is much more work, as it effectively requires implementing a whole parser. So currently it's the case of worse-is-better, where SQL Formatter can easily support lots of dialects, but it has fundamental shortcomings, that prevent it from doing a really great job at formatting.

[Jimex]

I'm sorry to inform you that the feature that aligns keywords to the right is deprecated :(

It’s a real pity—this feature is super useful for traditional SQL developers like me

[nene]

Frankly I don't really know how many people actually use it (I only know that most people using the formatter don't ). I haven't encountered any SQL style guide or documentation that uses such a style. Even the original author of the implementation didn't personally use that style, he sort of came up with it from thin air. So part of the problem with supporting it is that I don't even know how that style should ideally work.

When looking at your example, the right-aligned style would not really work for it even when the formatter would recognize the procedural syntax properly. I would think that by this right-aligned style it would need to be indented like so:

       IF v_count = 0 THEN
RAISE_APPLICATION_ERROR (-20001, 'Error');
      END IF;

That's because the first word on second line should be aligned right from 20 characters position, but RAISE_APPLICATION_ERROR is longer than 20 characters. This also highlights a major shortcoming of this indentation style - I don't think it can be applied consistently for all the SQL code.

Another major reason is that supporting multiple formatting styles is just a nuisance. The formatter supports one primary style, and the other styles are implemented in more of a bolted-on fashion.

[Jimex]

The right-align feature actually originated from Oracle's SQL Developer application, and it was intended to apply only to query keywords such as SELECT, UPDATE, DELETE, FROM, WHERE, etc.

[nene]

Good to know that there's some other tool supporting such style.