Debian Codegraph @

www/series.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The generate_series Table-Valued Function</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The generate_series Table-Valued Function
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc2"><a href="#equivalent_recursive_common_table_expression">1.1. Equivalent Recursive Common Table Expression</a></div>
127 <div class="fancy-toc1"><a href="#usage_examples">2. Usage Examples</a></div>
128 </div>
129 </details>
130 </div>
131
132
133
134
135
136 <h1 id="overview"><span>1. </span>Overview</h1>
137
138 <p>The generate_series(START,STOP,STEP) <a href="vtab.html#tabfunc2">table-valued function</a> is a
139 <a href="loadext.html">loadable extension</a> included in the SQLite source tree, and compiled into
140 the <a href="cli.html">command-line shell</a>. The generate_series() table has one visible
141 result column named "value" holding integer values
142 and a number of rows determined by the
143 parameters START, STOP, and STEP. The first row of the table has
144 a value of START. Subsequent rows increment by STEP to a value
145 not exceeding STOP.
146
147 </p><p>The generate_series() table has additional, hidden columns
148 named "start", "stop", and "step" whose values are the effective
149 values of START, STOP and STEP as provided or defaulted.
150 It also has a rowid, accessible by its usual names.
151
152 </p><p>Omitted parameters take on default values. STEP defaults to 1.
153 STOP defaults to 4294967295. The START parameter is required
154 as of version 3.37.0 (2021-11-27) and later and an error will
155 be raised if START is omitted or has a self-referential or otherwise
156 uncomputable value. Older versions used a default of 0 for START.
157 The legacy behavior can be obtained from recent code by compiling
158 with -DZERO_ARGUMENT_GENERATE_SERIES.
159
160 </p><h2 id="equivalent_recursive_common_table_expression"><span>1.1. </span>Equivalent Recursive Common Table Expression</h2>
161
162 <p>The generate_series table can be simulated for positive step values
163 using a <a href="lang_with.html#recursivecte">recursive common table expression</a>. If the three parameters
164 are $start, $end, and $step, then the equivalent common table
165 expression is:
166
167 </p><div class="codeblock"><pre>WITH RECURSIVE generate_series(value) AS (
168 SELECT $start
169 UNION ALL
170 SELECT value+$step FROM generate_series
171 WHERE value+$step&lt;=$end
172 ) ...
173 </pre></div>
174
175 <p>The common table expression works without having to load an
176 extension. On the other hand, the extension is easier to program
177 and faster.
178
179 </p><h1 id="usage_examples"><span>2. </span>Usage Examples</h1>
180
181 <p>Generate all multiples of 5 less than or equal to 100:
182
183 </p><div class="codeblock"><pre>SELECT value FROM generate_series(5,100,5);
184 </pre></div>
185
186 <p>Generate the 20 random integer values:
187
188 </p><div class="codeblock"><pre>SELECT random() FROM generate_series(1,20);
189 </pre></div>
190
191 <p>Find the name of every customer whose account number
192 is an even multiple of 100 between 10000 and 20000.
193
194 </p><div class="codeblock"><pre>SELECT customer.name
195 FROM customer, generate_series(10000,20000,100)
196 WHERE customer.id=value;
197 /* or */
198 SELECT name FROM customer
199 WHERE id IN (SELECT value
200 FROM generate_series(10000,20000,200));
201 </pre></div>
202 <p align="center"><small><i>This page was last updated on 2023-05-01 21:49:55Z </small></i></p>
203