Failure on schema-qualified special OPERATOR in PostgreSQL

[innermatrix]

Describe the bug

echo "select 'a' operator(public.=) 'A';" | npx sql-formatter -l postgresql

Expected behavior

Useful output

Actual behavior

Failure

Usage

$ npx sql-formatter --version    
15.2.0

Yeah, this syntax is weird; unfortunately, it's generated by pg_dump in some cases, so my scripts that pipe schema dump through sql-formatter are failing.

[nene]

Thanks for reporting.

That's definitely a tricky bit of syntax to handle.

You might be interested in trying out prettier-plugin-sql-cst. It doesn't yet have a full PostgreSQL support, but it does handle the OPERATOR() syntax.

I can thing of several hacks to fix this issue in sql-formatter. Will have to figure out which one would be the least hacky way of doing it.

[innermatrix]

Thanks; I am following that project and plan to switch to it when its Postgres support matures more, but for now here we are 🙂

[nene]

If you know of anything particular that's missing from the current PostgreSQL support in the prettier-plugin-sql-cst, feel free to let me know and I might prioritize that. It will take a while for it to support 100% of Postgres syntax, but most likely something like 10% of it is actually used by most people. But I don't know which 10% :)

[zda]

FYI, in case anyone else is having similar issues, I also discovered sql-formatter has trouble with create operator in this Postgres case: https://github.com/jetify-com/typeid-sql/blob/main/sql/04_operator.sql#L29-L34 (it adds spaces inside ===).

I gave prettier-plugin-sql-cst a quick try; it didn't add spaces, but it seems not to support create operator yet. nene/prettier-plugin-sql-cst#38

[roman-pozd]

+1 on the OPERATOR() issue. @nene any plans to fix it?

[nene]

There's actually a workaround for the OPERATOR() syntax. One can define a custom paramType like this:

paramTypes: {
  custom: [{ regex: String.raw`OPERATOR *\([^)]+\)` }]
}

However with regards to CREATE OPERATOR I think it's unlikely to be ever fully supported by SQL Formatter.

[nene]

Well... as I'm looking into this already, might as well fix it...

[nene]

Fixed in 15.4.7