Set Returning Functions

This section describes functions that can return more than one row. Set returning functions are usually used in SQL queries in the FROM clause.

The unnest function expands the elements of an array.

You can also call unnest with multiple array parameters. In this case, the arrays are expanded pairwise. If the arrays aren’t all the same length, then the shorter ones are padded with NULL values.

The external function reads data stored in an external file format from one or multiple external locations. It takes the form‌ of:

Where <option> can be one of:

<source_location>

A location to read the data from. See External Location.

FORMAT => format_name

Selects the data format to be read. You can exclude this option if the format is in the file extension. All extensions in a source list must match. For supported formats, see External Formats.

format_specific_option => value

A format-specific option. See External Formats.

Read a local CSV file from the working directory of the server, having two columns and a custom delimiter. A filter is applied to the price column.

Reading from multiple CSV files:

Reading an Apache Parquet file from Amazon S3 by using empty credentials and inferring the bucket region. The schema of the file is inferred from the schema information in the file. The file format is inferred from the file extension.

With explicit Amazon S3 credentials and bucket region:

Generate a series of values, from start to stop with a given step size

The default step size is 1.

This function doesn’t return any rows if start is greater than stop and the step size is positive. It also doesn’t return any rows if start is less than stop and the step size is negative. Any input of null returns zero rows.