Repository: tobymao/sqlglot

Issue:
Transpile functions to DuckDB using "CREATE MACRO"
I'd like to transpile some sql udf from databricks to duckdb, which seems to be lacking support for duckdb's `create macro` statement: https://duckdb.org/docs/sql/statements/create_macro.html 

So bascially I tried this:

```python
import sqlglot as sg
sql = """create or replace function
    udf_overlapping_ranges(
        from_id1 bigint,
        to_id1 bigint,
        from_id2 bigint,
        to_id2 bigint,
        min_interval bigint default 1
    )
returns bigint
return from_id1 """
sg.transpile(sql, "databricks", "duckdb")
```

which returns: `'CREATE OR REPLACE FUNCTION udf_overlapping_ranges(from_id1, to_id1, from_id2, to_id2, min_interval) AS RETURN from_id1'`

It's correct to drop the returns, though at best it would also support transpiling Table UDF's. In [Databricks](https://www.databricks.com/blog/2021/10/20/introducing-sql-user-defined-functions.html) they are defined as something like `RETURNS TABLE(name STRING )`, in duckdb it's without schema: `CREATE MACRO dynamic_table(col1_value, col2_value) AS TABLE`



Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index c590c6ec..c2f58a16 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -389,6 +389,7 @@ class DuckDB(Dialect):
         SUPPORTS_TO_NUMBER = False
         COPY_HAS_INTO_KEYWORD = False
         STAR_EXCEPT = "EXCLUDE"
+        PAD_FILL_PATTERN_IS_REQUIRED = True
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -456,6 +457,8 @@ class DuckDB(Dialect):
             ),
             exp.RegexpLike: rename_func("REGEXP_MATCHES"),
             exp.RegexpSplit: rename_func("STR_SPLIT_REGEX"),
+            exp.Return: lambda self, e: self.sql(e, "this"),
+            exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",
             exp.Rand: rename_func("RANDOM"),
             exp.SafeDivide: no_safe_divide_sql,
             exp.Split: rename_func("STR_SPLIT"),
@@ -617,6 +620,7 @@ class DuckDB(Dialect):
         # can be transpiled to DuckDB, so we explicitly override them accordingly
         PROPERTIES_LOCATION[exp.LikeProperty] = exp.Properties.Location.POST_SCHEMA
         PROPERTIES_LOCATION[exp.TemporaryProperty] = exp.Properties.Location.POST_CREATE
+        PROPERTIES_LOCATION[exp.ReturnsProperty] = exp.Properties.Location.POST_ALIAS
 
         def strtotime_sql(self, expression: exp.StrToTime) -> str:
             if expression.args.get("safe"):
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index a447d136..5120a1d8 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -447,6 +447,7 @@ class Hive(Dialect):
         SUPPORTS_TO_NUMBER = False
         WITH_PROPERTIES_PREFIX = "TBLPROPERTIES"
         PARSE_JSON_NAME = None
+        PAD_FILL_PATTERN_IS_REQUIRED = True
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
             exp.Insert,
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 4ffe2fd7..630ced55 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -690,6 +690,7 @@ class MySQL(Dialect):
         JSON_KEY_VALUE_PAIR_SEP = ","
         SUPPORTS_TO_NUMBER = False
         PARSE_JSON_NAME = None
+        PAD_FILL_PATTERN_IS_REQUIRED = True
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 44ea12bb..f3258c8e 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -352,6 +352,7 @@ class Presto(Dialect):
         SUPPORTS_TO_NUMBER = False
         HEX_FUNC = "TO_HEX"
         PARSE_JSON_NAME = "JSON_PARSE"
