Repository: tobymao/sqlglot

Issue:
CURRENT_SCHEMA Transpilation
Currently, SQLGlot does not have an abstraction for `CURRENT_SCHEMA` and alike. These functions return the string representation of the current schema.

I would like to cover the following engines.

SQLite: `'main'` ([Doc](https://www.sqlite.org/lang_naming.html))
TSQL: `SCHEMA_NAME([schema_id])` ([Doc](https://learn.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql?view=sql-server-ver16))
MySQL: `SCHEMA()` or `DATABASE()` ([Doc](https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_schema))
Postgres: `CURRENT_SCHEMA[()]` ([Doc](https://www.postgresql.org/docs/current/functions-info.html))

Docs for other engines:
Clickhouse: `CURRENT_DATABASE()` ([Doc](https://clickhouse.com/docs/en/sql-reference/functions/other-functions#currentdatabase))
Databricks: `CURRENT_SCHEMA()` ([Doc](https://docs.databricks.com/en/sql/language-manual/functions/current_schema.html))
DuckDB: `CURRENT_SCHEMA()` ([Doc](https://duckdb.org/docs/sql/functions/utility.html#current_schema))
Hive: maybe `CURRENT_DATABASE()` ([SO](https://stackoverflow.com/a/77953269), [Tutorial](https://www.tutorialscampus.com/hive/use-database.htm), though I didn't find it in the docs)
Snowflake: `CURRENT_SCHEMA()` ([Doc](https://docs.snowflake.com/en/sql-reference/functions/current_schema))
Spark: `CURRENT_SCHEMA()` ([Doc](https://spark.apache.org/docs/latest/api/sql/index.html#current_schema))
Oracle: `SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')` ([Doc](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html))
Trino: `CURRENT_SCHEMA` ([Doc](https://trino.io/docs/current/functions/session.html))

Unsupported:
BigQuery
Presto (though `CURRENT_USER` seems to be [supported](https://prestodb.io/docs/current/functions/session.html))

Hints from issue comments:
@georgesittas: FYI, @pruzko and I chatted in Slack and he's planning to tackle this.@georgesittas: Closing this for the time being as it's not in our scope to tackle. Feel free to link this issue to the PR so we can close it properly later.

Solution patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 6a5ed40bf7..d064dd5a8a 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -27,6 +27,7 @@
     trim_sql,
     timestrtotime_sql,
 )
+from sqlglot.generator import unsupported_args
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
 
@@ -316,6 +317,8 @@ class Parser(parser.Parser):
                 this=exp.TsOrDsToDate(this=seq_get(args, 0)),
                 format=exp.Literal.string("%B"),
             ),
+            "SCHEMA": exp.CurrentSchema.from_arg_list,
+            "DATABASE": exp.CurrentSchema.from_arg_list,
             "STR_TO_DATE": _str_to_date,
             "TIMESTAMPDIFF": build_date_delta(exp.TimestampDiff),
             "TO_DAYS": lambda args: exp.paren(
@@ -1265,3 +1268,7 @@ def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"REGEXP_LIKE({self.sql(expression.this)}, '^[[:ascii:]]*$')"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return self.func("SCHEMA")
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index d358ebd461..1d0c862c5e 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -34,10 +34,12 @@
     ts_or_ds_add_cast,
     strposition_sql,
 )
+from sqlglot.generator import unsupported_args
 from sqlglot.helper import is_int, seq_get
 from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 
+
 DATE_DIFF_FACTOR = {
     "MICROSECOND": " * 1000000",
     "MILLISECOND": " * 1000",
@@ -393,6 +395,11 @@ class Parser(parser.Parser):
             "LEVENSHTEIN_LESS_EQUAL": _build_levenshtein_less_equal,
         }
 
+        NO_PAREN_FUNCTIONS = {
+            **parser.Parser.NO_PAREN_FUNCTIONS,
+            TokenType.CURRENT_SCHEMA: exp.CurrentSchema,
+        }
+
         FUNCTION_PARSERS = {
             **parser.Parser.FUNCTION_PARSERS,
             "DATE_PART": lambda self: self._parse_date_part(),
@@ -723,3 +730,7 @@ def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint)
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"({self.sql(expression.this)} ~ '^[[:ascii:]]*$')"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return "CURRENT_SCHEMA"
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index d7d9fb66db..721e23689c 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -17,8 +17,8 @@
     rename_func,
     strposition_sql,
 )
-from sqlglot.tokens import TokenType
 from sqlglot.generator import unsupported_args
+from sqlglot.tokens import TokenType
 
 
 def _date_add_sql(self: SQLite.Generator, expression: exp.DateAdd) -> str:
@@ -302,3 +302,7 @@ def transaction_sql(self, expression: exp.Transaction) -> str:
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"(NOT {self.sql(expression.this)} GLOB CAST(x'2a5b5e012d7f5d2a' AS TEXT))"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return "'main'"
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 8c585cfffc..b30dc6b84e 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -575,6 +575,7 @@ class Parser(parser.Parser):
             "RIGHT": _build_with_arg_as_text(exp.Right),
             "PARSENAME": _build_parsename,
             "REPLICATE": exp.Repeat.from_arg_list,
+            "SCHEMA_NAME": exp.CurrentSchema.from_arg_list,
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
             "SYSDATETIME": exp.CurrentTimestamp.from_arg_list,
             "SUSER_NAME": exp.CurrentUser.from_arg_list,
@@ -927,6 +928,7 @@ class Generator(generator.Generator):
             exp.Min: min_or_least,
             exp.NumberToStr: _format_sql,
             exp.Repeat: rename_func("REPLICATE"),
+            exp.CurrentSchema: rename_func("SCHEMA_NAME"),
             exp.Select: transforms.preprocess(
                 [
                     transforms.eliminate_distinct_on,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index e3cc94e158..ac6ceede67 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5628,6 +5628,10 @@ class CurrentTimestamp(Func):
     arg_types = {"this": False, "sysdate": False}
 
 
+class CurrentSchema(Func):
+    arg_types = {"this": False}
+
+
 class CurrentUser(Func):
     arg_types = {"this": False}
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5a803e52c9..25660e1e1a 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -487,6 +487,7 @@ class Parser(metaclass=_Parser):
         TokenType.CONSTRAINT,
         TokenType.COPY,
         TokenType.CUBE,
+        TokenType.CURRENT_SCHEMA,
         TokenType.DEFAULT,
         TokenType.DELETE,
         TokenType.DESC,
@@ -589,6 +590,7 @@ class Parser(metaclass=_Parser):
         TokenType.COMMAND,
         TokenType.CURRENT_DATE,
         TokenType.CURRENT_DATETIME,
+        TokenType.CURRENT_SCHEMA,
         TokenType.CURRENT_TIMESTAMP,
         TokenType.CURRENT_TIME,
         TokenType.CURRENT_USER,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 0d2704bfaf..06afaa0c6f 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -250,6 +250,7 @@ class TokenType(AutoName):
     CUBE = auto()
     CURRENT_DATE = auto()
     CURRENT_DATETIME = auto()
+    CURRENT_SCHEMA = auto()
     CURRENT_TIME = auto()
     CURRENT_TIMESTAMP = auto()
     CURRENT_USER = auto()
@@ -713,6 +714,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "CROSS": TokenType.CROSS,
         "CUBE": TokenType.CUBE,
         "CURRENT_DATE": TokenType.CURRENT_DATE,
+        "CURRENT_SCHEMA": TokenType.CURRENT_SCHEMA,
         "CURRENT_TIME": TokenType.CURRENT_TIME,
         "CURRENT_TIMESTAMP": TokenType.CURRENT_TIMESTAMP,
         "CURRENT_USER": TokenType.CURRENT_USER,


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index d408f90e1b..6a8b1ca77a 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -3306,3 +3306,19 @@ def test_median(self):
                     "postgres": f"PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
                 },
             )
+
+    def test_current_schema(self):
+        self.validate_all(
+            "CURRENT_SCHEMA()",
+            read={
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA()",
+                "tsql": "SCHEMA_NAME()",
+            },
+            write={
+                "sqlite": "'main'",
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA",
+                "tsql": "SCHEMA_NAME()",
+            },
+        )
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 34ee063364..4a9e2b4df2 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -253,8 +253,7 @@ def test_identity(self):
         self.validate_identity("SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE")
         self.validate_identity("SET TRANSACTION READ ONLY")
         self.validate_identity("SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE")
-        self.validate_identity("SELECT SCHEMA()")
-        self.validate_identity("SELECT DATABASE()")
+        self.validate_identity("DATABASE()", "SCHEMA()")
         self.validate_identity(
             "SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000"
         )
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 6714de2b30..b02cc8cf37 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -73,6 +73,7 @@ def test_postgres(self):
         self.validate_identity("SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)")
         self.validate_identity("CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) * -100")
         self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True)
+        self.validate_identity("SELECT CURRENT_SCHEMA")
         self.validate_identity("SELECT CURRENT_USER")
         self.validate_identity("SELECT * FROM ONLY t1")
         self.validate_identity(
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index dc35610b7a..8960757eef 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -443,6 +443,16 @@ def test_tsql(self):
             "CREATE TABLE db.t1 (a INTEGER, b INTEGER, CONSTRAINT c PRIMARY KEY (a DESC, b))"
         )
 
+        self.validate_all(
+            "SCHEMA_NAME(id)",
+            write={
+                "sqlite": "'main'",
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA",
+                "tsql": "SCHEMA_NAME(id)",
+            },
+        )
+
     def test_option(self):
         possible_options = [
             "HASH GROUP",