Table Functions

Table functions are functions that produce a set of rows. They are used like a table or subquery in the FROM clause of a query.

Table functions can also be used in the select list of a query. The table functions will be evaluated per row of the relations in the FROM clause, generating one or more rows which are appended to the result set. If multiple table functions with different amount of rows are used, null values will be returned for the functions that are exhausted. An example:

  1. cr> select unnest([1, 2, 3]), unnest([1, 2]);
  2. +-------------------+----------------+
  3. | unnest([1, 2, 3]) | unnest([1, 2]) |
  4. +-------------------+----------------+
  5. |                 1 |              1 |
  6. |                 2 |              2 |
  7. |                 3 |           NULL |
  8. +-------------------+----------------+
  9. SELECT 3 rows in set (... sec)

Note

Table functions in the select list are executed after aggregations. So aggregations can be used as arguments to table functions, but the other way around is not allowed, unless sub queries are utilized. (SELECT aggregate_func(col) FROM (SELECT table_func(…) as col) …)

Table of Contents

empty_row( )

empty_row doesn’t take any argument and produces a table with an empty row and no column.

  1. cr> select * from empty_row();
  2. SELECT OK, 1 row affected  (... sec)

unnest( array [ array , ] )

unnest takes any number of array parameters and produces a table where each provided array argument results in a column.

The columns are named colN where N is a number starting at 1.

  1. cr> select * from unnest([1, 2, 3], ['Arthur', 'Trillian', 'Marvin']);
  2. +------+----------+
  3. | col1 | col2     |
  4. +------+----------+
  5. |    1 | Arthur   |
  6. |    2 | Trillian |
  7. |    3 | Marvin   |
  8. +------+----------+
  9. SELECT 3 rows in set (... sec)

If an array with object literals is passed into unnest the object will be regarded as a object with column policy ignored. This means that it is not possible to access values of the object using the subscript notation:

  1. cr> select col1['x'] from unnest([{x=10}]);
  2. SQLActionException[ColumnUnknownException: Column col1['x'] unknown]

generate_series(start, stop, [step])

Generate a series of values from inclusive start to inclusive stop with step increments. The step parameter is optional and defaults to 1.

The arguments can be either of type integer or long and the return value will match the argument types.

  1. cr> SELECT * FROM generate_series(1, 4);
  2. +------+
  3. | col1 |
  4. +------+
  5. |    1 |
  6. |    2 |
  7. |    3 |
  8. |    4 |
  9. +------+
  10. SELECT 4 rows in set (... sec)