SingleStoreDB v8.1

TABLE

It is a table-valued function that converts a PSQL array to a set of rows.

Syntax

TABLE(array)

Arguments

array: A PSQL array of scalar values.

Return Type

A set of table-type rows.

Remarks

It returns a single column of output named table_col, with one row for each array entry.
The TABLE() function can be used only in the FROM clause of a query.
The array argument to the TABLE() function must contain only scalar values of valid column data types.
Array of RECORDS and multi-dimensional arrays are not supported as arguments to the TABLE() function.
PSQL arrays are not supported as a table data type or array literals in SingleStoreDB queries.
When used as a reference to the second (or subsequent) table in the FROM clause (with an argument that is correlated), if the array value passed to TABLE() has N entries, the output will have N * number_of_values_from_the_table_on_the_left rows. The values from the table to the left will be repeated for each array entry.

Examples

Example 1

SELECT * FROM TABLE([1,2,3]);
****
+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

SELECT * FROM TABLE(["hello", "world"]);
****
+-----------+
| table_col |
+-----------+
| hello     |
| world     |
+-----------+
2 rows in set (0.04 sec)

SELECT * FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));
****
+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

Example 2

In the FROM clause, if the table on the left has 2 rows and the array passed to TABLE() has 2 entries, the output will have 4 rows (2*2). For example,

CREATE TABLE num (x int);
INSERT INTO num VALUES (1),(2);

SELECT * FROM num, TABLE([1,2]);
****
+------+-----------+
| x    | table_col |
+------+-----------+
|    1 | 1         |
|    1 | 2         |
|    2 | 1         |
|    2 | 2         |
+------+-----------+

Example 3

The following example demonstrates the usage of TABLE() function through UDFs.

CREATE TABLE square
  (num INT PRIMARY KEY);

INSERT INTO square VALUES (1),(2),(3),(4),(5);

DELIMITER //

CREATE OR REPLACE FUNCTION to_array (x INT)
  RETURNS ARRAY(INT) AS
  DECLARE
    arr ARRAY(INT) = CREATE_ARRAY(x+1);
  BEGIN
    FOR i IN 0..x LOOP
      arr[i] = i * i;
    END LOOP;
  RETURN arr;
END //

DELIMITER ;

SELECT num, table_col AS "SQUARE"
FROM square INNER JOIN TABLE(to_array(6))
  ON table_col = num*num ORDER BY num;
****
+-----+--------+
| num | SQUARE |
+-----+--------+
|   1 |      1 |
|   2 |      4 |
|   3 |      9 |
|   4 |     16 |
|   5 |     25 |
+-----+--------+

Example 4

The TABLE function can be used to expand a field in a table.

CREATE TABLE empRole(Name varchar(20), Role JSON);
INSERT INTO empRole VALUES
  ('Adam', '["Team Lead","Security Engineer"]'),
  ('Mary','["Product Manager"]');

SELECT * FROM empRole;
****
+-------+-----------------------------------+
| Name  | Role                              |
+-------+-----------------------------------+
| Adam  | ["Team Lead","Security Engineer"] |
| Mary  | ["Product Manager"]               |
+------+------------------------------------+

SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));
****
+------+---------------------+
| Name | Title               |
+------+---------------------+
| Adam | "Team Lead"         |
| Adam | "Security Engineer" |
| Mary | "Product Manager"   |
+------+---------------------+

In this section: