[FORMATTING] Inconsistent formatting of PostgreSQL CREATE POLICY commands

[peperoli]

Input data

Which SQL and options did you provide as input?

CREATE POLICY "Allow users to select their own records" ON public.user_achievements
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

CREATE POLICY "Allow users to insert their own records" ON public.user_achievements
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

Actual Output

CREATE POLICY "Allow users to select their own records" ON public.user_achievements FOR
SELECT
  TO authenticated USING (user_id = auth.uid ());

CREATE POLICY "Allow users to insert their own records" ON public.user_achievements FOR INSERT TO authenticated
WITH
  CHECK (user_id = auth.uid ());

Those two similar statements are formatted differently for no apparent reason.

Usage

• How are you calling / using the library? tested with the demo and prettier-plugin-sql
• What SQL language(s) does this apply to? PostgreSQL
• Which SQL Formatter version are you using? (The exact version number.) 15.7.0

[nene]

Thanks for reporting.

I guess this can be improved by explicitly detecting the FOR {SELECT | INSERT | ...} syntax. And then also the WITH CHECK syntax. But properly dealing with TO role syntax is pretty hard for the formatter. These are really just patches on top of the fundamental shortcomings of the architecture of the formatter.

As you're already using Prettier, I would suggest you try out prettier-plugin-sql-cst which handles PostgreSQL quite a bit better. Though not all of PostgreSQL is fully supported yet.

[peperoli]

Yeah, I think detecting FOR {SELECT | INSERT | ...}, USING and WITH CHECK would defenitely be an improvement.

Thanks for the recommendation. I was a bit hesitant to use prettier-plugin-sql-cst as it says "postgresql: experimental! (expect crashes)" but I'll try it out.

[nene]

Yeah, it definitely can and will crash. The question is how often. For most of the usual PostgreSQL code it should work fine.

The main difference is that when it encounters unknown syntax, it will crash, while SQL Formatter will just continue on.

So you have a choice of: SQL Formatter with worse formatting, but fewer crashes, or the prettier plugin with better formatting, but more potential crashes. However, there's a workaround to use conditional comments to disable the parsing & formatting of problematic parts.