The generate_series Table-Valued Function

1. Overview

The generate_series(START,END,STEP) table-valued function is a loadable extension included in the SQLite source tree, and compiled into the command-line shell. The generate_series() table has a single result column named "value" holding integer values and a number of rows determined by the parameters START, END, and STEP. The first row of the table has a value of START. Subsequent rows increase by STEP up to END.

Omitted parameters take on default values. STEP defaults to 1. END defaults to 9223372036854775807. START defaults to 0.

1.1. Equivalent Recursive Common Table Expression

The generate_series table can be simulated using a recursive common table expression. If the three parameters are $start, $end, and $step, then the equivalent common table expression is:

WITH RECURSIVE generate_series(value) AS (
  SELECT $start
  UNION ALL
  SELECT value+$step FROM generate_series
   WHERE value+$step<=$end
) ...

The common table expression works without having to load an extension. On the other hand, the extension is easier to program and faster.

2. Usage Examples

Generate all multiples of 5 less than or equal to 100:

SELECT value FROM generate_series(5,100,5);

Generate the 20 random integer values:

SELECT random() FROM generate_series LIMIT 20;

Find the name of every customer whose account number is an even multiple of 100 between 10000 and 20000.

SELECT customer.name
  FROM customer, generate_series(10000,20000,100)
 WHERE customer.id=value;
/* or */
SELECT name FROM customer
 WHERE id IN (SELECT value
                FROM generate_series(10000,20000,200));