Repository: tobymao/sqlglot

Issue:
[Postgres] Parser does not support string concatenation operator inside substring function
Example (valid sql):
```
select substring(2022::char(4) || lpad(3::char(2), 2, '0') from 3 for 4)
```
Result:
```
ParseError: Expecting ). Line 1, Col: 31.
  select substring(2022::char(4)||lpad(3::char(2), 2, '0') from 3 for 4)

Expected table name. Line 1, Col: 63.
  select substring(2022::char(4)||lpad(3::char(2), 2, '0') from 3 for 4)

Required keyword: 'this' missing for <class 'sqlglot.expressions.Table'>. Line 1, Col: 63.
  select substring(2022::char(4)||lpad(3::char(2), 2, '0') from 3 for 4)
```

And it seems like https://github.com/tobymao/sqlglot/issues/421 is still an issue here (I'm using sqlglot 6.0.7).

Discussion:

Patch:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 2df99526..63e40897 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1936,12 +1936,12 @@ class Parser:
         # Postgres supports the form: substring(string [from int] [for int])
         # https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
 
-        args = self._parse_csv(self._parse_term)
+        args = self._parse_csv(self._parse_bitwise)
 
         if self._match(TokenType.FROM):
-            args.append(self._parse_term())
+            args.append(self._parse_bitwise())
             if self._match(TokenType.FOR):
-                args.append(self._parse_term())
+                args.append(self._parse_bitwise())
 
         this = exp.Substring.from_arg_list(args)
         self.validate_expression(this, args)


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 9c4d4f0a..db5fc2d1 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -72,6 +72,9 @@ class TestPostgres(Validator):
         self.validate_identity(
             "SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '%#\"o_a#\"_' FOR '#') IN ('mas')"
         )
+        self.validate_identity(
+            "SELECT SUBSTRING('bla' + 'foo' || 'bar' FROM 3 - 1 + 5 FOR 4 + SOME_FUNC(arg1, arg2))"
+        )
         self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
         self.validate_identity(
             "SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)"
@@ -128,6 +131,12 @@ class TestPostgres(Validator):
                 "spark": "SELECT * FROM x WHERE SUBSTRING(col1, 3 + LENGTH(col1) - 10, 10) IN (col2)",
             },
         )
+        self.validate_all(
+            "SELECT SUBSTRING(CAST(2022 AS CHAR(4)) || LPAD(CAST(3 AS CHAR(2)), 2, '0') FROM 3 FOR 4)",
+            read={
+                "postgres": "SELECT SUBSTRING(2022::CHAR(4) || LPAD(3::CHAR(2), 2, '0') FROM 3 FOR 4)",
+            },
+        )
         self.validate_all(
             "SELECT TRIM(BOTH ' XXX ')",
             write={