Documentation Source Text

Artifact Content
Login

Artifact c3e87885c9d7cdc8a6493b1ad067511ea5f3bdd6:


<title>The generate_series Table-Valued Function</title>
<tcl>hd_keywords series {generate_series}</tcl>

<table_of_contents>

<h1>Overview</h1>

<p>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.

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

<h2>Equivalent Recursive Common Table Expression</h2>

<p>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:

<codeblock>
WITH RECURSIVE generate_series(value) AS (
  SELECT $start
  UNION ALL
  SELECT value+$step FROM generate_series
   WHERE value+$step&lt;=$end
) ...
</codeblock>

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

<h1>Usage Examples</h1>

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

<codeblock>
SELECT value FROM generate_series(5,100,5);
</codeblock>

<p>Generate the 20 random integer values:

<codeblock>
SELECT random() FROM generate_series LIMIT 20;
</codeblock>

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

<codeblock>
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));
</codeblock>