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 SingleStore Cloud 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"   |
+------+---------------------+

Last modified: June 5, 2023

Was this article helpful?