Repository: DerekStride/tree-sitter-sql (language: python)

Issue:
Parsing PostgreSQL FILTER aggregate expressions
I've noticed the grammar does not handle [PostgreSQL aggregate expressions](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES) well. In particular, it does not seem to handle `FILTER` aggregate expressions.

<details>

<summary>SQL query from the documentation</summary>

Parsing the example SQL query provided in the documentation:

```sql
SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
```

yields the following AST:

```
(program
  (statement
    (select
      (keyword_select)
      (select_expression
        (term
          (invocation
            (object_reference
              (identifier))
            (term
              (all_fields)))
          (keyword_as)
          (identifier))
        (ERROR
          (term
            (invocation
              (object_reference
                (identifier))
              (term
                (all_fields)))
            (identifier)))
        (term
          (binary_expression
            (field
              (identifier))
            (ERROR)
            (literal))
          (keyword_as)
          (identifier))))
    (from
      (keyword_from)
      (relation
        (invocation
          (object_reference
            (identifier))
          (term
            (literal))
          (term
            (literal)))
        (keyword_as)
        (identifier)
        (list
          (column
            (identifier)))))))
```

This query executes just fine:

![image](https://github.com/DerekStride/tree-sitter-sql/assets/889383/18acadef-5c5f-497e-9fa4-1c6499fa6a0c)


</details>

<details>

<summary>A more complex query</summary>

Parsing a more complex SQL query that uses aggregate expressions:

```sql
SELECT
    count(*) filter (where exists(select regexp_matches(issue_url, '.*/pull/.*'))) as no_pull_request_comments,
    count(*) filter (where exists(select regexp_matches(issue_url, '.*/issue/.*'))) as no_issue_comments
FROM "Gelio/some-repo:latest".comments
```

yields the following AST:

```
(program
  (statement
    (select
      (keyword_select)
      (ERROR
        (term
          (invocation
            (object_reference
              (identifier))
            (term
              (all_fields)))
          (identifier)))
      (select_expression
        (term
          (ERROR
            (field
              (identifier)))
          (exists
            (keyword_exists)
            (subquery
              (select
                (keyword_select)
                (select_expression
                  (term
                    (invocation
                      (object_reference
                        (identifier))
                      (term
                        (field
                          (identifier)))
                      (term
                        (literal))))))))
          (keyword_as)
          (identifier))
        (ERROR
          (term
            (invocation
              (object_reference
                (identifier))
              (term
                (all_fields)))
            (identifier)))
        (term
          (ERROR
            (field
              (identifier)))
          (exists
            (keyword_exists)
            (subquery
              (select
                (keyword_select)
                (select_expression
                  (term
                    (invocation
                      (object_reference
                        (identifier))
                      (term
                        (field
                          (identifier)))
                      (term
                        (literal))))))))
          (keyword_as)
          (identifier))))
    (from
      (keyword_from)
      (relation
        (object_reference
          (identifier)
          (identifier))))))

```


Discussion / hints:

Fix patch:
diff --git a/grammar.js b/grammar.js
index 617a6a64..35e2356c 100644
--- a/grammar.js
+++ b/grammar.js
@@ -99,6 +99,7 @@ module.exports = grammar({
     keyword_as: _ => make_keyword("as"),
     keyword_distinct: _ => make_keyword("distinct"),
     keyword_constraint: _ => make_keyword("constraint"),
+    keyword_filter: _ => make_keyword("filter"),
     keyword_cast: _ => make_keyword("cast"),
     keyword_separator: _ => make_keyword("separator"),
     keyword_max: _ => make_keyword("max"),
@@ -1927,6 +1928,13 @@ module.exports = grammar({
       ')',
     ),
 
+    filter_expression : $ => seq(
+      $.keyword_filter,
+      '(',
+      $.where,
+      ')',
+    ),
+
     invocation: $ => prec(1,
       seq(
         $.object_reference,
@@ -1956,6 +1964,9 @@ module.exports = grammar({
             ')',
           ),
         ),
+        optional(
+          $.filter_expression
+        )
       ),
     ),
 


Test patch:
diff --git a/test/corpus/select.txt b/test/corpus/select.txt
index f997024c..78b01d82 100644
--- a/test/corpus/select.txt
+++ b/test/corpus/select.txt
@@ -2668,3 +2668,40 @@ parenthesized select
       (select_expression
         (term
           (literal))))))
+
+================================================================================
+Select with filtered aggregation
+================================================================================
+
+SELECT
+    count(*) FILTER (WHERE i < 5) AS filtered
+FROM tab
+
+--------------------------------------------------------------------------------
+
+(program
+  (statement
+    (select
+      (keyword_select)
+      (select_expression
+        (term
+          (invocation
+            (object_reference
+              (identifier))
+            (term
+              (all_fields))
+            (filter_expression
+              (keyword_filter)
+              (where
+                (keyword_where)
+                (binary_expression
+                  (field
+                    (identifier))
+                  (literal)))))
+          (keyword_as)
+          (identifier))))
+    (from
+      (keyword_from)
+      (relation
+        (object_reference
+          (identifier))))))