Chaining table functions
You can create a query that invokes a table function that is correlated on a table function, which is called chaining table functions.
The results of the first invocation of the UDTF can be input to
subsequent UDTFs for more processing. For example, the following query
shows how the results from one correlation are fed to another correlation:
mydb.schema(usr1)=> SELECT t.order_id, t.prod_codes, f.product_id,
x.product_id FROM orders t JOIN TABLE(parseNames(prod_codes)) AS f ON
1=1 JOIN TABLE (parseNames(f.product_id)) x ON 1=1 ORDER BY
order_id;To identify the behavior of the chain of correlated functions,
it can be helpful to divide the query into parts and examine the results
for each part. For example, the first “part” is the query that joins
the orders table with the parseNames UDTF as follows.
For brevity, the output shows only the results for the first two order
IDs (120 and 124):
mydb.schema(usr1)=> SELECT t.order_id, t.prod_codes, f.product_id FROM orders
t JOIN TABLE(parseNames(prod_codes)) AS f ON 1=1 ORDER BY order_id;
ORDER_ID | PROD_CODES | PRODUCT_ID
----------+-----------------------+------------
120 | 28,36,80 | 28
120 | 28,36,80 | 36
120 | 28,36,80 | 80
124 | 124,6,12,121 | 124
124 | 124,6,12,121 | 6
124 | 124,6,12,121 | 12
124 | 124,6,12,121 | 121
...
(34 rows)As the output shows, the parseNames UDTF returns
a table with a row for each unique value in the prod_codes string
of values. This initial result set is fed into the next join, which
invokes the parseNames function for each unique
value in the f.product_id column, as follows:
mydb.schema(usr1)=> SELECT t.order_id, t.prod_codes, f.product_id,
x.product_id FROM orders t JOIN TABLE(parseNames(prod_codes)) AS f ON
1=1 JOIN TABLE (parseNames(f.product_id)) x ON 1=1 ORDER BY
order_id;
ORDER_ID | PROD_CODES | PRODUCT_ID | PRODUCT_ID
----------+-----------------------+------------+------------
120 | 28,36,80 | 28 | 28
120 | 28,36,80 | 36 | 36
120 | 28,36,80 | 80 | 80
124 | 124,6,12,121 | 124 | 124
124 | 124,6,12,121 | 6 | 6
124 | 124,6,12,121 | 12 | 12
124 | 124,6,12,121 | 121 | 121
...
(34 rows)