Repository: tobymao/sqlglot

Issue:
Postgresql overlay string function causes parser error
When parsing the following PostgreSQL command with dialect="postgres";

`select overlay('Txxxxas' placing 'hom' from 2 for 4)`

SqlGlot throws ParseError:

![Screenshot 2024-09-25 170438](https://github.com/user-attachments/assets/0e6cd1d2-dc5f-4a28-9157-fca57d398e99)


https://w3resource.com/PostgreSQL/overlay-function.php


Discussion:

Patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 35de7115..c5e40bed 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5913,6 +5913,10 @@ class Normalize(Func):
     arg_types = {"this": True, "form": False}
 
 
+class Overlay(Func):
+    arg_types = {"this": True, "expression": True, "from": True, "for": False}
+
+
 # https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
 class Predict(Func):
     arg_types = {"this": True, "expression": True, "params_struct": False}
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index c7473b27..1f85126b 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4378,3 +4378,12 @@ class Generator(metaclass=_Generator):
             func = f"*{func}"
 
         return func
+
+    def overlay_sql(self, expression: exp.Overlay):
+        this = self.sql(expression, "this")
+        expr = self.sql(expression, "expression")
+        from_sql = self.sql(expression, "from")
+        for_sql = self.sql(expression, "for")
+        for_sql = f" FOR {for_sql}" if for_sql else ""
+
+        return f"OVERLAY({this} PLACING {expr} FROM {from_sql}{for_sql})"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 125a1c5e..5f734967 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1107,6 +1107,7 @@ class Parser(metaclass=_Parser):
         "MATCH": lambda self: self._parse_match_against(),
         "NORMALIZE": lambda self: self._parse_normalize(),
         "OPENJSON": lambda self: self._parse_open_json(),
+        "OVERLAY": lambda self: self._parse_overlay(),
         "POSITION": lambda self: self._parse_position(),
         "PREDICT": lambda self: self._parse_predict(),
         "SAFE_CAST": lambda self: self._parse_cast(False, safe=True),
@@ -7473,3 +7474,14 @@ class Parser(metaclass=_Parser):
             principals=principals,
             grant_option=grant_option,
         )
+
+    def _parse_overlay(self) -> exp.Overlay:
+        return self.expression(
+            exp.Overlay,
+            **{  # type: ignore
+                "this": self._parse_bitwise(),
+                "expression": self._match_text_seq("PLACING") and self._parse_bitwise(),
+                "from": self._match_text_seq("FROM") and self._parse_bitwise(),
+                "for": self._match_text_seq("FOR") and self._parse_bitwise(),
+            },
+        )


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index e4d239e5..63266a50 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -800,6 +800,8 @@ class TestPostgres(Validator):
         self.validate_identity(
             "SELECT 1 FROM ((VALUES (1)) AS vals(id) LEFT OUTER JOIN tbl ON vals.id = tbl.id)"
         )
+        self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1)")
+        self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1 FOR 1)")
 
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier