Fine! Of course, you can have more than one column in your recursive CTE. Do remember, though, that the anchor member and the recursive member must have the same number of columns – that's an essential condition for any query with UNION or UNION ALL. Take a look:
WITH RECURSIVE counter (
previous_value, previous_double) AS (
SELECT 1, 2 /*anchor member*/
UNION ALL
/*recursive member*/
SELECT
previous_value + 1,
2 * (previous_value + 1)
FROM counter
/*termination check*/
WHERE previous_value < 10
)
/*invocation*/
SELECT
*
FROM counter;
In the query above, we show numbers from 1 to 10 plus the respective number multiplied by two.
In the recursive member, we added 2 * (previous_value + 1) as the second column. Because the number of columns must be the same in the anchor and recursive member, we had to add the second column in the anchor member as well (which is just the number 2, that is 1 multiplied by 2).
Naturally, we also added the second column to the list of CTE columns:
WITH RECURSIVE counter ( previous_value, previous_double) AS ...
Show two columns. In the first column (column name previous_value), show numbers from one to ten. In the second column (column name previous_sum), show the total sum of all numbers so far.
In the second column, always add the previous_value + 1 to the previous_sum in the recursive member.
