[
https://issues.apache.org/jira/browse/SPARK-39247?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17853410#comment-17853410
]
Jonathan Boarman commented on SPARK-39247:
------------------------------------------
Being able to create a *UDTF* (user-defined table function), also known as a
tabular UDF, table UDF, UDF table function, or a TVF (user-defined table-value
function), are incredibly useful and very powerful, especially in combination
with the LATERAL invocations.
Other platforms, such as SQL Server has offered this feature for more than 20
years back, which means this has been around long enough to develop deep and
frequent use cases in more mature data teams.
{panel:title=Documentation from a few major data platforms covering their
implementation usage: }
*_Databricks -- Create a SQL table function_*
https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-create-sql-function.html#create-a-sql-table-function
*_Snowflake -- Creating Tabular SQL UDFs (UDTFs)_*
https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-tabular-functions
*_BigQuery -- Creating table-valued functions (TVF)_*
https://cloud.google.com/bigquery/docs/table-functions
*_Postgres -- SQL Functions Returning TABLE_*
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE
*_SAP HANA -- Table user-defined functions (TUDF)_*
https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/2fc6d7beebd14c579457092e91519082.html?q=create%20function
*_IBM DB2 -- CREATE FUNCTION (user-defined external table)_*
https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-function-external-table
*_Oracle -- Pipelined Table Functions_*
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/PIPELINED-clause.html
*_MSSQL -- Inline and Multi-statement table-valued functions (TVF/MSTVF)_*
https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver16#inline-table-valued-function-tvf
{panel}
> Support returning a table or set of rows in CREATE FUNCTION
> -----------------------------------------------------------
>
> Key: SPARK-39247
> URL: https://issues.apache.org/jira/browse/SPARK-39247
> Project: Spark
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 3.3.0
> Reporter: Erik Jansen
> Priority: Major
>
> The Databricks version supports create a function which returns a result set:
> _"Creates a SQL scalar or table function that takes a set of arguments and
> returns a scalar value or a set of rows."_
> Such functions are very useful as the user can pass a list of parameters
> which can be used in the function to create a dynamic result set (a query
> which will use the passed parameters).
> See the example from databricks:
> {_}"{_}{_}-- Produce all weekdays between two dates > CREATE FUNCTION
> weekdays(start DATE, end DATE) RETURNS TABLE(day_of_week STRING, day DATE)
> RETURN SELECT extract(DAYOFWEEK_ISO FROM day), day FROM (SELECT
> sequence(weekdays.start, weekdays.end)) AS T(days) LATERAL VIEW explode(days)
> AS day WHERE extract(DAYOFWEEK_ISO FROM day) BETWEEN 1 AND 5;"{_}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
