Does SQL have the concept of a function that returns multiple columns, outside of a table function? Here would be an example:

SELECT
    id,
    SPLIT_NAME(name, ' ')
FROM
    table

And I want the SPLIT_NAME function to produce two columns, one for first and one for last, so the output would be:

id      first        last
1        tom         jones

I know this can be done with:

SELECT id, SPLIT(...) first, SPLIT(...) last FROM table

Or possibly with a table function such as:

SELECT id, first, last FROM table, SPLIT_NAME(name, ' ')

But, wondering if SQL has any sort of scalar-ish function that can produce multiple outputs, as I think a join could be quite expensive (I think?) if joining to something like a billion rows where (hopefully) the function itself could just be inlined.

Note: either Postgres or SQL Server is fine.

CodePudding user response：

The two concepts you need are...

• inlined table valued functions (even if you only return one row, allows multiple columns)
• APPLY or LATERAL JOIN to call the function for each input row

SQL Server, for example might be written as...

SELECT
  table.id,
  name_parts.first_name,
  name_parts.last_name
FROM
  table
CROSS APPLY
  SPLIT_NAME(table.name, ' ')   AS name_parts

Other dialects might use lateral joins, different functionality all together, or might not have the functionality at all.

EDIT: An example of inline table valued function...

Again, this is SQL Server specific, and does Not generalise to other DBMS...

CREATE FUNCTION [dbo].[SPLIT_NAME] (@name NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
  SELECT
    SUBSTRING(@name, 1, PATINDEX('% %',@name)            ) AS first_name,
    SUBSTRING(@name,    PATINDEX('% %',@name), LEN(@name)) AS last_name

It's inline because there is no begin/end, procedural language, etc. Its just a SELECT statement (which can have a FROM clause, but doesn't have to), and some parameters.

(I wrote that on a phone, it might not be completely syntactically correct, but demonstrates the concept.)