RecursiveUnion

Description

The RecursiveUnion operator is used to process UNION statements that are recursively called. Such statements are usually used in CTEs. Common syntax logic is as follows: An initial input set is used as the initial data of the recursive process, and then recursive calling is performed to obtain the output. Finally, the output of the current recursive calling is used as the input of the next recursive calling, and the final output is obtained through cyclic calling.

Typical Scenarios

Execute an SQL statement with RecursiveUnion.

Examples

Execute an SQL statement with RecursiveUnion.

-- Prepare data.
gaussdb=#DROP TABLE IF EXISTS t1;
gaussdb=#CREATE TABLE t1(c1 number, c2 number, c3 number); 
CREATE TABLE 
gaussdb=#INSERT INTO t1 VALUES(generate_series(1, 100), 2, 3); 
INSERT 0 100

-- Execution result.
gaussdb=#EXPLAIN WITH RECURSIVE t1(n) AS ( 
VALUES(1) 
UNION ALL 
SELECT n+1 FROM t1 WHERE n < 100) 
SELECT sum(n) FROM t1;
                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=3.65..3.66 rows=1 width=12)
   CTE t1
     ->  Recursive Union  (cost=0.00..2.96 rows=31 width=4)
           ->  Values Scan on "*VALUES*"  (cost=0.00..0.01 rows=1 width=4)
           ->  WorkTable Scan on t1  (cost=0.00..0.23 rows=3 width=4)
                 Filter: (n < 100)
   ->  CTE Scan on t1  (cost=0.00..0.62 rows=31 width=4)
(7 rows)

-- Drop.
gaussdb=#DROP TABLE IF EXISTS t1;

In the preceding example, the output of the RecursiveUnion operator is as follows.