Possible to create a function that returns more than one column?

Question

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.

Answer 1

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.

(That might not be completely syntactically correct, but demonstrates the concept.)