[SPARK-39247] Support returning a table or set of rows in CREATE FUNCTION

• Type: New Feature
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.3.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Erik Jansen
• Created: 21/May/22 10:25
• Updated: 08/Jun/24 17:54

Description

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;"

Issue Links

• is related to: SPARK-43797 Python User-defined Table Functions