Add support for Postgres Aggregate expressions

[roman-vanesyan]

Hey! First of all, I'd like to thank you for your work. You're truly doing an awesome job!

Recently I've been dealing with Postgres aggregate expressions and https://github.com/nene/prettier-plugin-sql-cst is failing on formatting them. AFAIA, the prettier plugin is based on this parser. I tried to look across this repository's issues but seems like there is no tracking ticket for this functionality.

Here you can read about aggregate expressions https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-AGGREGATES.

Here is an example of a query for which formatter is failing:

SELECT ARRAY_AGG(id) FILTER (WHERE status = 'active') AS ids
FROM email_addresses
WHERE id = ANY (@to_email_addresses::UUID[]); 

The stack trace:

[error] db/query.sql: Error: Syntax Error: Unexpected ")"
[error] Was expecting to see: "!", "!=", "!~", "!~*", "!~~", "!~~*", "#", "#-", "#>", "#>>", "%", "&", "(", "*", "+", "-", "->", "->>", ".", "/", "::", "<", "<<", "<=", "<>", "<@", "=", ">", ">=", ">>", "?", "?&", "?|", "@", "@>", "@?", "@@", "AND", "AT", "BETWEEN", "COLLATE", "ILIKE", "IN", "IS", "ISNULL", "LIKE", "NOT", "NOTNULL", "OPERATOR", "OR", "SIMILAR", "[", "^", "^@", "`", "|", "|/", "||", "||/", "~", "~*", "~~", "~~*", or whitespace
[error] --> /Users/roman/dev/work/atcirclesquare/emailer/db/query.sql:43:53
[error]    |
[error] 43 | SELECT ARRAY_AGG(id) FILTER (WHERE status = 'active') AS ids
[error]    |                                                     ^
[error]     at parse (/emailer/node_modules/sql-parser-cst/lib/main.js:40:19)
[error]     at Object.parse (/emailer/node_modules/prettier-plugin-sql-cst/dist/index.js:41:76)
[error]     at parse4 (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/index.mjs:22117:24)
[error]     at async coreFormat (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/index.mjs:22607:7)
[error]     at async formatWithCursor (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/index.mjs:22809:14)
[error]     at async formatFiles (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/internal/cli.mjs:6673:18)
[error]     at async main (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/internal/cli.mjs:7081:5)
[error]     at async Module.run (file://node-versions/v20.11.1/installation/lib/node_modules/prettier/internal/cli.mjs:7027:5)

Here are the dependencies versions:

  "dependencies": {
    "prettier": "^3.2.5",
    "prettier-plugin-sql-cst": "^0.11.4"
  }

Prettier config:

    {
      files: ["*.sql"],
      options: {
        parser: "postgresql",
        sqlCanonicalSyntax: true,
        sqlKeywordCase: "upper",
        sqlParamTypes: ["?", "$nr", ":name", "@name", "$name"]
      }
    }

[nene]

Thanks for reporting.

This is indeed a problem in the parser. Looks like I have skipped over this part of PostgreSQL documentation. Thanks for the link.

Apparently I have explicitly enabled the FILTER syntax only for SQLite. Should be a simple fix to right it.

[nene]

Fixed the FILTER-issue. But there's more in this aggregate function syntax that I'm missing.

Will probably do a release with just that fix though.

[roman-vanesyan]

Could you please cut the release as well?

[nene]

@roman-vanesyan the release with this fix is now out and I've also released new version of the prettier plugin.