Set Returning Functions

This section describes functions that return multiple rows. You can call set-returning functions in the FROM clause of a SQL query:

1SELECT * FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) AS days(day)

• unnest - Expands the elements of an array.
• external - Reads data stored in an external file format from one or multiple external locations.
• generate_series (Numerical) - Generates a series of numerical values from start to stop with a given step size.
• generate_series (Time-based) - Generates a series of date or timestamp values from start to stop with a given interval step size.
• regexp_matches - Returns the captured groups of each match of a regular expression pattern.
• result_scan - Returns the result set of a previously executed query as a table. Available in Data 360 SQL only.

In SQL, all row sets are unordered by default. Due to implementation details such as parallelization, rows might be processed and output in any order.

Sometimes, the order of rows generated by a set-returning function is important, though. In such cases, the WITH ORDINALITY clause can be used to add an additional output column, assigning a sequential number to the generated rows:

1SELECT *
2FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) WITH ORDINALITY AS days(day, ordinality)
3ORDER BY ordinality

Results:

The WITH ORDINALITY clause is supported by most, but not all, set-returning functions.

• SELECT
• FROM Clause