TABLE
On this page
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_
, with one row for each array entry.col -
The
TABLE()
function can be used only in theFROM
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 theTABLE()
function. -
PSQL arrays are not supported as a table data type or array literals in SingleStore 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 toTABLE()
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).
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) ASDECLAREarr ARRAY(INT) = CREATE_ARRAY(x+1);BEGINFOR i IN 0..x LOOParr[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 empRoleJOIN TABLE(JSON_TO_ARRAY(Role));
+------+---------------------+
| Name | Title |
+------+---------------------+
| Adam | "Team Lead" |
| Adam | "Security Engineer" |
| Mary | "Product Manager" |
+------+---------------------+
Last modified: June 5, 2023