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 
TRUE JOIN TABLE (parseNames(f.product_id)) x ON TRUE 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 TRUE 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 
TRUE JOIN TABLE (parseNames(f.product_id)) x ON TRUE 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)