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 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 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 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). 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" | +------+---------------------+