+        PAD_FILL_PATTERN_IS_REQUIRED = True
 
         PROPERTIES_LOCATION = {
             **generator.Generator.PROPERTIES_LOCATION,
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 623aaba7..35944d52 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -131,6 +131,7 @@ class Spark(Spark2):
 
     class Generator(Spark2.Generator):
         SUPPORTS_TO_NUMBER = True
+        PAD_FILL_PATTERN_IS_REQUIRED = False
 
         TYPE_MAPPING = {
             **Spark2.Generator.TYPE_MAPPING,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index c70f7839..2d86d2da 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4798,6 +4798,11 @@ class List(Func):
     is_var_len_args = True
 
 
+# String pad, kind True -> LPAD, False -> RPAD
+class Pad(Func):
+    arg_types = {"this": True, "expression": True, "fill_pattern": False, "is_left": True}
+
+
 # https://docs.snowflake.com/en/sql-reference/functions/to_char
 # https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
 class ToChar(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index c860bdb5..7e9bde76 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -375,6 +375,9 @@ class Generator(metaclass=_Generator):
     # Whether to quote the generated expression of exp.JsonPath
     QUOTE_JSON_PATH = True
 
+    # Whether the text pattern/fill (3rd) parameter of RPAD()/LPAD() is optional (defaults to space)
+    PAD_FILL_PATTERN_IS_REQUIRED = False
+
     # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
     PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 
@@ -997,6 +1000,7 @@ class Generator(metaclass=_Generator):
             expression_sql = f"{begin}{self.sep()}{expression_sql}{end}"
 
             if self.CREATE_FUNCTION_RETURN_AS or not isinstance(expression.expression, exp.Return):
+                postalias_props_sql = ""
                 if properties_locs.get(exp.Properties.Location.POST_ALIAS):
                     postalias_props_sql = self.properties(
                         exp.Properties(
@@ -1004,9 +1008,8 @@ class Generator(metaclass=_Generator):
                         ),
                         wrapped=False,
                     )
-                    expression_sql = f" AS {postalias_props_sql}{expression_sql}"
-                else:
-                    expression_sql = f" AS{expression_sql}"
+                postalias_props_sql = f" {postalias_props_sql}" if postalias_props_sql else ""
+                expression_sql = f" AS{postalias_props_sql}{expression_sql}"
 
         postindex_props_sql = ""
         if properties_locs.get(exp.Properties.Location.POST_INDEX):
@@ -4048,3 +4051,12 @@ class Generator(metaclass=_Generator):
         end = f"{self.seg('')}{end}" if end else ""
 
         return f"CHANGES ({information}){at_before}{end}"
+
+    def pad_sql(self, expression: exp.Pad) -> str:
+        prefix = "L" if expression.args.get("is_left") else "R"
+
+        fill_pattern = self.sql(expression, "fill_pattern") or None
+        if not fill_pattern and self.PAD_FILL_PATTERN_IS_REQUIRED:
+            fill_pattern = "' '"
+
+        return self.func(f"{prefix}PAD", expression.this, expression.expression, fill_pattern)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f6510df9..44894d32 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -108,6 +108,15 @@ def build_mod(args: t.List) -> exp.Mod:
     return exp.Mod(this=this, expression=expression)
 
 
+def build_pad(args: t.List, is_left: bool = True):
+    return exp.Pad(
+        this=seq_get(args, 0),
+        expression=seq_get(args, 1),
+        fill_pattern=seq_get(args, 2),
+        is_left=is_left,
+    )
+
+
 class _Parser(type):
     def __new__(cls, clsname, bases, attrs):
         klass = super().__new__(cls, clsname, bases, attrs)
@@ -159,7 +168,11 @@ class Parser(metaclass=_Parser):
         "LOG2": lambda args: exp.Log(this=exp.Literal.number(2), expression=seq_get(args, 0)),
         "LOG10": lambda args: exp.Log(this=exp.Literal.number(10), expression=seq_get(args, 0)),
         "LOWER": build_lower,
+        "LPAD": lambda args: build_pad(args),
+        "LEFTPAD": lambda args: build_pad(args),
         "MOD": build_mod,
+        "RPAD": lambda args: build_pad(args, is_left=False),
+        "RIGHTPAD": lambda args: build_pad(args, is_left=False),
         "SCOPE_RESOLUTION": lambda args: exp.ScopeResolution(expression=seq_get(args, 0))
         if len(args) != 2
         else exp.ScopeResolution(this=seq_get(args, 0), expression=seq_get(args, 1)),


Test patch:
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 471830ff..86612cc7 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -98,6 +98,22 @@ class TestDatabricks(Validator):
                 read="databricks",
             )
 
+        self.validate_all(
+            "CREATE OR REPLACE FUNCTION func(a BIGINT, b BIGINT) RETURNS TABLE (a INT) RETURN SELECT a",
+            write={
+                "databricks": "CREATE OR REPLACE FUNCTION func(a BIGINT, b BIGINT) RETURNS TABLE (a INT) RETURN SELECT a",
+                "duckdb": "CREATE OR REPLACE FUNCTION func(a, b) AS TABLE SELECT a",
+            },
+        )
+
+        self.validate_all(
+            "CREATE OR REPLACE FUNCTION func(a BIGINT, b BIGINT) RETURNS BIGINT RETURN a",
+            write={
+                "databricks": "CREATE OR REPLACE FUNCTION func(a BIGINT, b BIGINT) RETURNS BIGINT RETURN a",
+                "duckdb": "CREATE OR REPLACE FUNCTION func(a, b) AS a",
+            },
+        )
+
     # https://docs.databricks.com/sql/language-manual/functions/colonsign.html
     def test_json(self):
         self.validate_identity("SELECT c1:price, c1:price.foo, c1:price.bar[1]")
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index a6ac69a7..e31b114b 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2573,3 +2573,33 @@ FROM subquery2""",
             """SELECT partition.d FROM t PARTITION (d)""",
             """SELECT partition.d FROM t AS PARTITION(d)""",
         )
+
+    def test_string_functions(self):
+        for pad_func in ("LPAD", "RPAD"):
+            ch_alias = "LEFTPAD" if pad_func == "LPAD" else "RIGHTPAD"
+            for fill_pattern in ("", ", ' '"):
+                with self.subTest(f"Testing {pad_func}() with pattern {fill_pattern}"):
+                    self.validate_all(
+                        f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                        read={
+                            "snowflake": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "databricks": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "spark": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "postgres": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "clickhouse": f"SELECT {ch_alias}('bar', 5{fill_pattern})",
+                        },
+                        write={
+                            "": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "spark": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "postgres": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "clickhouse": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "snowflake": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "databricks": f"SELECT {pad_func}('bar', 5{fill_pattern})",
+                            "duckdb": f"SELECT {pad_func}('bar', 5, ' ')",
+                            "mysql": f"SELECT {pad_func}('bar', 5, ' ')",
+                            "hive": f"SELECT {pad_func}('bar', 5, ' ')",
+                            "spark2": f"SELECT {pad_func}('bar', 5, ' ')",
+                            "presto": f"SELECT {pad_func}('bar', 5, ' ')",
+                            "trino": f"SELECT {pad_func}('bar', 5, ' ')",
+                        },
+                    )