Debian Codegraph @

doc/s-sql.html

raw ◀ ▶
Source reconstructed at patch state 0 of 1 — not the installed (fully patched) source.
1 Debian patch touch this file
1 <!DOCTYPE html>
2 <html lang="en">
3 <head>
4 <!-- 2021-04-25 Sun 12:36 -->
5 <meta charset="utf-8">
6 <meta name="viewport" content="width=device-width, initial-scale=1">
7 <title>S-SQL Reference Manual</title>
8 <meta name="generator" content="Org mode">
9 <meta name="author" content="Sabra Crolleton">
10 <style type="text/css">
11 <!--/*--><![CDATA[/*><!--*/
12 .title { text-align: center;
13 margin-bottom: .2em; }
14 .subtitle { text-align: center;
15 font-size: medium;
16 font-weight: bold;
17 margin-top:0; }
18 .todo { font-family: monospace; color: red; }
19 .done { font-family: monospace; color: green; }
20 .priority { font-family: monospace; color: orange; }
21 .tag { background-color: #eee; font-family: monospace;
22 padding: 2px; font-size: 80%; font-weight: normal; }
23 .timestamp { color: #bebebe; }
24 .timestamp-kwd { color: #5f9ea0; }
25 .org-right { margin-left: auto; margin-right: 0px; text-align: right; }
26 .org-left { margin-left: 0px; margin-right: auto; text-align: left; }
27 .org-center { margin-left: auto; margin-right: auto; text-align: center; }
28 .underline { text-decoration: underline; }
29 #postamble p, #preamble p { font-size: 90%; margin: .2em; }
30 p.verse { margin-left: 3%; }
31 pre {
32 border: 1px solid #ccc;
33 box-shadow: 3px 3px 3px #eee;
34 padding: 8pt;
35 font-family: monospace;
36 overflow: auto;
37 margin: 1.2em;
38 }
39 pre.src {
40 position: relative;
41 overflow: auto;
42 padding-top: 1.2em;
43 }
44 pre.src:before {
45 display: none;
46 position: absolute;
47 background-color: white;
48 top: -10px;
49 right: 10px;
50 padding: 3px;
51 border: 1px solid black;
52 }
53 pre.src:hover:before { display: inline; margin-top: 14px;}
54 /* Languages per Org manual */
55 pre.src-asymptote:before { content: 'Asymptote'; }
56 pre.src-awk:before { content: 'Awk'; }
57 pre.src-C:before { content: 'C'; }
58 /* pre.src-C++ doesn't work in CSS */
59 pre.src-clojure:before { content: 'Clojure'; }
60 pre.src-css:before { content: 'CSS'; }
61 pre.src-D:before { content: 'D'; }
62 pre.src-ditaa:before { content: 'ditaa'; }
63 pre.src-dot:before { content: 'Graphviz'; }
64 pre.src-calc:before { content: 'Emacs Calc'; }
65 pre.src-emacs-lisp:before { content: 'Emacs Lisp'; }
66 pre.src-fortran:before { content: 'Fortran'; }
67 pre.src-gnuplot:before { content: 'gnuplot'; }
68 pre.src-haskell:before { content: 'Haskell'; }
69 pre.src-hledger:before { content: 'hledger'; }
70 pre.src-java:before { content: 'Java'; }
71 pre.src-js:before { content: 'Javascript'; }
72 pre.src-latex:before { content: 'LaTeX'; }
73 pre.src-ledger:before { content: 'Ledger'; }
74 pre.src-lisp:before { content: 'Lisp'; }
75 pre.src-lilypond:before { content: 'Lilypond'; }
76 pre.src-lua:before { content: 'Lua'; }
77 pre.src-matlab:before { content: 'MATLAB'; }
78 pre.src-mscgen:before { content: 'Mscgen'; }
79 pre.src-ocaml:before { content: 'Objective Caml'; }
80 pre.src-octave:before { content: 'Octave'; }
81 pre.src-org:before { content: 'Org mode'; }
82 pre.src-oz:before { content: 'OZ'; }
83 pre.src-plantuml:before { content: 'Plantuml'; }
84 pre.src-processing:before { content: 'Processing.js'; }
85 pre.src-python:before { content: 'Python'; }
86 pre.src-R:before { content: 'R'; }
87 pre.src-ruby:before { content: 'Ruby'; }
88 pre.src-sass:before { content: 'Sass'; }
89 pre.src-scheme:before { content: 'Scheme'; }
90 pre.src-screen:before { content: 'Gnu Screen'; }
91 pre.src-sed:before { content: 'Sed'; }
92 pre.src-sh:before { content: 'shell'; }
93 pre.src-sql:before { content: 'SQL'; }
94 pre.src-sqlite:before { content: 'SQLite'; }
95 /* additional languages in org.el's org-babel-load-languages alist */
96 pre.src-forth:before { content: 'Forth'; }
97 pre.src-io:before { content: 'IO'; }
98 pre.src-J:before { content: 'J'; }
99 pre.src-makefile:before { content: 'Makefile'; }
100 pre.src-maxima:before { content: 'Maxima'; }
101 pre.src-perl:before { content: 'Perl'; }
102 pre.src-picolisp:before { content: 'Pico Lisp'; }
103 pre.src-scala:before { content: 'Scala'; }
104 pre.src-shell:before { content: 'Shell Script'; }
105 pre.src-ebnf2ps:before { content: 'ebfn2ps'; }
106 /* additional language identifiers per "defun org-babel-execute"
107 in ob-*.el */
108 pre.src-cpp:before { content: 'C++'; }
109 pre.src-abc:before { content: 'ABC'; }
110 pre.src-coq:before { content: 'Coq'; }
111 pre.src-groovy:before { content: 'Groovy'; }
112 /* additional language identifiers from org-babel-shell-names in
113 ob-shell.el: ob-shell is the only babel language using a lambda to put
114 the execution function name together. */
115 pre.src-bash:before { content: 'bash'; }
116 pre.src-csh:before { content: 'csh'; }
117 pre.src-ash:before { content: 'ash'; }
118 pre.src-dash:before { content: 'dash'; }
119 pre.src-ksh:before { content: 'ksh'; }
120 pre.src-mksh:before { content: 'mksh'; }
121 pre.src-posh:before { content: 'posh'; }
122 /* Additional Emacs modes also supported by the LaTeX listings package */
123 pre.src-ada:before { content: 'Ada'; }
124 pre.src-asm:before { content: 'Assembler'; }
125 pre.src-caml:before { content: 'Caml'; }
126 pre.src-delphi:before { content: 'Delphi'; }
127 pre.src-html:before { content: 'HTML'; }
128 pre.src-idl:before { content: 'IDL'; }
129 pre.src-mercury:before { content: 'Mercury'; }
130 pre.src-metapost:before { content: 'MetaPost'; }
131 pre.src-modula-2:before { content: 'Modula-2'; }
132 pre.src-pascal:before { content: 'Pascal'; }
133 pre.src-ps:before { content: 'PostScript'; }
134 pre.src-prolog:before { content: 'Prolog'; }
135 pre.src-simula:before { content: 'Simula'; }
136 pre.src-tcl:before { content: 'tcl'; }
137 pre.src-tex:before { content: 'TeX'; }
138 pre.src-plain-tex:before { content: 'Plain TeX'; }
139 pre.src-verilog:before { content: 'Verilog'; }
140 pre.src-vhdl:before { content: 'VHDL'; }
141 pre.src-xml:before { content: 'XML'; }
142 pre.src-nxml:before { content: 'XML'; }
143 /* add a generic configuration mode; LaTeX export needs an additional
144 (add-to-list 'org-latex-listings-langs '(conf " ")) in .emacs */
145 pre.src-conf:before { content: 'Configuration File'; }
146
147 table { border-collapse:collapse; }
148 caption.t-above { caption-side: top; }
149 caption.t-bottom { caption-side: bottom; }
150 td, th { vertical-align:top; }
151 th.org-right { text-align: center; }
152 th.org-left { text-align: center; }
153 th.org-center { text-align: center; }
154 td.org-right { text-align: right; }
155 td.org-left { text-align: left; }
156 td.org-center { text-align: center; }
157 dt { font-weight: bold; }
158 .footpara { display: inline; }
159 .footdef { margin-bottom: 1em; }
160 .figure { padding: 1em; }
161 .figure p { text-align: center; }
162 .equation-container {
163 display: table;
164 text-align: center;
165 width: 100%;
166 }
167 .equation {
168 vertical-align: middle;
169 }
170 .equation-label {
171 display: table-cell;
172 text-align: right;
173 vertical-align: middle;
174 }
175 .inlinetask {
176 padding: 10px;
177 border: 2px solid gray;
178 margin: 10px;
179 background: #ffffcc;
180 }
181 #org-div-home-and-up
182 { text-align: right; font-size: 70%; white-space: nowrap; }
183 textarea { overflow-x: auto; }
184 .linenr { font-size: smaller }
185 .code-highlighted { background-color: #ffff00; }
186 .org-info-js_info-navigation { border-style: none; }
187 #org-info-js_console-label
188 { font-size: 10px; font-weight: bold; white-space: nowrap; }
189 .org-info-js_search-highlight
190 { background-color: #ffff00; color: #000000; font-weight: bold; }
191 .org-svg { width: 90%; }
192 /*]]>*/-->
193 </style>
194 <link rel="stylesheet" type="text/css" href="style.css" />
195 <style>pre.src{background:#343131;color:white;} </style>
196 <script type="text/javascript">
197 // @license magnet:?xt=urn:btih:e95b018ef3580986a04669f1b5879592219e2a7a&dn=public-domain.txt Public Domain
198 <!--/*--><![CDATA[/*><!--*/
199 function CodeHighlightOn(elem, id)
200 {
201 var target = document.getElementById(id);
202 if(null != target) {
203 elem.classList.add("code-highlighted");
204 target.classList.add("code-highlighted");
205 }
206 }
207 function CodeHighlightOff(elem, id)
208 {
209 var target = document.getElementById(id);
210 if(null != target) {
211 elem.classList.remove("code-highlighted");
212 target.classList.remove("code-highlighted");
213 }
214 }
215 /*]]>*///-->
216 // @license-end
217 </script>
218 <script type="text/x-mathjax-config">
219 MathJax.Hub.Config({
220 displayAlign: "center",
221 displayIndent: "0em",
222
223 "HTML-CSS": { scale: 100,
224 linebreaks: { automatic: "false" },
225 webFont: "TeX"
226 },
227 SVG: {scale: 100,
228 linebreaks: { automatic: "false" },
229 font: "TeX"},
230 NativeMML: {scale: 100},
231 TeX: { equationNumbers: {autoNumber: "AMS"},
232 MultLineWidth: "85%",
233 TagSide: "right",
234 TagIndent: ".8em"
235 }
236 });
237 </script>
238 <script type="text/javascript"
239 src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.0/MathJax.js?config=TeX-AMS_HTML"></script>
240 </head>
241 <body>
242 <div id="content">
243 <header>
244 <h1 class="title">S-SQL Reference Manual</h1>
245 </header><nav id="table-of-contents">
246 <h2>Table of Contents</h2>
247 <div id="text-table-of-contents">
248 <ul>
249 <li><a href="#interface">Interface</a>
250 <ul>
251 <li><a href="#macro-sql">macro sql (form)</a></li>
252 <li><a href="#function-sql-compile">function sql-compile (form)</a></li>
253 <li><a href="#function-sql-template">function sql-template (form)</a></li>
254 <li><a href="#function-enable-s-sql-syntax">function enable-s-sql-syntax (&amp;optional (char #\Q))</a></li>
255 <li><a href="#function-sql-escape-string">function sql-escape-string (string)</a></li>
256 <li><a href="#method-sql-escape">method sql-escape (value)</a></li>
257 <li><a href="#variable-downcase-symbols">variable <code>*downcase-symbols*</code></a></li>
258 <li><a href="#variable-standard-sql-strings">variable <code>*standard-sql-strings*</code></a></li>
259 <li><a href="#variable-postgres-reserved-words">variable <code>*postgres-reserved-words*</code> hashtable</a></li>
260 <li><a href="#variable-escape-sql-names-p">variable <code>*escape-sql-names-p*</code></a></li>
261 <li><a href="#function-sql-type-name">function sql-type-name (type)</a></li>
262 <li><a href="#function-to-sql-name">function to-sql-name (name &amp;optional (escape-p <code>*escape-sql-names-p*</code>)(ignore-reserved-words nil)</a></li>
263 <li><a href="#function-from-sql-name">function from-sql-name (string)</a></li>
264 <li><a href="#macro-register-sql-operators">macro register-sql-operators (arity &amp;rest names)</a></li>
265 </ul>
266 </li>
267 <li><a href="#sql-types">SQL Types</a>
268 <ul>
269 <li><a href="#type-db-null">type db-null</a></li>
270 </ul>
271 </li>
272 <li><a href="#sql-syntax">SQL Syntax</a>
273 <ul>
274 <li><a href="#sql-op-select">sql-op :select (&amp;rest args)</a></li>
275 <li><a href="#e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">Joins</a>
276 <ul>
277 <li><a href="#40e45849-5e9d-4b4c-830b-53f79f0b21e2">Cross Join/ Cross Join Lateral</a></li>
278 <li><a href="#85c25a7d-3660-4d38-85f0-2b9c9dc88684">Inner Join / Inner Join Lateral</a></li>
279 <li><a href="#ee0a6fef-de2f-407e-9cc9-3667de7775dc">Outer Join / Outer Join Lateral</a></li>
280 <li><a href="#3061c378-d2d1-4dda-833a-f1b3f8569018">Left Join / Left Join Lateral / Right Join / Right Join Lateral</a></li>
281 </ul>
282 </li>
283 </ul>
284 </li>
285 <li><a href="#defined-operators">Defined Operators</a>
286 <ul>
287 <li><a href="#sql-op-misc">sql-op :+, :*, :%, :&amp;, :|, :||, :and, :or, :=, :/, :!=, :&lt;, :&gt;, :&lt;=, :&gt;=, :^, :union, :union-all, :intersect, :intersect-all, :except, :except-all (&amp;rest args)</a></li>
288 <li><a href="#sql-op-or">sql-op :or</a></li>
289 <li><a href="#sql-op-intersect">sql-op :intersect</a></li>
290 <li><a href="#026a2773-c17f-4cb2-a86c-34babf8c48a2">sql-op :union, :union-all</a></li>
291 <li><a href="#4f77625f-4b6e-417d-8b56-d76835d6832d">sql-op :except, :except-all</a></li>
292 <li><a href="#sql-op-not">sql-op :~, :not (arg)</a></li>
293 <li><a href="#sql-op-any">sql-op :any, :any*</a></li>
294 <li><a href="#sql-op-function">sql-op :function (name (&amp;rest arg-types) return-type stability body)</a></li>
295 <li><a href="#sql-op-pattern">sql-op :~, :~*, :!~, :!~* (string pattern)</a></li>
296 <li><a href="#sql-op-like">sql-op :like, :ilike (string pattern)</a></li>
297 <li><a href="#sql-op-double-ampersand">sql-op :@@</a></li>
298 <li><a href="#sql-op-desc">sql-op :desc (column)</a></li>
299 <li><a href="#sql-op-nulls-first">sql-op :nulls-first, :nulls-last (column)</a></li>
300 <li><a href="#sql-op-as">sql-op :as (form name &amp;rest fields)</a></li>
301 <li><a href="#sql-op-cast">sql-op :cast (query)</a></li>
302 <li><a href="#sql-op-type-query">sql-op :type (query)</a></li>
303 <li><a href="#sql-op-type-form">sql-op :type (form type)</a></li>
304 <li><a href="#sql-op-create-composite-type">sql-op :create-composite-type (type-name &amp;rest args)</a></li>
305 <li><a href="#sql-op-exists">sql-op :exists (query)</a></li>
306 <li><a href="#sql-op-is-false">sql-op :is-false (arg)</a></li>
307 <li><a href="#sql-op-is-true">sql-op :is-true (arg)</a></li>
308 <li><a href="#sql-op-is-null">sql-op :is-null (arg)</a></li>
309 <li><a href="#sql-op-not-null">sql-op :not-null (arg)</a></li>
310 <li><a href="#sql-op-in">sql-op :in (value set)</a></li>
311 <li><a href="#sql-op-not-in">sql-op :not-in (value set)</a></li>
312 <li><a href="#sql-op-set">sql-op :set (&amp;rest elements)</a></li>
313 <li><a href="#sql-op-array">sql-op :array (query)</a></li>
314 <li><a href="#sql-op-array-rest">sql-op :array[] (&amp;rest args)</a></li>
315 <li><a href="#sql-op-square-brackets">sql-op :[] (form start &amp;optional end)</a></li>
316 <li><a href="#sql-op-extract">sql-op :extract (unit form)</a></li>
317 <li><a href="#sql-op-case">sql-op :case (&amp;rest clauses)</a></li>
318 <li><a href="#sql-op-between">sql-op :between (n start end)</a></li>
319 <li><a href="#sql-op-between-symmetric">sql-op :between-symmetric (n start end)</a></li>
320 <li><a href="#sql-op-dot">sql-op :dot (&amp;rest names)</a></li>
321 <li><a href="#sql-op-raw-string">sql-op :raw (string)</a></li>
322 <li><a href="#sql-op-fetch">sql-op :fetch (form amount &amp;optional offset)</a></li>
323 <li><a href="#sql-op-limit">sql-op :limit (query amount &amp;optional offset)</a></li>
324 <li><a href="#sql-op-order-by">sql-op :order-by (query &amp;rest exprs)</a></li>
325 <li><a href="#sql-op-values">sql-op :values</a></li>
326 <li><a href="#sql-op-empty-set">sql-op :empty-set</a></li>
327 <li><a href="#sql-op-group-by">sql-op :group-by</a></li>
328 <li><a href="#sql-op-grouping-sets">sql-op :grouping-sets</a></li>
329 </ul>
330 </li>
331 <li><a href="#sql-op-time-date-and-interval">Time, Date and Interval Operators</a>
332 <ul>
333 <li><a href="#sql-op-interval">sql-op :interval (arg)</a></li>
334 <li><a href="#sql-op-current-date">sql-op :current-date ()</a></li>
335 <li><a href="#sql-op-current-time">sql-op :current-time ()</a></li>
336 <li><a href="#sql-op-current-timestamp">sql-op :current-timestamp ()</a></li>
337 <li><a href="#sql-op-timestamp">sql-op :timestamp (arg)</a></li>
338 <li><a href="#sql-op-age">sql-op :age (&amp;rest args)</a></li>
339 <li><a href="#sql-op-date">sql-op :date (arg)</a></li>
340 <li><a href="#sql-op-make-insterval">sql-op :make-interval (&amp;rest args)</a></li>
341 <li><a href="#sql-op-make-timestamp">sql-op :make-timestamp (&amp;rest args)</a></li>
342 <li><a href="#sql-op-make-timestamptz">sql-op :make-timestamptz (&amp;rest args)</a></li>
343 </ul>
344 </li>
345 <li><a href="#sql-op-aggregation-operators">Aggregation Operators</a>
346 <ul>
347 <li><a href="#sql-op-count">sql-op :count (&amp;rest args)</a></li>
348 <li><a href="#sql-op-avg">sql-op :avg (&amp;rest rest args)</a></li>
349 <li><a href="#sql-op-sum">sql-op :sum (&amp;rest rest args)</a></li>
350 <li><a href="#sql-op-max">sql-op ::max (&amp;rest args)</a></li>
351 <li><a href="#sql-op-min">sql-op ::min (&amp;rest args)</a></li>
352 <li><a href="#sql-op-every">sql-op ::every (&amp;rest args)</a></li>
353 <li><a href="#sql-op-percentile-cont">sql-op :percentile-cont (&amp;rest args)</a></li>
354 <li><a href="#sql-op-percentile-dist">sql-op :percentile-dist (&amp;rest args)</a></li>
355 <li><a href="#sql-op-corr">sql-op :corr (y x)</a></li>
356 <li><a href="#sql-op-covar-pop">sql-op :covar-pop (y x)</a></li>
357 <li><a href="#sql-op-covar-samp">sql-op :covar-samp (y x)</a></li>
358 <li><a href="#sql-op-string-agg">sql-op :string-agg (&amp;rest args)</a></li>
359 <li><a href="#sql-op-array-agg">sql-op :array-agg (&amp;rest args)</a></li>
360 <li><a href="#sql-op-mode">sql-op :mode (&amp;rest args)</a></li>
361 <li><a href="#sql-op-regr-avgx">sql-op :regr_avgx (y x)</a></li>
362 <li><a href="#sql-op-regr-avgy">sql-op :regr_avgy (y x)</a></li>
363 <li><a href="#sql-op-regr-count">sql-op :regr_count (y x)</a></li>
364 <li><a href="#sql-op-regr-intercept">sql-op :regr_intercept (y x)</a></li>
365 <li><a href="#sql-op-regr-r2">sql-op :regr_r2 (y x)</a></li>
366 <li><a href="#sql-op-regr-slope">sql-op :regr_slope (y x)</a></li>
367 <li><a href="#sql-op-regr-sxx">sql-op :regr_sxx (y x)</a></li>
368 <li><a href="#sql-op-regr-sxy">sql-op :regr_sxy (y x)</a></li>
369 <li><a href="#sql-op-regr-syy">sql-op :regr_syy (y x)</a></li>
370 <li><a href="#sql-op-stddev">sql-op :stddev (&amp;rest args)</a></li>
371 <li><a href="#sql-op-stddev-pop">sql-op :stddev-pop (&amp;rest args)</a></li>
372 <li><a href="#sql-op-stddev-samp">sql-op :stddev-samp (&amp;rest args)</a></li>
373 <li><a href="#sql-op-variance">sql-op :variance (&amp;rest args)</a></li>
374 <li><a href="#sql-op-var-pop">sql-op :var-pop (&amp;rest args)</a></li>
375 <li><a href="#sql-op-var-samp">sql-op :var-samp (&amp;rest args)</a></li>
376 <li><a href="#org2555f16">sql-op :range-between (&amp;rest args)</a></li>
377 <li><a href="#orgfb601b4">sql-op :rows-between (&amp;rest args)</a></li>
378 <li><a href="#sql-op-over">sql-op :over (form &amp;rest args)</a></li>
379 <li><a href="#sql-op-partition-by">sql-op :partition-by (&amp;rest args)</a></li>
380 <li><a href="#sql-op-window">sql-op :window (form)</a></li>
381 <li><a href="#sql-op-with">sql-op :with (&amp;rest args)</a></li>
382 <li><a href="#sql-op-with-recursive">sql-op :with-recursive (&amp;rest args)</a></li>
383 <li><a href="#org6f9c3d3">sql-op :with-ordinality, :with-ordinality-as</a></li>
384 </ul>
385 </li>
386 <li><a href="#table-functions">Table Functions</a>
387 <ul>
388 <li><a href="#sql-op-for-update">sql-op :for-update (query &amp;key of nowait)</a></li>
389 <li><a href="#sql-op-for-share">sql-op :for-share (query &amp;key of nowait)</a></li>
390 <li><a href="#sql-op-insert-into">sql-op :insert-into (table &amp;rest rest)</a></li>
391 <li><a href="#sql-op-insert-rows-into">sql-op :insert-rows-into (table &amp;rest rest)</a></li>
392 <li><a href="#sql-op-update">sql-op :update (table &amp;rest rest)</a></li>
393 <li><a href="#sql-op-delete-from">sql-op :delete-from (table &amp;rest rest)</a></li>
394 <li><a href="#sql-op-create-table">sql-op :create-table (name (&amp;rest columns) &amp;rest options)</a>
395 <ul>
396 <li><a href="#sql-op-column-definition-parameters">Column Definition parameters</a></li>
397 <li><a href="#sql-op-table-constraints">Table Constraints</a></li>
398 </ul>
399 </li>
400 <li><a href="#sql-op-alter-table">sql-op :alter-table (name action &amp;rest args)</a></li>
401 <li><a href="#sql-op-drop-table">sql-op :drop-table (name)</a></li>
402 <li><a href="#sql-op-truncate">sql-op :truncate (&amp;rest args)</a></li>
403 <li><a href="#sql-op-create-index">sql-op :create-index (name &amp;rest args)</a></li>
404 <li><a href="#sql-op-create-unique-index">sql-op :create-unique-index (name &amp;rest args)</a></li>
405 <li><a href="#sql-op-drop-index">sql-op :drop-index (name)</a></li>
406 <li><a href="#sql-op-create-sequence">sql-op :create-sequence (name &amp;key increment min-value max-value start cache cycle)</a></li>
407 <li><a href="#sql-op-alter-sequence">sql-op :alter-sequence (name)</a></li>
408 <li><a href="#sql-op-drop-sequence">sql-op :drop-sequence (name)</a></li>
409 <li><a href="#sql-op-create-view">sql-op :create-view (name query)</a></li>
410 <li><a href="#sql-op-drop-view">sql-op :drop-view (name)</a></li>
411 <li><a href="#sql-op-set-constraints">sql-op :set-constraints (state &amp;rest constraints)</a></li>
412 <li><a href="#sql-op-listen">sql-op :listen (channel)</a></li>
413 <li><a href="#sql-op-unlisten">sql-op :unlisten (channel)</a></li>
414 <li><a href="#sql-op-notify">sql-op :notify (channel &amp;optional payload)</a></li>
415 <li><a href="#sql-op-create-role">sql-op :create-role (role &amp;rest args)</a></li>
416 <li><a href="#sql-op-create-database">sql-op :create-database (name)</a></li>
417 <li><a href="#sql-op-drop-database">sql-op :drop-database (name)</a></li>
418 <li><a href="#sql-op-copy">sql-op :copy (table &amp;rest args)</a></li>
419 </ul>
420 </li>
421 <li><a href="#dynamic-queries-composition-and-parameterized-queries">Dynamic Queries, Composition and Parameterized Queries</a>
422 <ul>
423 <li><a href="#dynamic-queries-overview">Overview</a>
424 <ul>
425 <li><a href="#programmer-built-queries">Programmer Built Queries</a></li>
426 <li><a href="#approach-use-sql-compile">Approach #2 Use sql-compile</a></li>
427 <li><a href="#raw-approach">Approach #3 Use :raw</a></li>
428 <li><a href="#queries-with-user-input">Queries with User Input</a></li>
429 </ul>
430 </li>
431 </ul>
432 </li>
433 </ul>
434 </div>
435 </nav>
436 <p>
437 This is the reference manual for the S-SQL component of the postmodern library.
438 </p>
439
440 <p>
441 S-SQL provides a lispy syntax for SQL queries, and knows how to convert various
442 lisp types to their textual SQL representation. It takes care to do as much of
443 the work as possible at compile-time, so that at runtime a string concatenation
444 is all that is needed to produce the final SQL query.
445 </p>
446
447 <div id="outline-container-interface" class="outline-2">
448 <h2 id="interface">Interface</h2>
449 <div class="outline-text-2" id="text-interface">
450 </div>
451 <div id="outline-container-macro-sql" class="outline-3">
452 <h3 id="macro-sql">macro sql (form)</h3>
453 <div class="outline-text-3" id="text-macro-sql">
454 <p>
455 → string
456 </p>
457
458 <p>
459 Convert the given form (a list starting with a keyword) to an SQL query string
460 at compile time, according to the rules described here. For example:
461 </p>
462 <div class="org-src-container">
463 <pre class="src src-lisp">(sql (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'country <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a 1)))
464
465 <span style="color: #e67128;">"(SELECT * FROM country WHERE (a = 1))"</span>
466 </pre>
467 </div>
468
469 <p>
470 but
471 </p>
472 <div class="org-src-container">
473 <pre class="src src-lisp">(sql '(<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'country <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a 1)))
474 </pre>
475 </div>
476
477 <p>
478 would throw an error. For the later case you need to use sql-compile.
479 </p>
480 </div>
481 </div>
482
483 <div id="outline-container-function-sql-compile" class="outline-3">
484 <h3 id="function-sql-compile">function sql-compile (form)</h3>
485 <div class="outline-text-3" id="text-function-sql-compile">
486 <p>
487 → string
488 </p>
489
490 <p>
491 This is the run-time variant of the sql macro. It converts the given list to
492 an SQL query, with the same rules except that symbols in this list do not
493 have to be quoted to be interpreted as identifiers. For example:
494 </p>
495 <div class="org-src-container">
496 <pre class="src src-lisp">(sql-compile '(<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'country <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a 1)))
497
498 \"(SELECT * FROM country WHERE (a = 1))\"
499 </pre>
500 </div>
501
502 <p>
503 but
504 </p>
505 <div class="org-src-container">
506 <pre class="src src-lisp">(sql (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'country <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a 1)))
507 </pre>
508 </div>
509
510 <p>
511 would throw an error. For the later case you need to use sql.
512 </p>
513 </div>
514 </div>
515
516 <div id="outline-container-function-sql-template" class="outline-3">
517 <h3 id="function-sql-template">function sql-template (form)</h3>
518 <div class="outline-text-3" id="text-function-sql-template">
519 <p>
520 In cases where you do need to build the query at run time, yet you do not
521 want to re-compile it all the time, this function can be used to compile it
522 once and store the result. It takes an S-SQL form, which may contain
523 \[ placeholder symbols, and returns a function that takes one argument for
524 every \]. When called, this returned function produces an SQL string in
525 which the placeholders have been replaced by the values of the arguments.
526 </p>
527 </div>
528 </div>
529
530 <div id="outline-container-function-enable-s-sql-syntax" class="outline-3">
531 <h3 id="function-enable-s-sql-syntax">function enable-s-sql-syntax (&amp;optional (char #\Q))</h3>
532 <div class="outline-text-3" id="text-function-enable-s-sql-syntax">
533 <p>
534 Modifies the current readtable to add a #Q syntax that is read as (sql &#x2026;).
535 The character to use can be overridden by passing an argument.
536 </p>
537 </div>
538 </div>
539 <div id="outline-container-function-sql-escape-string" class="outline-3">
540 <h3 id="function-sql-escape-string">function sql-escape-string (string)</h3>
541 <div class="outline-text-3" id="text-function-sql-escape-string">
542 <p>
543 → string
544 </p>
545
546 <p>
547 <a href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS">Escapes</a> a string for inclusion in a PostgreSQL query. A quoted symbol will generate an error.
548 Example:
549 </p>
550 <div class="org-src-container">
551 <pre class="src src-lisp">(sql-escape-string \"Puss in 'Boots'\")
552
553 \"E'Puss in ''Boots'''\"
554 </pre>
555 </div>
556 </div>
557 </div>
558
559 <div id="outline-container-method-sql-escape" class="outline-3">
560 <h3 id="method-sql-escape">method sql-escape (value)</h3>
561 <div class="outline-text-3" id="text-method-sql-escape">
562 <p>
563 → string
564 </p>
565
566 <p>
567 A generalisation of sql-escape-string looks at the type of the value passed, and properly writes it out it for inclusion in an SQL query. Symbols will be
568 converted to SQL names. Examples:
569 </p>
570 <div class="org-src-container">
571 <pre class="src src-lisp">(sql-escape <span style="color: #e67128;">"tr'-x"</span>)
572
573 <span style="color: #e67128;">"E'tr''-x'"</span>
574
575 (sql-escape (/ 1 13))
576
577 <span style="color: #e67128;">"0.0769230769230769230769230769230769230"</span>
578
579 (sql-escape #(<span style="color: #e67128;">"Baden-Wurttemberg"</span> <span style="color: #e67128;">"Bavaria"</span> <span style="color: #e67128;">"Berlin"</span> <span style="color: #e67128;">"Brandenburg"</span>))
580
581 <span style="color: #e67128;">"ARRAY[E'Baden-Wurttemberg', E'Bavaria', E'Berlin', E'Brandenburg']"</span>
582 </pre>
583 </div>
584 </div>
585 </div>
586 <div id="outline-container-variable-downcase-symbols" class="outline-3">
587 <h3 id="variable-downcase-symbols">variable <code>*downcase-symbols*</code></h3>
588 <div class="outline-text-3" id="text-variable-downcase-symbols">
589 <p>
590 When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.
591 </p>
592 </div>
593 </div>
594 <div id="outline-container-variable-standard-sql-strings" class="outline-3">
595 <h3 id="variable-standard-sql-strings">variable <code>*standard-sql-strings*</code></h3>
596 <div class="outline-text-3" id="text-variable-standard-sql-strings">
597 <p>
598 Used to configure whether S-SQL will use standard SQL strings (just replace #\' with ''), or backslash-style escaping. Setting this to NIL is always safe, but when the server is configured to allow standard strings (compile-time parameter 'standard_conforming_strings' is 'on', which will become the default in future versions of PostgreSQL), the noise in queries can be reduced by setting this to T.
599 </p>
600 </div>
601 </div>
602
603 <div id="outline-container-variable-postgres-reserved-words" class="outline-3">
604 <h3 id="variable-postgres-reserved-words">variable <code>*postgres-reserved-words*</code> hashtable</h3>
605 <div class="outline-text-3" id="text-variable-postgres-reserved-words">
606 <p>
607 A set of all Postgresql's reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway.
608 '("all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" "authorization"
609 "between" "binary" "both" "case" "cast" "check" "collate" "column" "concurrently"
610 "constraint" "create" "cross" "current-catalog" "current-date" "current-role" "current-schema"
611 "current-time" "current-timestamp" "current-user" "default" "deferrable"
612 "desc" "distinct" "do" "else" "end" "except" "false" "fetch" "filter"
613 "for" "foreign" "freeze" "from" "full" "grant" "group" "having" "ilike" "in" "initially"
614 "inner" "intersect" "into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit"
615 "localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" "off" "offset" "old"
616 "on" "only" "or" "order" "outer" "overlaps" "placing" "primary" "references" "returning"
617 "right" "select" "session-user" "Share" "similar" "some" "symmetric" "table" "then" "to" "trailing" "true"
618 "union" "unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with"))
619 </p>
620 </div>
621 </div>
622
623 <div id="outline-container-variable-escape-sql-names-p" class="outline-3">
624 <h3 id="variable-escape-sql-names-p">variable <code>*escape-sql-names-p*</code></h3>
625 <div class="outline-text-3" id="text-variable-escape-sql-names-p">
626 <p>
627 Determines whether double quotes are added around column, table, and ** function names in
628 queries. Valid values:
629 </p>
630
631 <ul class="org-ul">
632 <li>T, in which case every name is escaped,</li>
633 <li>NIL, in which case no name is escape,</li>
634 <li>:auto, which causes only <a href="http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html">reserved words</a> to be escaped, or.</li>
635 <li>:literal which is the same as :auto except it has added consequence in to-sql-name (see below).</li>
636 </ul>
637
638 <p>
639 The default value is :auto.
640 </p>
641
642 <p>
643 Be careful when binding this with let and such ― since a lot of SQL compilation tends to happen at
644 compile-time, the result might not be what you expect. Mixed case sensitivity is not currently
645 well supported. Postgresql itself will downcase unquoted identifiers. This will be revisited in the future if requested.
646 </p>
647 </div>
648 </div>
649
650 <div id="outline-container-function-sql-type-name" class="outline-3">
651 <h3 id="function-sql-type-name">function sql-type-name (type)</h3>
652 <div class="outline-text-3" id="text-function-sql-type-name">
653 <p>
654 → string
655 </p>
656
657 <p>
658 Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol's name.
659 </p>
660 </div>
661 </div>
662
663 <div id="outline-container-function-to-sql-name" class="outline-3">
664 <h3 id="function-to-sql-name">function to-sql-name (name &amp;optional (escape-p <code>*escape-sql-names-p*</code>)(ignore-reserved-words nil)</h3>
665 <div class="outline-text-3" id="text-function-to-sql-name">
666 <p>
667 → string
668 </p>
669
670 <p>
671 Convert a symbol or string into a name that can be a sql table, column, or operation name. Add quotes when escape-p is true, or escape-p is :auto and the name contains reserved words. Quoted or delimited identifiers can be used by passing :literal as the value of escape-p. If escape-p is :literal, and the name is a string then the string is still escaped but the symbol or string is not downcased, regardless of the setting for <code>*downcase-symbols*</code> and the hyphen and forward slash characters are not replaced with underscores.
672 </p>
673
674 <p>
675 Ignore-reserved-words is only used internally for column names which are allowed to be reserved words, but it is not recommended.
676 </p>
677 </div>
678 </div>
679
680 <div id="outline-container-function-from-sql-name" class="outline-3">
681 <h3 id="function-from-sql-name">function from-sql-name (string)</h3>
682 <div class="outline-text-3" id="text-function-from-sql-name">
683 <p>
684 → keyword
685 </p>
686
687 <p>
688 Convert a string that represents an SQL identifier to a keyword by uppercasing
689 it and converting the underscores to dashes.
690 </p>
691 </div>
692 </div>
693
694 <div id="outline-container-macro-register-sql-operators" class="outline-3">
695 <h3 id="macro-register-sql-operators">macro register-sql-operators (arity &amp;rest names)</h3>
696 <div class="outline-text-3" id="text-macro-register-sql-operators">
697 <p>
698 Define simple SQL operators. Arity is one of :unary (like 'not'), :unary-postfix
699 (the operator comes after the operand), :n-ary (like '\+': the operator falls away
700 when there is only one operand), :2+-ary (like '=', which is meaningless for one
701 operand), or :n-or-unary (like '-', where the operator is kept in the unary case).
702 After the arity may follow any number of operators, either just a keyword, in
703 which case the downcased symbol name is used as the SQL operator, or a two-element
704 list containing a keyword and a name string.
705 </p>
706 </div>
707 </div>
708 </div>
709 <div id="outline-container-sql-types" class="outline-2">
710 <h2 id="sql-types">SQL Types</h2>
711 <div class="outline-text-2" id="text-sql-types">
712 <p>
713 S-SQL knows the SQL equivalents to a number of Lisp types, and defines some
714 extra types that can be used to denote other SQL types. The following
715 table (yes, I know this table is duplicated on other pages) shows the correspondence:
716 </p>
717
718 <table>
719
720
721 <colgroup>
722 <col class="org-left">
723
724 <col class="org-left">
725
726 <col class="org-left">
727 </colgroup>
728 <thead>
729 <tr>
730 <th scope="col" class="org-left">Lisp type</th>
731 <th scope="col" class="org-left">SQL type</th>
732 <th scope="col" class="org-left">Description</th>
733 </tr>
734 </thead>
735 <tbody>
736 <tr>
737 <td class="org-left">integer</td>
738 <td class="org-left">smallint</td>
739 <td class="org-left">-32,768 to +32,768 2-byte storage</td>
740 </tr>
741
742 <tr>
743 <td class="org-left">integer</td>
744 <td class="org-left">integer</td>
745 <td class="org-left">-2147483648 to +2147483647 integer, 4-byte storage</td>
746 </tr>
747
748 <tr>
749 <td class="org-left">integer</td>
750 <td class="org-left">bigint</td>
751 <td class="org-left">-9223372036854775808 to 9223372036854775807 8-byte storage</td>
752 </tr>
753
754 <tr>
755 <td class="org-left">(numeric X Y)</td>
756 <td class="org-left">numeric(X, Y)</td>
757 <td class="org-left">see discussion below</td>
758 </tr>
759
760 <tr>
761 <td class="org-left">float, real</td>
762 <td class="org-left">real</td>
763 <td class="org-left">float, 6 decimal digit precision 4-byte storage</td>
764 </tr>
765
766 <tr>
767 <td class="org-left">double-float</td>
768 <td class="org-left">double-precision</td>
769 <td class="org-left">float, 15 decimal digit precision 8-byte storage</td>
770 </tr>
771
772 <tr>
773 <td class="org-left">string, text</td>
774 <td class="org-left">text</td>
775 <td class="org-left">variable length string, no limit specified</td>
776 </tr>
777
778 <tr>
779 <td class="org-left">string</td>
780 <td class="org-left">char(X)</td>
781 <td class="org-left">char(length), blank-padded string, fixed storage length</td>
782 </tr>
783
784 <tr>
785 <td class="org-left">string</td>
786 <td class="org-left">varchar(X)</td>
787 <td class="org-left">varchar(length), non-blank-padded string, variable storage</td>
788 </tr>
789
790 <tr>
791 <td class="org-left">boolean</td>
792 <td class="org-left">boolean</td>
793 <td class="org-left">boolean, 'true'/'false', 1 byte</td>
794 </tr>
795
796 <tr>
797 <td class="org-left">bytea</td>
798 <td class="org-left">bytea</td>
799 <td class="org-left">binary string which allows non-printable octets</td>
800 </tr>
801
802 <tr>
803 <td class="org-left">date</td>
804 <td class="org-left">date</td>
805 <td class="org-left">date range: 4713 BC to 5874897 AD</td>
806 </tr>
807
808 <tr>
809 <td class="org-left"><a href="interval-notes.html">interval</a></td>
810 <td class="org-left">interval</td>
811 <td class="org-left">time intervals</td>
812 </tr>
813
814 <tr>
815 <td class="org-left">array</td>
816 <td class="org-left">array</td>
817 <td class="org-left">See <a href="array-notes.html">Array-Notes</a></td>
818 </tr>
819 </tbody>
820 </table>
821
822 <p>
823 Numeric and decimal are variable storage size numbers with user specified precision.
824 Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
825 The syntax is numeric(precision, scale). Numeric columns with a specified scale will coerce input
826 values to that scale. For more detail, see <a href="https://www.postgresql.org/docs/current/datatype-numeric.html">https://www.postgresql.org/docs/current/datatype-numeric.html</a>
827 </p>
828 </div>
829
830 <div id="outline-container-type-db-null" class="outline-3">
831 <h3 id="type-db-null">type db-null</h3>
832 <div class="outline-text-3" id="text-type-db-null">
833 <p>
834 This is a type of which only the keyword :null is a member. It is used to represent
835 NULL values from the database.
836 </p>
837 </div>
838 </div>
839 </div>
840
841 <div id="outline-container-sql-syntax" class="outline-2">
842 <h2 id="sql-syntax">SQL Syntax</h2>
843 <div class="outline-text-2" id="text-sql-syntax">
844 <p>
845 An S-SQL form is converted to a query through the following rules:
846 </p>
847
848 <ul class="org-ul">
849 <li>Lists starting with a keyword are operators. They are expanded as
850 described below if they are known, otherwise they are expanded in the
851 standard way: operator(arguments, &#x2026;)</li>
852
853 <li>Quoted symbols or keywords are interpreted as names of columns or
854 tables, and converted to strings with to-sql-name.</li>
855
856 <li>Anything else is evaluated and the resulting Lisp value is converted
857 to its textual SQL representation (or an error is raised when there is
858 no rule for converting objects of this type). Self-quoting atoms may
859 be converted to strings at compile-time.</li>
860 </ul>
861 </div>
862
863 <div id="outline-container-sql-op-select" class="outline-3">
864 <h3 id="sql-op-select">sql-op :select (&amp;rest args)</h3>
865 <div class="outline-text-3" id="text-sql-op-select">
866 <p>
867 Creates a select query. The arguments are split on the keywords found among
868 them. The group of arguments immediately after :select is interpreted as
869 the expressions that should be selected. After this, an optional :distinct
870 may follow, which will cause the query to only select distinct rows, or
871 alternatively :distinct-on followed by a group of row names. Next comes the
872 optional keyword :from, followed by at least one table name and then any
873 number of join statements.
874 </p>
875
876 <p>
877 Join statements start with one of :join, :left-join,
878 :right-join, :inner-join, :outer-join, :cross-join (or those with -lateral,
879 e.g :join-lateral, :left-join-lateral, :right-join-lateral, :inner-join-lateral, :outer-join-lateral).
880 S-sql will accept :join, but best usage is to explicitly use :inner-join instead.
881 </p>
882
883 <p>
884 Then comes a table name or subquery,
885 </p>
886
887 <p>
888 Then there is an optional :with-ordinality or :with-ordinality-as alisa
889 </p>
890
891 <p>
892 Then the keyword :on or :using, if applicable, and then a form.
893 A join can be preceded by :natural (leaving off the :on clause) to use a
894 natural join.
895 </p>
896
897 <p>
898 After the joins an optional :where followed by a single form may occur.
899 </p>
900
901 <p>
902 Finally :group-by and :having can optionally be specified.
903 The first takes any number of arguments, and the second only one.
904 </p>
905
906 <p>
907 A few examples:
908 </p>
909 <div class="org-src-container">
910 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'item <span style="color: #23d7d7;">:distinct</span>
911 <span style="color: #23d7d7;">:from</span> 'item-table
912 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'col1 <span style="color: #e67128;">"Albania"</span>)))
913
914 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'field-1 100) 'field-5
915 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'my-table 'x)
916 <span style="color: #23d7d7;">:left-join</span> 'your-table
917 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'x.field-2 'your-table.field-1)
918 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not-null</span> 'a.field-3)))
919
920 (query (<span style="color: #23d7d7;">:order-by</span>
921 (<span style="color: #23d7d7;">:select</span> 'regions.name
922 (<span style="color: #23d7d7;">:count</span> 'regions.name)
923 <span style="color: #23d7d7;">:from</span> 'countries 'regions
924 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id)
925 <span style="color: #23d7d7;">:group-by</span> 'regions.name)
926 'regions.name))
927
928 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> 'c.id) 'r.name
929 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'countries 'c)
930 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'regions 'r)
931 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'c.region-id 'r.id)
932 <span style="color: #23d7d7;">:group-by</span> 'r.name
933 <span style="color: #23d7d7;">:having</span> (<span style="color: #23d7d7;">:&lt;</span> (<span style="color: #23d7d7;">:count</span> 'c.id) 10)))
934
935 (query (<span style="color: #23d7d7;">:select</span> 'i.* 'p.*
936 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'individual 'i)
937 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'publisher 'p)
938 <span style="color: #23d7d7;">:using</span> ('individualid)
939 <span style="color: #23d7d7;">:left-join-lateral</span> (<span style="color: #23d7d7;">:as</span> 'anothertable 'a)
940 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'a.identifier 'i.individualid)
941 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a.something \"something\")))
942
943 (query (<span style="color: #23d7d7;">:select</span> 't1.id 'a.elem 'a.nr
944 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 't12 't1)
945 <span style="color: #23d7d7;">:left-join</span> (<span style="color: #23d7d7;">:unnest</span> (<span style="color: #23d7d7;">:string-to-array</span> 't1.elements <span style="color: #e67128;">","</span>))
946 <span style="color: #23d7d7;">:with-ordinality-as</span> (<span style="color: #23d7d7;">:a</span> 'elem 'nr)
947 <span style="color: #23d7d7;">:on</span> 't))
948 </pre>
949 </div>
950 <p>
951 Other examples can be found in s-sql/tests/tests.lisp
952 </p>
953 </div>
954 </div>
955 <div id="outline-container-e0f01ac7-cb3c-4b38-8902-dc4a981a15e8" class="outline-3">
956 <h3 id="e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">Joins</h3>
957 <div class="outline-text-3" id="text-e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">
958 <p>
959 Allowable join keywords are:
960 </p>
961 <ul class="org-ul">
962 <li>:left-join</li>
963 <li>:right-join</li>
964 <li>:inner-join</li>
965 <li>:outer-join</li>
966 <li>:cross-join</li>
967 <li>:join-lateral</li>
968 <li>:left-join-lateral (left join with an additional sql keyword LATERAL)</li>
969 <li>:right-join-lateral (right join with an additional sql keyword LATERAL)</li>
970 <li>:inner-join-lateral (inner join with an additional sql keyword LATERAL)</li>
971 <li>:outer-join-lateral (outer join with an additional sql keyword LATERAL)</li>
972 <li>:cross-join-lateral (cross join with an additional sql keyword LATERAL)</li>
973 </ul>
974
975 <p>
976 The lateral joins will not be discussed separately.
977 </p>
978 </div>
979 <div id="outline-container-40e45849-5e9d-4b4c-830b-53f79f0b21e2" class="outline-4">
980 <h4 id="40e45849-5e9d-4b4c-830b-53f79f0b21e2">Cross Join/ Cross Join Lateral</h4>
981 <div class="outline-text-4" id="text-40e45849-5e9d-4b4c-830b-53f79f0b21e2">
982 <p>
983 From the postgresql documentation: "For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows."
984 </p>
985 <div class="org-src-container">
986 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '* from 'employee <span style="color: #23d7d7;">:cross-join</span> 'compensation))
987 </pre>
988 </div>
989 </div>
990 </div>
991
992 <div id="outline-container-85c25a7d-3660-4d38-85f0-2b9c9dc88684" class="outline-4">
993 <h4 id="85c25a7d-3660-4d38-85f0-2b9c9dc88684">Inner Join / Inner Join Lateral</h4>
994 <div class="outline-text-4" id="text-85c25a7d-3660-4d38-85f0-2b9c9dc88684">
995 <p>
996 An inner join looks at two tables and creates a new result consisting of the selected elements in the rows from the two tables that match the specified conditions. You can simplistically think of it as the intersection of the two sets. In reality, it is creating a new set consisting of certain elements of the intersecting rows. An inner join is the default and need not be specified.
997 </p>
998
999 <p>
1000 A sample of standard sql using two inner joins to collect information from three
1001 tables could look like this:
1002 </p>
1003 <div class="org-src-container">
1004 <pre class="src src-sql">(<span style="color: #ffad29; font-weight: bold;">SELECT</span> foo, bar, baz
1005 <span style="color: #ffad29; font-weight: bold;">FROM</span> (<span style="color: #ffad29; font-weight: bold;">SELECT</span> foo <span style="color: #ffad29; font-weight: bold;">FROM</span> x <span style="color: #ffad29; font-weight: bold;">WHERE</span> <span style="color: #ffad29; font-weight: bold;">some</span>-condition-here) <span style="color: #ffad29; font-weight: bold;">AS</span> tmp1
1006 <span style="color: #ffad29; font-weight: bold;">INNER</span> <span style="color: #ffad29; font-weight: bold;">JOIN</span> (<span style="color: #ffad29; font-weight: bold;">SELECT</span> bar <span style="color: #ffad29; font-weight: bold;">FROM</span> x <span style="color: #ffad29; font-weight: bold;">WHERE</span> <span style="color: #ffad29; font-weight: bold;">some</span>-condition-here) <span style="color: #ffad29; font-weight: bold;">AS</span> tmp2
1007 <span style="color: #ffad29; font-weight: bold;">ON</span> (tmp1.id = tmp2.id)
1008 <span style="color: #ffad29; font-weight: bold;">INNER</span> <span style="color: #ffad29; font-weight: bold;">JOIN</span> (<span style="color: #ffad29; font-weight: bold;">SELECT</span> baz <span style="color: #ffad29; font-weight: bold;">FROM</span> x <span style="color: #ffad29; font-weight: bold;">WHERE</span> <span style="color: #ffad29; font-weight: bold;">some</span>-condition-here) <span style="color: #ffad29; font-weight: bold;">AS</span> tmp3
1009 <span style="color: #ffad29; font-weight: bold;">ON</span> (tmp2.id = tmp3.id))
1010 </pre>
1011 </div>
1012
1013 <p>
1014 The same query could be expressed in s-sql as:
1015 </p>
1016 <div class="org-src-container">
1017 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'foo 'bar 'baz
1018 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span>
1019 (<span style="color: #23d7d7;">:select</span> 'foo
1020 <span style="color: #23d7d7;">:from</span> 'x
1021 <span style="color: #23d7d7;">:where</span> 'x) <span style="color: #ff4242; font-weight: bold;">'tmp1)</span>
1022 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span>
1023 (<span style="color: #23d7d7;">:select</span> 'bar
1024 <span style="color: #23d7d7;">:from</span> 'x
1025 <span style="color: #23d7d7;">:where</span> 'x) <span style="color: #ff4242; font-weight: bold;">'tmp2)</span>
1026 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.id 'tmp2.id)
1027 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span>
1028 (<span style="color: #23d7d7;">:select</span> 'baz
1029 <span style="color: #23d7d7;">:from</span> 'x
1030 <span style="color: #23d7d7;">:where</span> 'x)
1031 'tmp3)
1032 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp2.id 'tmp3.id)))
1033 </pre>
1034 </div>
1035
1036 <p>
1037 The pre-ansi shorthand example, using a countries and regions tables would look like this:
1038 </p>
1039 <div class="org-src-container">
1040 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
1041 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1042 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'countries.region-id 'regions.id)
1043 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>))))
1044 </pre>
1045 </div>
1046 <p>
1047 The full portable ansi version, using inner join would look like this.
1048 </p>
1049 <div class="org-src-container">
1050 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'tmp1.name <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'name 'region-id
1051 <span style="color: #23d7d7;">:from</span> 'countries)
1052 'tmp1)
1053 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'id
1054 <span style="color: #23d7d7;">:from</span> 'regions
1055 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'name <span style="color: #e67128;">"North America"</span>))
1056 'tmp2)
1057 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.region-id 'tmp2.id)))
1058 </pre>
1059 </div>
1060 </div>
1061 </div>
1062
1063 <div id="outline-container-ee0a6fef-de2f-407e-9cc9-3667de7775dc" class="outline-4">
1064 <h4 id="ee0a6fef-de2f-407e-9cc9-3667de7775dc">Outer Join / Outer Join Lateral</h4>
1065 <div class="outline-text-4" id="text-ee0a6fef-de2f-407e-9cc9-3667de7775dc">
1066 <p>
1067 An outer join not only generates an inner join, it also joins the rows from one table that matches the conditions and adds null values for the joined columns from the second table (which obviously did not match the condition.) Under Postgresql, a "left join", "right join" or "full join" all imply an outer join.
1068 </p>
1069
1070 <p>
1071 A left join (or left outer join) looks at two tables, keeps the matched rows from both and the unmatched rows from the left table and drops the unmatched rows from the right table. A right outer join keeps the matched rows, the unmatched rows from the right table and drops the unmatched rows from the left table. A full outer join includes the rows that match from each table individually, with null values for the missing matching columns.
1072 </p>
1073 </div>
1074 </div>
1075
1076 <div id="outline-container-3061c378-d2d1-4dda-833a-f1b3f8569018" class="outline-4">
1077 <h4 id="3061c378-d2d1-4dda-833a-f1b3f8569018">Left Join / Left Join Lateral / Right Join / Right Join Lateral</h4>
1078 <div class="outline-text-4" id="text-3061c378-d2d1-4dda-833a-f1b3f8569018">
1079 <p>
1080 Example: Here we assume two tables. A countries table and a many-to-many linking table named countries-topics. (There is an implicit third table named topics.) We are looking for records from the countries table which do not have a match in the countries-topics table. In other words, where do we have a note, but not matched it to a topic?
1081 </p>
1082 <div class="org-src-container">
1083 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
1084 <span style="color: #23d7d7;">:distinct</span> <span style="color: #23d7d7;">:from</span> 'countries
1085 <span style="color: #23d7d7;">:left-join</span> 'countries-topics
1086 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'countries.id 'countries-topics.country-id)
1087 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:is-null</span> 'countries-topics.country-id))
1088 'countries.id))
1089 </pre>
1090 </div>
1091
1092 <p>
1093 Here is a somewhat contrived example using a countries and regions table. We want to get the names of all the regions and also return the country names in one specified region. Assume that we only want the names of the countries in Central America, which happens to have a region-id of 3.
1094 </p>
1095 <div class="org-src-container">
1096 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'tmp2.name 'tmp1.name
1097 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'id 'name
1098 <span style="color: #23d7d7;">:from</span> 'regions)
1099 'tmp2)
1100 <span style="color: #23d7d7;">:left-join</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'name 'region-id
1101 <span style="color: #23d7d7;">:from</span> 'countries
1102 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'region-id 3))
1103 'tmp1)
1104 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.region-id 'tmp2.id)))
1105 </pre>
1106 </div>
1107 </div>
1108 </div>
1109 </div>
1110 </div>
1111
1112 <div id="outline-container-defined-operators" class="outline-2">
1113 <h2 id="defined-operators">Defined Operators</h2>
1114 <div class="outline-text-2" id="text-defined-operators">
1115 <p>
1116 The following operators are defined:
1117 </p>
1118 </div>
1119
1120 <div id="outline-container-sql-op-misc" class="outline-3">
1121 <h3 id="sql-op-misc">sql-op :+, :*, :%, :&amp;, :|, :||, :and, :or, :=, :/, :!=, :&lt;, :&gt;, :&lt;=, :&gt;=, :^, :union, :union-all, :intersect, :intersect-all, :except, :except-all (&amp;rest args)</h3>
1122 <div class="outline-text-3" id="text-sql-op-misc">
1123 <p>
1124 These are expanded as infix operators. When meaningful, they allow more than
1125 two arguments. :- can also be used as a unary operator to negate a value.
1126 Note that the arguments to :union, :union-all, :intersect, and :except
1127 should be queries (:select forms).
1128 </p>
1129
1130 <p>
1131 Note that you'll have to escape pipe characters to enter them as keywords. S-SQL
1132 handles the empty keyword symbol (written :||) specially, and treats it like :\|\|,
1133 so that it can be written without escapes. With :\|, this doesn't work.
1134 </p>
1135 </div>
1136 </div>
1137 <div id="outline-container-sql-op-or" class="outline-3">
1138 <h3 id="sql-op-or">sql-op :or</h3>
1139 <div class="outline-text-3" id="text-sql-op-or">
1140 <div class="org-src-container">
1141 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
1142 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1143 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
1144 (<span style="color: #23d7d7;">:or</span> (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
1145 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Central America"</span>))
1146 (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id))))
1147 </pre>
1148 </div>
1149 <p>
1150 or using parameterized queries
1151 </p>
1152 <div class="org-src-container">
1153 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
1154 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1155 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
1156 (<span style="color: #23d7d7;">:or</span> (<span style="color: #23d7d7;">:=</span> 'regions.name '$1)
1157 (<span style="color: #23d7d7;">:=</span> 'regions.name '$2))
1158 (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id)))
1159 <span style="color: #e67128;">"North America"</span> <span style="color: #e67128;">"Central America"</span>)
1160 </pre>
1161 </div>
1162 </div>
1163 </div>
1164 <div id="outline-container-sql-op-intersect" class="outline-3">
1165 <h3 id="sql-op-intersect">sql-op :intersect</h3>
1166 <div class="outline-text-3" id="text-sql-op-intersect">
1167 <p>
1168 Intersect produces a result contain rows that appear on all the sub-selects.
1169 </p>
1170 <div class="org-src-container">
1171 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:intersect</span> (<span style="color: #23d7d7;">:select</span> 'countries.name
1172 <span style="color: #23d7d7;">:from</span> 'countries
1173 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'latitude 16.44))
1174 (<span style="color: #23d7d7;">:select</span> 'countries.name
1175 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1176 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'region-id 'regions.id)
1177 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Caribbean"</span>)))))
1178 </pre>
1179 </div>
1180 </div>
1181 </div>
1182 <div id="outline-container-026a2773-c17f-4cb2-a86c-34babf8c48a2" class="outline-3">
1183 <h3 id="026a2773-c17f-4cb2-a86c-34babf8c48a2">sql-op :union, :union-all</h3>
1184 <div class="outline-text-3" id="text-026a2773-c17f-4cb2-a86c-34babf8c48a2">
1185 <p>
1186 The union operation generally eliminates what it thinks are duplicate rows. The union-all operation preserves duplicate rows. The examples below use the union-all operator, but the syntax would be the same with union.
1187 </p>
1188 <div class="org-src-container">
1189 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
1190 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:union-all</span>
1191 (<span style="color: #23d7d7;">:select</span> 'id 'name <span style="color: #23d7d7;">:from</span> 'countries
1192 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;=</span> 'name <span style="color: #e67128;">"B"</span> ))
1193 (<span style="color: #23d7d7;">:select</span> 'id 'name <span style="color: #23d7d7;">:from</span> 'countries
1194 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&gt;=</span> 'name <span style="color: #e67128;">"V"</span> )))
1195 'a)))
1196
1197 (query (<span style="color: #23d7d7;">:select</span> 'a.id 'a.name 'a.region
1198 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:union-all</span>
1199 (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
1200 (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
1201 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1202 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
1203 (<span style="color: #23d7d7;">:&lt;=</span> 'countries.name <span style="color: #e67128;">"B"</span> )
1204 (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id )))
1205 (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
1206 (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
1207 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1208 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
1209 (<span style="color: #23d7d7;">:&gt;=</span> 'countries.name <span style="color: #e67128;">"V"</span> )
1210 (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id ))))
1211 'a)
1212 <span style="color: #23d7d7;">:group-by</span> 'a.id 'a.region 'a.name))
1213 </pre>
1214 </div>
1215 </div>
1216 </div>
1217
1218 <div id="outline-container-4f77625f-4b6e-417d-8b56-d76835d6832d" class="outline-3">
1219 <h3 id="4f77625f-4b6e-417d-8b56-d76835d6832d">sql-op :except, :except-all</h3>
1220 <div class="outline-text-3" id="text-4f77625f-4b6e-417d-8b56-d76835d6832d">
1221 <p>
1222 :except removes all matches. :except-all is slightly different.
1223 If the first select statement has two rows that match a single row in the second
1224 select statement, only one is removed.
1225 </p>
1226 <div class="org-src-container">
1227 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:except</span> (<span style="color: #23d7d7;">:select</span> 'id 'name
1228 <span style="color: #23d7d7;">:from</span> 'countries
1229 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%New%"</span>))
1230 (<span style="color: #23d7d7;">:select</span> 'id 'name
1231 <span style="color: #23d7d7;">:from</span> 'countries
1232 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%Zealand%"</span>))))
1233
1234 (query (<span style="color: #23d7d7;">:except-all</span> (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'clients) (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'vips)))
1235
1236 </pre>
1237 </div>
1238 </div>
1239 </div>
1240
1241 <div id="outline-container-sql-op-not" class="outline-3">
1242 <h3 id="sql-op-not">sql-op :~, :not (arg)</h3>
1243 <div class="outline-text-3" id="text-sql-op-not">
1244 <p>
1245 Unary operators for bitwise and logical negation.
1246 </p>
1247 <div class="org-src-container">
1248 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'recommendedby
1249 (<span style="color: #23d7d7;">:count</span> '*)
1250 <span style="color: #23d7d7;">:from</span> 'cd.members
1251 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not</span> (<span style="color: #23d7d7;">:is-null</span> 'recommendedby))
1252 <span style="color: #23d7d7;">:group-by</span> 'recommendedby)
1253 'recommendedby))
1254 </pre>
1255 </div>
1256 </div>
1257 </div>
1258 <div id="outline-container-sql-op-any" class="outline-3">
1259 <h3 id="sql-op-any">sql-op :any, :any*</h3>
1260 <div class="outline-text-3" id="text-sql-op-any">
1261 <p>
1262 Any needs to be considered as a special case. Quoting Marijn Haverbeke here,"Postgres has both a function-call-style any and an infix any, and S-SQL's syntax doesn't allow them to be distinguished." As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.
1263 </p>
1264
1265 <p>
1266 In general, the any qualifier in an sql statement looks at a subquery and does a comparison against that subquery. Sticking with our countries table, we have latitude, longitude data for every country (I'm not sure whether my table pulled the capital cities or the geographic center) and some designated a region for each country, so we have a region-id that matches the primary key 'id' in a regions table.
1267 </p>
1268
1269 <p>
1270 Out of curiosity, let's determine which countries in "North America" have a longitude less than any country in "South America". The standard sql could look like this:
1271 </p>
1272 <div class="org-src-container">
1273 <pre class="src src-lisp">(query <span style="color: #e67128;">"select countries.name</span>
1274 <span style="color: #e67128;"> from countries,regions</span>
1275 <span style="color: #e67128;"> where regions.id=region_id</span>
1276 <span style="color: #e67128;"> and regions.name='North America'</span>
1277 <span style="color: #e67128;"> and longitude &gt; any(select longitude</span>
1278 <span style="color: #e67128;"> from countries, regions</span>
1279 <span style="color: #e67128;"> where region_id = regions.id</span>
1280 <span style="color: #e67128;"> and regions.name='South America')"</span>)
1281 ((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
1282 </pre>
1283 </div>
1284
1285 <p>
1286 This can be re-phrased in s-sql as
1287 </p>
1288 <div class="org-src-container">
1289 <pre class="src src-lisp">(query
1290 (<span style="color: #23d7d7;">:select</span> 'countries.name
1291 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1292 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
1293 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
1294 (<span style="color: #23d7d7;">:&gt;</span> 'longitude
1295 (<span style="color: #23d7d7;">:any</span>
1296 (<span style="color: #23d7d7;">:select</span> 'longitude
1297 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1298 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
1299 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"South America"</span>))))))))
1300 ((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
1301 </pre>
1302 </div>
1303
1304 <p>
1305 Subselects work fine in both regular sql and s-sql. If you have already calculated your subselect and put it in a variable, that variable needs to be a vector and whether you should use the :any sql-op or the :any* sql-op depends on your phrasing. (Notice that the second variation has an asterisk). (If you try to use a list, you will trigger an error message that you cannot convert that into an sql literal.)
1306 </p>
1307
1308 <p>
1309 The SQL keyword ANY can be used in a parameterized sql statement if you provide it with a vector. The following two toy examples work in raw sql.
1310 </p>
1311 <div class="org-src-container">
1312 <pre class="src src-lisp">(query <span style="color: #e67128;">"select name from countries where id=any($1)"</span>
1313 (vector 21 22))
1314 ((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))
1315
1316 (<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
1317 (query <span style="color: #e67128;">"select name from countries where id=any($1)"</span>
1318 toy-query))
1319 ((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))
1320
1321 </pre>
1322 </div>
1323
1324 <p>
1325 Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.
1326 </p>
1327 <div class="org-src-container">
1328 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
1329 (query (<span style="color: #23d7d7;">:select</span> 'name
1330 <span style="color: #23d7d7;">:from</span> 'countries
1331 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any</span> '$1)))
1332 toy-query))
1333 <span style="color: #74af68;">; </span><span style="color: #74af68;">Evaluation aborted on #&lt;CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}&gt;.</span>
1334
1335 (<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
1336 (query (<span style="color: #23d7d7;">:select</span> 'name
1337 <span style="color: #23d7d7;">:from</span> 'countries
1338 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any*</span> '$1)))
1339 toy-query))
1340 ((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))
1341 </pre>
1342 </div>
1343 <p>
1344 Going back to our earlier example, remember that I said that unless you use a subselect, you need to provide a vector to :any or :any*. A standard query returns a list, not a vector. So you would need to coerce the variable into a vector before you pass it to :any*. See below as an example.
1345 </p>
1346 <div class="org-src-container">
1347 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((South-America
1348 (coerce
1349 (query (<span style="color: #23d7d7;">:select</span> 'longitude
1350 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1351 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
1352 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"South America"</span>)))
1353 <span style="color: #23d7d7;">:column</span>))
1354 'vector))
1355 (query (<span style="color: #23d7d7;">:select</span> 'countries.name
1356 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1357 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
1358 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
1359 (<span style="color: #23d7d7;">:&gt;</span> 'longitude
1360 (<span style="color: #23d7d7;">:any*</span> South-America))))))
1361 ((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
1362 </pre>
1363 </div>
1364 </div>
1365 </div>
1366
1367 <div id="outline-container-sql-op-function" class="outline-3">
1368 <h3 id="sql-op-function">sql-op :function (name (&amp;rest arg-types) return-type stability body)</h3>
1369 <div class="outline-text-3" id="text-sql-op-function">
1370 <p>
1371 Create a stored procedure. The argument and return types are interpreted as
1372 type names and not evaluated. Stability should be one of :immutable, :stable,
1373 or :volatile (see the PostgreSQL documentation). For example, a function that
1374 gets foobars by id:
1375 </p>
1376 <div class="org-src-container">
1377 <pre class="src src-lisp">(<span style="color: #23d7d7;">:function</span> 'get-foobar (integer) foobar <span style="color: #23d7d7;">:stable</span> (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'foobar <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id '$1)))
1378 </pre>
1379 </div>
1380 </div>
1381 </div>
1382
1383 <div id="outline-container-sql-op-pattern" class="outline-3">
1384 <h3 id="sql-op-pattern">sql-op :~, :~*, :!~, :!~* (string pattern)</h3>
1385 <div class="outline-text-3" id="text-sql-op-pattern">
1386 <p>
1387 Regular expression matching operators. The exclamation mark means 'does not match',
1388 the asterisk makes the match case-insensitive.
1389 </p>
1390 <div class="org-src-container">
1391 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regexp_match</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"bar.*que"</span>)) <span style="color: #23d7d7;">:single</span>)
1392
1393 #(<span style="color: #e67128;">"barbeque"</span>)
1394
1395 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regexp_match</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"bar.~que"</span>)) <span style="color: #23d7d7;">:single</span>)
1396
1397 <span style="color: #23d7d7;">:NULL</span>
1398
1399 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:~</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"bar.*que"</span>) ) <span style="color: #23d7d7;">:single</span>)
1400
1401 t
1402
1403 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:!~</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"bar.*que"</span>) ) <span style="color: #23d7d7;">:single</span>)
1404
1405 nil
1406
1407 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:~</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"barque"</span>) ) <span style="color: #23d7d7;">:single</span>)
1408
1409 nil
1410
1411 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:~</span> <span style="color: #e67128;">"foobarbequebaz"</span> <span style="color: #e67128;">"barbeque"</span>) ) <span style="color: #23d7d7;">:single</span>)
1412
1413 t
1414
1415 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:~</span> <span style="color: #e67128;">"foobarBequebaz"</span> <span style="color: #e67128;">"barbeque"</span>) ) <span style="color: #23d7d7;">:single</span>)
1416
1417 nil
1418
1419 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:~*</span> <span style="color: #e67128;">"foobarBequebaz"</span> <span style="color: #e67128;">"barbeque"</span>) ) <span style="color: #23d7d7;">:single</span>)
1420
1421 t
1422
1423 (query (<span style="color: #23d7d7;">:select</span> 'id 'text <span style="color: #23d7d7;">:from</span> 'text-search <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:~</span> 'text <span style="color: #e67128;">"sushi"</span>)))
1424
1425 </pre>
1426 </div>
1427 </div>
1428 </div>
1429
1430 <div id="outline-container-sql-op-like" class="outline-3">
1431 <h3 id="sql-op-like">sql-op :like, :ilike (string pattern)</h3>
1432 <div class="outline-text-3" id="text-sql-op-like">
1433 <p>
1434 Simple SQL string matching operators (:ilike is case-insensitive).
1435 </p>
1436 <div class="org-src-container">
1437 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
1438 <span style="color: #23d7d7;">:from</span> 'countries
1439 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%New%"</span>)))
1440 </pre>
1441 </div>
1442 </div>
1443 </div>
1444 <div id="outline-container-sql-op-double-ampersand" class="outline-3">
1445 <h3 id="sql-op-double-ampersand">sql-op :@@</h3>
1446 <div class="outline-text-3" id="text-sql-op-double-ampersand">
1447 <p>
1448 Fast Text Search match operator.
1449 </p>
1450 </div>
1451 </div>
1452
1453 <div id="outline-container-sql-op-desc" class="outline-3">
1454 <h3 id="sql-op-desc">sql-op :desc (column)</h3>
1455 <div class="outline-text-3" id="text-sql-op-desc">
1456 <p>
1457 Used to invert the meaning of an operator in an :order-by clause.
1458 </p>
1459 <div class="org-src-container">
1460 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
1461 (<span style="color: #23d7d7;">:select</span> 'location 'time 'report
1462 <span style="color: #23d7d7;">:distinct-on</span> 'location
1463 <span style="color: #23d7d7;">:from</span> 'weather-reports)
1464 'location (<span style="color: #23d7d7;">:desc</span> 'time)))
1465 </pre>
1466 </div>
1467 </div>
1468 </div>
1469 <div id="outline-container-sql-op-nulls-first" class="outline-3">
1470 <h3 id="sql-op-nulls-first">sql-op :nulls-first, :nulls-last (column)</h3>
1471 <div class="outline-text-3" id="text-sql-op-nulls-first">
1472 <p>
1473 Used to determine where :null values appear in an :order-by clause.
1474 </p>
1475 </div>
1476 </div>
1477
1478 <div id="outline-container-sql-op-as" class="outline-3">
1479 <h3 id="sql-op-as">sql-op :as (form name &amp;rest fields)</h3>
1480 <div class="outline-text-3" id="text-sql-op-as">
1481 <p>
1482 Also known in some explanations as "alias". This assigns a name to a column or
1483 table in a :select form. When fields are given, they are added after the name,
1484 in parentheses. For example, (:as 'table1 't1 'foo 'bar)
1485 becomes table1 AS t1(foo, bar). When you need to specify types for the fields,
1486 you can do something like (:as 'table2 't2 ('foo integer)). Note that names are
1487 quoted, types are not (when using sql-compile or sql-template, you can leave
1488 out the quotes entirely).
1489 </p>
1490 <div class="org-src-container">
1491 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'countries.name 'country)
1492 (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
1493 <span style="color: #23d7d7;">:from</span> 'countries 'regions
1494 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
1495 (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Central America"</span>)))
1496 <span style="color: #23d7d7;">:alists</span>)
1497
1498 (((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"Belize"</span>) (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>)) ((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"Costa Rica"</span>)
1499 (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>)) ((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"El Salvador"</span>)
1500 (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>)) ((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"Guatemala"</span>)
1501 (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>)) ((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"Panama"</span>) (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>))
1502 ((<span style="color: #23d7d7;">:COUNTRY</span> . <span style="color: #e67128;">"Nicaragua"</span>) (<span style="color: #23d7d7;">:REGION</span> . <span style="color: #e67128;">"Central America"</span>)))
1503 </pre>
1504 </div>
1505 <p>
1506 The following uses aliases for both columns and tables in the from and inner-join clauses:
1507 </p>
1508 <div class="org-src-container">
1509 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
1510 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'recs.firstname 'firstname)
1511 (<span style="color: #23d7d7;">:as</span> 'recs.surname 'surname)
1512 <span style="color: #23d7d7;">:distinct</span>
1513 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'mems)
1514 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'recs)
1515 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'recs.memid 'mems.recommendedby))
1516 'surname 'firstname))
1517 </pre>
1518 </div>
1519
1520 <p>
1521 Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:
1522 </p>
1523 <div class="org-src-container">
1524 <pre class="src src-lisp"><span style="color: #e67128;">"select sum(slots as "</span>Total Slots<span style="color: #e67128;">" from cd.bookings"</span>
1525 </pre>
1526 </div>
1527
1528 <p>
1529 without using :raw
1530 </p>
1531 </div>
1532 </div>
1533
1534 <div id="outline-container-sql-op-cast" class="outline-3">
1535 <h3 id="sql-op-cast">sql-op :cast (query)</h3>
1536 <div class="outline-text-3" id="text-sql-op-cast">
1537 <p>
1538 The CAST operator. Takes a query as an argument, and returns the result
1539 explicitly cast by postgresql to a specific type. Unlike :type, :cast can
1540 pass the type as a variable.
1541 </p>
1542 <div class="org-src-container">
1543 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:cast</span> (<span style="color: #23d7d7;">:as</span> <span style="color: #e67128;">"20"</span> 'integer)))
1544 <span style="color: #23d7d7;">:single</span>)
1545 20
1546
1547 (<span style="color: #ffad29; font-weight: bold;">let</span> ((type 'text))
1548 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:cast</span> (<span style="color: #23d7d7;">:as</span> <span style="color: #e67128;">"20"</span> type)))
1549 <span style="color: #23d7d7;">:single</span>))
1550 <span style="color: #e67128;">"20"</span>
1551
1552 (<span style="color: #ffad29; font-weight: bold;">let</span> ((type 'integer))
1553 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:cast</span> (<span style="color: #23d7d7;">:as</span> <span style="color: #e67128;">"20"</span> type)))
1554 <span style="color: #23d7d7;">:single</span>))
1555 20
1556
1557 (query (<span style="color: #23d7d7;">:union</span> (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 1 'real))
1558 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:cast</span> (<span style="color: #23d7d7;">:as</span> <span style="color: #e67128;">"2.2"</span> 'real)))))
1559 ((1.0) (2.2))
1560 </pre>
1561 </div>
1562 </div>
1563 </div>
1564 <div id="outline-container-sql-op-type-query" class="outline-3">
1565 <h3 id="sql-op-type-query">sql-op :type (query)</h3>
1566 <div class="outline-text-3" id="text-sql-op-type-query">
1567 <p>
1568 Is similar to cast but uses the postgresql :: formating. Unlike cast it will not
1569 accept a variable as the type.
1570 </p>
1571
1572 <p>
1573 E.g.
1574 </p>
1575 <div class="org-src-container">
1576 <pre class="src src-lisp">(sql (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:-</span> (<span style="color: #23d7d7;">:type</span> (<span style="color: #23d7d7;">:now</span>) 'date) 'x) 'some-date) <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:generate-series</span> 1 10) 'x)))
1577
1578 <span style="color: #e67128;">"(SELECT (now()::DATE - x) AS some_date FROM generate_series(1, 10) AS x)"</span>
1579 </pre>
1580 </div>
1581 </div>
1582 </div>
1583 <div id="outline-container-sql-op-type-form" class="outline-3">
1584 <h3 id="sql-op-type-form">sql-op :type (form type)</h3>
1585 <div class="outline-text-3" id="text-sql-op-type-form">
1586 <p>
1587 Add a type declaration to a value, as in in "4.3::real". The second
1588 argument is not evaluated normally, but put through sql-type-name to
1589 get a type identifier.
1590 </p>
1591 <div class="org-src-container">
1592 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:type</span> 1.0 int)))
1593 </pre>
1594 </div>
1595 </div>
1596 </div>
1597 <div id="outline-container-sql-op-create-composite-type" class="outline-3">
1598 <h3 id="sql-op-create-composite-type">sql-op :create-composite-type (type-name &amp;rest args)</h3>
1599 <div class="outline-text-3" id="text-sql-op-create-composite-type">
1600 <p>
1601 Creates a composite type with a type-name and two or more columns. E.g.
1602 </p>
1603 <div class="org-src-container">
1604 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-composite-type</span> 'fullname (first-name text) (last-name text)))
1605 </pre>
1606 </div>
1607 </div>
1608 </div>
1609 <div id="outline-container-sql-op-exists" class="outline-3">
1610 <h3 id="sql-op-exists">sql-op :exists (query)</h3>
1611 <div class="outline-text-3" id="text-sql-op-exists">
1612 <p>
1613 The EXISTS operator. Takes a query as an argument, and returns true or false
1614 depending on whether that query returns any rows. In the example below, it is
1615 applied to a subquery.
1616 </p>
1617 <div class="org-src-container">
1618 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
1619 <span style="color: #23d7d7;">:from</span> 'regions
1620 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:exists</span>
1621 (<span style="color: #23d7d7;">:select</span> 'region-id
1622 <span style="color: #23d7d7;">:from</span> 'countries
1623 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
1624 (<span style="color: #23d7d7;">:=</span> 'countries.name <span style="color: #e67128;">"Costa Rica"</span>)
1625 (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id))))))
1626 </pre>
1627 </div>
1628 </div>
1629 </div>
1630 <div id="outline-container-sql-op-is-false" class="outline-3">
1631 <h3 id="sql-op-is-false">sql-op :is-false (arg)</h3>
1632 <div class="outline-text-3" id="text-sql-op-is-false">
1633 <p>
1634 Test whether a binary value is false.
1635 </p>
1636 <div class="org-src-container">
1637 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'ta <span style="color: #23d7d7;">:from</span> 'a <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:is-false</span> 'ta)))
1638
1639 (select-dao 'account (:is-false 'active))</pre>
1640 </div>
1641 </div>
1642 </div>
1643
1644 <div id="outline-container-sql-op-is-true" class="outline-3">
1645 <h3 id="sql-op-is-true">sql-op :is-true (arg)</h3>
1646 <div class="outline-text-3" id="text-sql-op-is-true">
1647 <p>
1648 Test whether a binary value is true.
1649 </p>
1650 <div class="org-src-container">
1651 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'ta <span style="color: #23d7d7;">:from</span> 'a <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:is-true</span> 'ta)))
1652
1653 (select-dao 'account (:is-true 'active))</pre>
1654 </div>
1655 </div>
1656 </div>
1657
1658 <div id="outline-container-sql-op-is-null" class="outline-3">
1659 <h3 id="sql-op-is-null">sql-op :is-null (arg)</h3>
1660 <div class="outline-text-3" id="text-sql-op-is-null">
1661 <p>
1662 Test whether a value is null.
1663 </p>
1664 <div class="org-src-container">
1665 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'ta <span style="color: #23d7d7;">:from</span> 'a <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not</span> (<span style="color: #23d7d7;">:is-null</span> 'ta))))
1666 </pre>
1667 </div>
1668 </div>
1669 </div>
1670
1671 <div id="outline-container-sql-op-not-null" class="outline-3">
1672 <h3 id="sql-op-not-null">sql-op :not-null (arg)</h3>
1673 <div class="outline-text-3" id="text-sql-op-not-null">
1674 <p>
1675 Test whether a value is not null.
1676 </p>
1677 <div class="org-src-container">
1678 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'ta <span style="color: #23d7d7;">:from</span> 'a <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not-null</span> 'ta)))
1679 </pre>
1680 </div>
1681 </div>
1682 </div>
1683 <div id="outline-container-sql-op-in" class="outline-3">
1684 <h3 id="sql-op-in">sql-op :in (value set)</h3>
1685 <div class="outline-text-3" id="text-sql-op-in">
1686 <p>
1687 Test whether a value is in a set of values.
1688 </p>
1689 <div class="org-src-container">
1690 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
1691 <span style="color: #23d7d7;">:from</span> 'countries
1692 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
1693 (<span style="color: #23d7d7;">:set</span> 20 21 23))))
1694
1695 (query (<span style="color: #23d7d7;">:select</span> 'region 'product (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'quantity) 'product-units)
1696 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'amount) 'product-sales)
1697 <span style="color: #23d7d7;">:from</span> 'orders
1698 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'region (<span style="color: #23d7d7;">:select</span> 'region <span style="color: #23d7d7;">:from</span> 'top-regions))
1699 <span style="color: #23d7d7;">:group-by</span> 'region 'product))
1700 </pre>
1701 </div>
1702 </div>
1703 </div>
1704 <div id="outline-container-sql-op-not-in" class="outline-3">
1705 <h3 id="sql-op-not-in">sql-op :not-in (value set)</h3>
1706 <div class="outline-text-3" id="text-sql-op-not-in">
1707 <p>
1708 Inverse of the above.
1709 </p>
1710 </div>
1711 </div>
1712
1713 <div id="outline-container-sql-op-set" class="outline-3">
1714 <h3 id="sql-op-set">sql-op :set (&amp;rest elements)</h3>
1715 <div class="outline-text-3" id="text-sql-op-set">
1716 <p>
1717 Denote a set of values. This operator has two interfaces. When
1718 the elements are known at compile-time, they can be given as
1719 multiple arguments to the operator. When they are not, a
1720 single argument that evaluates to a list should be used.
1721 </p>
1722
1723 <p>
1724 The following would be the syntax in postmodern sql where the set is a list. If
1725 you want to use a vector, then you need to use Any:
1726 </p>
1727
1728 <p>
1729 The following are equivalent
1730 </p>
1731 <div class="org-src-container">
1732 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
1733 <span style="color: #23d7d7;">:from</span> 'countries
1734 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
1735 (<span style="color: #23d7d7;">:set</span> 20 21 23))))
1736
1737 (<span style="color: #ffad29; font-weight: bold;">let</span> ((x (list 20 21 23)))
1738 (query (<span style="color: #23d7d7;">:select</span> 'name
1739 <span style="color: #23d7d7;">:from</span> 'countries
1740 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
1741 (<span style="color: #23d7d7;">:set</span> x)))))
1742
1743 (query (<span style="color: #23d7d7;">:select</span> 'name
1744 <span style="color: #23d7d7;">:from</span> 'countries
1745 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id (<span style="color: #23d7d7;">:set</span> (list 20 21 23)))))
1746
1747 </pre>
1748 </div>
1749 <p>
1750 However, the following will generate an error about inability to convert to an sql literal
1751 </p>
1752
1753 <div class="org-src-container">
1754 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
1755 <span style="color: #23d7d7;">:from</span> 'countries
1756 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
1757 (<span style="color: #23d7d7;">:set</span> '(20 21 23)))))
1758 </pre>
1759 </div>
1760 <p>
1761 Now with selecting a dao
1762 </p>
1763 <div class="org-src-container">
1764 <pre class="src src-lisp">(select-dao 'countries
1765 (<span style="color: #23d7d7;">:in</span> 'id
1766 (<span style="color: #23d7d7;">:set</span> (list 20 21 23
1767 </pre>
1768 </div>
1769 <p>
1770 Now with selecting from a vector. Note both the use of any* and := instead of :in.
1771 </p>
1772 <div class="org-src-container">
1773 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((x (vector 20 21 23)))
1774 (query (<span style="color: #23d7d7;">:select</span> 'name
1775 <span style="color: #23d7d7;">:from</span> 'countries
1776 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any*</span> x)))))
1777 </pre>
1778 </div>
1779 <p>
1780 Note that the responses will still come back in a list of lists
1781 </p>
1782
1783 <p>
1784 IMPORTANT: If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in."
1785 </p>
1786 </div>
1787 </div>
1788
1789 <div id="outline-container-sql-op-array" class="outline-3">
1790 <h3 id="sql-op-array">sql-op :array (query)</h3>
1791 <div class="outline-text-3" id="text-sql-op-array">
1792 <p>
1793 This is used when calling a select query into an array. See <a href="array-notes.html">array-notes.html</a>
1794 for more detailed notes on the use of arrays.
1795 </p>
1796 <div class="org-src-container">
1797 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
1798 (<span style="color: #23d7d7;">:select</span> 'r.rolename
1799 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:array</span>
1800 (<span style="color: #23d7d7;">:select</span> 'b.rolename
1801 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg_catalog.pg-auth-members 'm)
1802 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'pg-catalog.pg-roles 'b)
1803 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'm.roleid 'b.oid)
1804 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'm.member 'r.oid )))
1805 'memberof)
1806
1807 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg-catalog.pg-roles 'r))
1808 1))
1809
1810 </pre>
1811 </div>
1812 </div>
1813 </div>
1814
1815 <div id="outline-container-sql-op-array-rest" class="outline-3">
1816 <h3 id="sql-op-array-rest">sql-op :array[] (&amp;rest args)</h3>
1817 <div class="outline-text-3" id="text-sql-op-array-rest">
1818 <p>
1819 This is the general operator for arrays. It also handles statements that include
1820 functions in the query such as (:+ 1 2), (:pi) in the array. See <a href="array-notes.html">array-notes.html</a>
1821 for more detailed notes on the use of arrays.
1822 </p>
1823 <div class="org-src-container">
1824 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:array-prepend</span> 1 (<span style="color: #23d7d7;">:array[]</span> 2 3))))
1825
1826 ((#(1 2 3)))
1827
1828 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:array-prepend</span> 1 (<span style="color: #23d7d7;">:array[]</span> 2 3)))
1829 <span style="color: #23d7d7;">:single</span>)
1830
1831 #(1 2 3)
1832 </pre>
1833 </div>
1834 </div>
1835 </div>
1836
1837 <div id="outline-container-sql-op-square-brackets" class="outline-3">
1838 <h3 id="sql-op-square-brackets">sql-op :[] (form start &amp;optional end)</h3>
1839 <div class="outline-text-3" id="text-sql-op-square-brackets">
1840 <p>
1841 Dereference an array value. If end is provided, extract a slice of the array.
1842 Sample usage below, but also see <a href="array-notes.html">array-notes.html</a> for more detailed notes on
1843 the use of arrays.
1844 </p>
1845 <div class="org-src-container">
1846 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'receipe-id (<span style="color: #23d7d7;">:[]</span> 'tags 2 3)
1847 <span style="color: #23d7d7;">:from</span> 'receipe-tags-array
1848 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'receipe-id 3)))
1849 </pre>
1850 </div>
1851 </div>
1852 </div>
1853
1854 <div id="outline-container-sql-op-extract" class="outline-3">
1855 <h3 id="sql-op-extract">sql-op :extract (unit form)</h3>
1856 <div class="outline-text-3" id="text-sql-op-extract">
1857 <p>
1858 Extract a field from a date/time value. For example, (:extract :month (:now)).
1859 </p>
1860 <div class="org-src-container">
1861 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
1862 (<span style="color: #23d7d7;">:select</span> 'facid
1863 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:extract</span> 'month 'starttime) 'month)
1864 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'slots) 'total-slots)
1865 <span style="color: #23d7d7;">:from</span> 'cd.bookings
1866 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:&gt;=</span> 'starttime <span style="color: #e67128;">"2012-01-01"</span>)
1867 (<span style="color: #23d7d7;">:&lt;</span> 'starttime <span style="color: #e67128;">"2013-01-01"</span>))
1868 <span style="color: #23d7d7;">:group-by</span> 'facid 'month)
1869 'facid 'month))
1870 </pre>
1871 </div>
1872 </div>
1873 </div>
1874 <div id="outline-container-sql-op-case" class="outline-3">
1875 <h3 id="sql-op-case">sql-op :case (&amp;rest clauses)</h3>
1876 <div class="outline-text-3" id="text-sql-op-case">
1877 <p>
1878 A conditional expression. Clauses should take the form (test value). If
1879 test is :else, an ELSE clause will be generated.
1880 </p>
1881 <div class="org-src-container">
1882 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
1883 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:case</span> ((<span style="color: #23d7d7;">:&gt;</span> 'monthlymaintenance 100) <span style="color: #e67128;">"expensive"</span>)
1884 (<span style="color: #23d7d7;">:else</span> <span style="color: #e67128;">"cheap"</span>)) <span style="color: #ff4242; font-weight: bold;">'cost)</span>
1885 <span style="color: #23d7d7;">:from</span> 'cd.facilities))
1886 </pre>
1887 </div>
1888 </div>
1889 </div>
1890 <div id="outline-container-sql-op-between" class="outline-3">
1891 <h3 id="sql-op-between">sql-op :between (n start end)</h3>
1892 <div class="outline-text-3" id="text-sql-op-between">
1893 <p>
1894 Test whether a value lies between two other values.
1895 </p>
1896 <div class="org-src-container">
1897 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
1898 <span style="color: #23d7d7;">:from</span> 'countries
1899 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:between</span> 'latitude -10 10))
1900 <span style="color: #23d7d7;">:column</span>)
1901 </pre>
1902 </div>
1903 </div>
1904 </div>
1905 <div id="outline-container-sql-op-between-symmetric" class="outline-3">
1906 <h3 id="sql-op-between-symmetric">sql-op :between-symmetric (n start end)</h3>
1907 <div class="outline-text-3" id="text-sql-op-between-symmetric">
1908 <p>
1909 Works like :between, except that the start value is not required to be
1910 less than the end value.
1911 </p>
1912 </div>
1913 </div>
1914
1915 <div id="outline-container-sql-op-dot" class="outline-3">
1916 <h3 id="sql-op-dot">sql-op :dot (&amp;rest names)</h3>
1917 <div class="outline-text-3" id="text-sql-op-dot">
1918 <p>
1919 Can be used to combine multiple names into a name of the form A.B to
1920 refer to a column in a table, or a table in a schema. Note that you
1921 can also just use a symbol with a dot in it.
1922 </p>
1923 </div>
1924 </div>
1925
1926 <div id="outline-container-sql-op-raw-string" class="outline-3">
1927 <h3 id="sql-op-raw-string">sql-op :raw (string)</h3>
1928 <div class="outline-text-3" id="text-sql-op-raw-string">
1929 <p>
1930 Insert a string as-is into the query. This can be useful for doing things
1931 that the syntax does not support, or to re-use parts of a query across
1932 multiple queries:
1933 </p>
1934 <div class="org-src-container">
1935 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let*</span> ((test (sql (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'foo 22) (<span style="color: #23d7d7;">:not-null</span> 'bar))))
1936 (rows (query (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'baz <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:raw</span> test)))))
1937 (query (<span style="color: #23d7d7;">:delete-from</span> 'baz <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:raw</span> test)))
1938 (<span style="color: #ffad29; font-weight: bold;">do-stuff</span> rows))
1939 </pre>
1940 </div>
1941 </div>
1942 </div>
1943
1944 <div id="outline-container-sql-op-fetch" class="outline-3">
1945 <h3 id="sql-op-fetch">sql-op :fetch (form amount &amp;optional offset)</h3>
1946 <div class="outline-text-3" id="text-sql-op-fetch">
1947 <p>
1948 Fetch is a more efficient way to do pagination instead of using limit and
1949 offset. Fetch allows you to retrieve a limited set of rows, optionally offset
1950 by a specified number of rows. In order to ensure this works correctly, you
1951 should use the order-by clause. If the amount is not provided, it assumes
1952 you only want to return 1 row.
1953 <a href="https://www.postgresql.org/docs/current/sql-select.html">https://www.postgresql.org/docs/current/sql-select.html</a>
1954 </p>
1955
1956 <p>
1957 Examples:
1958 </p>
1959 <div class="org-src-container">
1960 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:fetch</span> (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'id <span style="color: #23d7d7;">:from</span> 'historical-events) 'id) 5))
1961
1962 ((1) (2) (3) (4) (5))
1963
1964 (query (<span style="color: #23d7d7;">:fetch</span> (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'id <span style="color: #23d7d7;">:from</span> 'historical-events) 'id) 5 10))
1965
1966 ((11) (12) (13) (14) (15))
1967 </pre>
1968 </div>
1969 </div>
1970 </div>
1971
1972 <div id="outline-container-sql-op-limit" class="outline-3">
1973 <h3 id="sql-op-limit">sql-op :limit (query amount &amp;optional offset)</h3>
1974 <div class="outline-text-3" id="text-sql-op-limit">
1975 <p>
1976 In S-SQL limit is not part of the select operator, but an extra
1977 operator that is applied to a query (this works out better when limiting
1978 the union or intersection of multiple queries, same for sorting).
1979 It limits the number of results to the amount given as the second
1980 argument, and optionally offsets the result by the amount given
1981 as the third argument.
1982 </p>
1983 <div class="org-src-container">
1984 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:limit</span> (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'surname <span style="color: #23d7d7;">:distinct</span> <span style="color: #23d7d7;">:from</span> 'cd.members) 'surname) 10))
1985 </pre>
1986 </div>
1987 </div>
1988 </div>
1989 <div id="outline-container-sql-op-order-by" class="outline-3">
1990 <h3 id="sql-op-order-by">sql-op :order-by (query &amp;rest exprs)</h3>
1991 <div class="outline-text-3" id="text-sql-op-order-by">
1992 <p>
1993 Order the results of a query by the given expressions. See :desc for
1994 when you want to invert an ordering. Note: This is not the same as
1995 passing an :order-by parameter to an aggregation operator.
1996 For that see Aggregation Operators.
1997 </p>
1998 <div class="org-src-container">
1999 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'id 'name 'city 'salary (<span style="color: #23d7d7;">:every</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"J%"</span>))
2000 <span style="color: #23d7d7;">:from</span> 'employee
2001 <span style="color: #23d7d7;">:group-by</span> 'name 'id 'salary 'city)
2002 'name))
2003 </pre>
2004 </div>
2005 </div>
2006 </div>
2007 <div id="outline-container-sql-op-values" class="outline-3">
2008 <h3 id="sql-op-values">sql-op :values</h3>
2009 <div class="outline-text-3" id="text-sql-op-values">
2010 <p>
2011 Values computes a row value or set of row values for use in a specific
2012 query. See the postgresql docs at:
2013 <a href="https://www.postgresql.org/docs/current/static/queries-values.html">https://www.postgresql.org/docs/current/static/queries-values.html</a>
2014 and <a href="https://www.postgresql.org/docs/current/static/sql-values.html">https://www.postgresql.org/docs/current/static/sql-values.html</a>
2015 Example:
2016 </p>
2017 <div class="org-src-container">
2018 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '*
2019 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> 1 <span style="color: #e67128;">"one"</span>)
2020 (<span style="color: #23d7d7;">:set</span> 2 <span style="color: #e67128;">"two"</span>)
2021 (<span style="color: #23d7d7;">:set</span> 3 <span style="color: #e67128;">"three"</span>))
2022 (<span style="color: #23d7d7;">:t1</span> 'num 'letter))))
2023
2024 (query (<span style="color: #23d7d7;">:select</span> 'a 'b 'c (<span style="color: #23d7d7;">:cast</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:*</span> 50 (<span style="color: #23d7d7;">:random</span>)) 'int))
2025 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"a"</span>) (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"b"</span>)) (<span style="color: #23d7d7;">:d1</span> 'a))
2026 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"c"</span>) (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"d"</span>)) (<span style="color: #23d7d7;">:d2</span> 'b))
2027 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"e"</span>) (<span style="color: #23d7d7;">:set</span> <span style="color: #e67128;">"f"</span>)) (<span style="color: #23d7d7;">:d3</span> 'c))))
2028
2029 (query
2030 (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
2031 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:t1</span> 'n)
2032 (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> 1))
2033 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'n 1)
2034 <span style="color: #23d7d7;">:from</span> 't1
2035 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'n 100))))
2036 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:sum</span> 'n) <span style="color: #23d7d7;">:from</span> 't1))
2037 <span style="color: #23d7d7;">:single</span>)
2038 </pre>
2039 </div>
2040 </div>
2041 </div>
2042
2043 <div id="outline-container-sql-op-empty-set" class="outline-3">
2044 <h3 id="sql-op-empty-set">sql-op :empty-set</h3>
2045 <div class="outline-text-3" id="text-sql-op-empty-set">
2046 <p>
2047 This is a fudge. It returns a string "()" where something like '()
2048 would return "false" or :() would throw an error. Example:
2049 </p>
2050 <div class="org-src-container">
2051 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'appnumber 'day (<span style="color: #23d7d7;">:sum</span> 'inserts)
2052 (<span style="color: #23d7d7;">:sum</span> 'updates) (<span style="color: #23d7d7;">:sum</span> 'deletes) (<span style="color: #23d7d7;">:sum</span> 'transactions)
2053 <span style="color: #23d7d7;">:from</span> 'db-details
2054 <span style="color: #23d7d7;">:group-by</span> (<span style="color: #23d7d7;">:grouping-sets</span> (<span style="color: #23d7d7;">:set</span> 'appnumber 'day (<span style="color: #23d7d7;">:empty-set</span>)))))
2055 </pre>
2056 </div>
2057 </div>
2058 </div>
2059
2060 <div id="outline-container-sql-op-group-by" class="outline-3">
2061 <h3 id="sql-op-group-by">sql-op :group-by</h3>
2062 <div class="outline-text-3" id="text-sql-op-group-by">
2063 <p>
2064 <a href="https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS">https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS</a>
2065 The GROUP BY Clause is used to group together those rows in a table that
2066 have the same values in all the columns listed. The order in which the
2067 columns are listed does not matter. The effect is to combine each set of
2068 rows having common values into one group row that represents all rows in
2069 the group. This is done to eliminate redundancy in the output and/or compute
2070 aggregates that apply to these groups. Example:
2071 </p>
2072 <div class="org-src-container">
2073 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
2074 (<span style="color: #23d7d7;">:select</span> 'mems.surname 'mems.firstname 'mems.memid (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:min</span> 'bks.starttime) 'starttime)
2075 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'cd.bookings 'bks)
2076 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'mems)
2077 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'mems.memid 'bks.memid)
2078 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&gt;=</span> 'starttime <span style="color: #e67128;">"2012-09-01"</span>)
2079 <span style="color: #23d7d7;">:group-by</span> 'mems.surname 'mems.firstname 'mems.memid)
2080 'mems.memid))
2081 </pre>
2082 </div>
2083 </div>
2084 </div>
2085
2086 <div id="outline-container-sql-op-grouping-sets" class="outline-3">
2087 <h3 id="sql-op-grouping-sets">sql-op :grouping-sets</h3>
2088 <div class="outline-text-3" id="text-sql-op-grouping-sets">
2089 <p>
2090 <a href="https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS">https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS</a>
2091 More complex grouping operations are possible using the concept of grouping
2092 sets. The data selected by the FROM and WHERE clauses is grouped separately
2093 by each specified grouping set, aggregates computed for each group just as
2094 for simple GROUP BY clauses, and then the results returned.
2095 This operator requires postgresql 9.5 or later. For example:
2096 </p>
2097 <div class="org-src-container">
2098 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'city (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:extract</span> 'year 'start-date) 'joining-year) (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:count</span> 1) 'employee_count)
2099 <span style="color: #23d7d7;">:from</span> 'employee
2100 <span style="color: #23d7d7;">:group-by</span> (<span style="color: #23d7d7;">:grouping-sets</span> (<span style="color: #23d7d7;">:set</span> 'city (<span style="color: #23d7d7;">:extract</span> 'year 'start-date)))))
2101 </pre>
2102 </div>
2103 </div>
2104 </div>
2105 </div>
2106
2107 <div id="outline-container-sql-op-time-date-and-interval" class="outline-2">
2108 <h2 id="sql-op-time-date-and-interval">Time, Date and Interval Operators</h2>
2109 <div class="outline-text-2" id="text-sql-op-time-date-and-interval">
2110 </div>
2111 <div id="outline-container-sql-op-interval" class="outline-3">
2112 <h3 id="sql-op-interval">sql-op :interval (arg)</h3>
2113 <div class="outline-text-3" id="text-sql-op-interval">
2114 <p>
2115 Creates an interval data type, generally represented in postmodern as an alist
2116 </p>
2117 </div>
2118 </div>
2119 <div id="outline-container-sql-op-current-date" class="outline-3">
2120 <h3 id="sql-op-current-date">sql-op :current-date ()</h3>
2121 <div class="outline-text-3" id="text-sql-op-current-date">
2122 <div class="org-src-container">
2123 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:current-date</span>)) <span style="color: #23d7d7;">:single</span>)
2124 </pre>
2125 </div>
2126 </div>
2127 </div>
2128 <div id="outline-container-sql-op-current-time" class="outline-3">
2129 <h3 id="sql-op-current-time">sql-op :current-time ()</h3>
2130 <div class="outline-text-3" id="text-sql-op-current-time">
2131 </div>
2132 </div>
2133 <div id="outline-container-sql-op-current-timestamp" class="outline-3">
2134 <h3 id="sql-op-current-timestamp">sql-op :current-timestamp ()</h3>
2135 <div class="outline-text-3" id="text-sql-op-current-timestamp">
2136 </div>
2137 </div>
2138 <div id="outline-container-sql-op-timestamp" class="outline-3">
2139 <h3 id="sql-op-timestamp">sql-op :timestamp (arg)</h3>
2140 <div class="outline-text-3" id="text-sql-op-timestamp">
2141 </div>
2142 </div>
2143 <div id="outline-container-sql-op-age" class="outline-3">
2144 <h3 id="sql-op-age">sql-op :age (&amp;rest args)</h3>
2145 <div class="outline-text-3" id="text-sql-op-age">
2146 </div>
2147 </div>
2148 <div id="outline-container-sql-op-date" class="outline-3">
2149 <h3 id="sql-op-date">sql-op :date (arg)</h3>
2150 <div class="outline-text-3" id="text-sql-op-date">
2151 </div>
2152 </div>
2153 <div id="outline-container-sql-op-make-insterval" class="outline-3">
2154 <h3 id="sql-op-make-insterval">sql-op :make-interval (&amp;rest args)</h3>
2155 <div class="outline-text-3" id="text-sql-op-make-insterval">
2156 <p>
2157 Takes lists of (time-unit value) and returns a timestamp type. Example:
2158 </p>
2159
2160 <div class="org-src-container">
2161 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:make-interval</span> (<span style="color: #e67128;">"days"</span> 4) (<span style="color: #e67128;">"hours"</span> 10) (<span style="color: #e67128;">"secs"</span> 1.2)))
2162 <span style="color: #23d7d7;">:single</span>)
2163 </pre>
2164 </div>
2165 </div>
2166 </div>
2167 <div id="outline-container-sql-op-make-timestamp" class="outline-3">
2168 <h3 id="sql-op-make-timestamp">sql-op :make-timestamp (&amp;rest args)</h3>
2169 <div class="outline-text-3" id="text-sql-op-make-timestamp">
2170 <p>
2171 Takes lists of (time-unit value) and returns a timestamptz type. Example:
2172 </p>
2173 <div class="org-src-container">
2174 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span>
2175 (<span style="color: #23d7d7;">:make-timestamptz</span> (<span style="color: #e67128;">"year"</span> 2014) (<span style="color: #e67128;">"month"</span> 1) (<span style="color: #e67128;">"mday"</span> 13)
2176 (<span style="color: #e67128;">"hour"</span> 21) (<span style="color: #e67128;">"min"</span> 50) (<span style="color: #e67128;">"sec"</span> 0)))
2177 <span style="color: #23d7d7;">:single</span>)
2178 </pre>
2179 </div>
2180 </div>
2181 </div>
2182 <div id="outline-container-sql-op-make-timestamptz" class="outline-3">
2183 <h3 id="sql-op-make-timestamptz">sql-op :make-timestamptz (&amp;rest args)</h3>
2184 <div class="outline-text-3" id="text-sql-op-make-timestamptz">
2185 <p>
2186 Takes lists of (time-unit value) and returns a timestamptz type. Example:
2187 </p>
2188 <div class="org-src-container">
2189 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span>
2190 (<span style="color: #23d7d7;">:make-timestamptz</span> (<span style="color: #e67128;">"year"</span> 2014) (<span style="color: #e67128;">"month"</span> 1) (<span style="color: #e67128;">"mday"</span> 13)
2191 (<span style="color: #e67128;">"hour"</span> 21) (<span style="color: #e67128;">"min"</span> 50) (<span style="color: #e67128;">"sec"</span> 0) (<span style="color: #e67128;">"timezone"</span> <span style="color: #e67128;">"Asia/Tokyo"</span>)))
2192 <span style="color: #23d7d7;">:single</span>)
2193 </pre>
2194 </div>
2195 </div>
2196 </div>
2197 </div>
2198
2199 <div id="outline-container-sql-op-aggregation-operators" class="outline-2">
2200 <h2 id="sql-op-aggregation-operators">Aggregation Operators</h2>
2201 <div class="outline-text-2" id="text-sql-op-aggregation-operators">
2202 </div>
2203 <div id="outline-container-sql-op-count" class="outline-3">
2204 <h3 id="sql-op-count">sql-op :count (&amp;rest args)</h3>
2205 <div class="outline-text-3" id="text-sql-op-count">
2206 <p>
2207 Count returns the number of rows for which the expression is not null.
2208 It can be the number of rows collected by the select statement as in:
2209 </p>
2210 <div class="org-src-container">
2211 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> '*)
2212 <span style="color: #23d7d7;">:from</span> 'table1
2213 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'price 100)))
2214 </pre>
2215 </div>
2216
2217 <p>
2218 or it can be a smaller number of rows based on the allowed keyword
2219 parameters :distinct and :filter or some other type of condition as in:
2220 </p>
2221 <div class="org-src-container">
2222 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> 'memid <span style="color: #23d7d7;">:distinct</span>)
2223 <span style="color: #23d7d7;">:from</span> 'cd.bookings))
2224 </pre>
2225 </div>
2226 <p>
2227 or
2228 </p>
2229
2230 <div class="org-src-container">
2231 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:count</span> '* <span style="color: #23d7d7;">:distinct</span>) 'unfiltered)
2232 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:count</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid))
2233 'filtered)
2234 <span style="color: #23d7d7;">:from</span> 'testtable))
2235 </pre>
2236 </div>
2237
2238 <p>
2239 Note that if used, the filter must be last in the count args. If distinct
2240 is used, it must come before filter. Unlike standard sql, the word 'where'
2241 is not used inside the filter clause. E.g.
2242 </p>
2243 <div class="org-src-container">
2244 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> '*)
2245 (<span style="color: #23d7d7;">:count</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid))
2246 'id
2247 <span style="color: #23d7d7;">:from</span> 'pbbench-history))
2248 </pre>
2249 </div>
2250
2251 <p>
2252 See tests.lisp for examples.
2253 </p>
2254 </div>
2255 </div>
2256
2257 <div id="outline-container-sql-op-avg" class="outline-3">
2258 <h3 id="sql-op-avg">sql-op :avg (&amp;rest rest args)</h3>
2259 <div class="outline-text-3" id="text-sql-op-avg">
2260 <p>
2261 Avg calculates the average value of a list of values. Note that if the
2262 filter keyword is used, the filter must be last in the avg args. If distinct
2263 is used, it must come before filter. E.g. See tests.lisp for more examples.
2264 </p>
2265 <div class="org-src-container">
2266 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:avg</span> '*) (<span style="color: #23d7d7;">:avg</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid)) 'id
2267 <span style="color: #23d7d7;">:from</span> 'pbbench-history))
2268 </pre>
2269 </div>
2270 </div>
2271 </div>
2272
2273 <div id="outline-container-sql-op-sum" class="outline-3">
2274 <h3 id="sql-op-sum">sql-op :sum (&amp;rest rest args)</h3>
2275 <div class="outline-text-3" id="text-sql-op-sum">
2276 <p>
2277 Sum calculates the total of a list of values. Note that if the keyword filter
2278 is used, the filter must be last in the sum args. If distinct is used, it
2279 must come before filter. Unlike standard sql, the word 'where' is not used
2280 inside the filter clause (s-sql will properly expand it). See tests.lisp
2281 for more examples.
2282 </p>
2283 <div class="org-src-container">
2284 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:sum</span> '*) (<span style="color: #23d7d7;">:sum</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid)) 'id
2285 <span style="color: #23d7d7;">:from</span> 'pbbench-history))
2286 </pre>
2287 </div>
2288 </div>
2289 </div>
2290
2291 <div id="outline-container-sql-op-max" class="outline-3">
2292 <h3 id="sql-op-max">sql-op ::max (&amp;rest args)</h3>
2293 <div class="outline-text-3" id="text-sql-op-max">
2294 <p>
2295 max returns the maximum value of a set of values. Note that if the filter
2296 keyword is used, the filter must be last in the max args. If distinct is
2297 used, it must come before filter. Unlike standard sql, the word 'where'
2298 is not used inside the filter clause (s-sql will properly expand it).
2299 See tests.lisp for more examples.
2300 </p>
2301
2302 <div class="org-src-container">
2303 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:max</span> '*) (<span style="color: #23d7d7;">:max</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid)) 'id
2304 <span style="color: #23d7d7;">:from</span> 'pbbench-history))
2305 </pre>
2306 </div>
2307 </div>
2308 </div>
2309 <div id="outline-container-sql-op-min" class="outline-3">
2310 <h3 id="sql-op-min">sql-op ::min (&amp;rest args)</h3>
2311 <div class="outline-text-3" id="text-sql-op-min">
2312 <p>
2313 min returns the minimum value of a set of values. Note that if the filter
2314 keyword is used, the filter must be last in the min args. If distinct is
2315 used, it must come before filter. Unlike standard sql, the word 'where'
2316 is not used inside the filter clause (s-sql will properly expand it).
2317 See tests.lisp for more examples.
2318 </p>
2319 <div class="org-src-container">
2320 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:min</span> '*) (<span style="color: #23d7d7;">:min</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid)) 'id
2321 <span style="color: #23d7d7;">:from</span> 'pbbench-history))
2322 </pre>
2323 </div>
2324 </div>
2325 </div>
2326
2327 <div id="outline-container-sql-op-every" class="outline-3">
2328 <h3 id="sql-op-every">sql-op ::every (&amp;rest args)</h3>
2329 <div class="outline-text-3" id="text-sql-op-every">
2330 <p>
2331 Every returns true if all input values are true, otherwise false. Note
2332 that if the filter keyword is used, the filter must be last in the every
2333 args. If distinct is used, it must come before filter. Unlike standard sql,
2334 the word 'where' is not used inside the filter clause (s-sql will
2335 properly expand it). See tests.lisp for more examples.
2336 </p>
2337 <div class="org-src-container">
2338 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '* (<span style="color: #23d7d7;">:every</span> (<span style="color: #23d7d7;">:like</span> 'studname <span style="color: #e67128;">"%h"</span>))
2339 <span style="color: #23d7d7;">:from</span> 'tbl-students
2340 <span style="color: #23d7d7;">:group-by</span> 'studname 'studid 'studgrades))
2341 </pre>
2342 </div>
2343 </div>
2344 </div>
2345
2346 <div id="outline-container-sql-op-percentile-cont" class="outline-3">
2347 <h3 id="sql-op-percentile-cont">sql-op :percentile-cont (&amp;rest args)</h3>
2348 <div class="outline-text-3" id="text-sql-op-percentile-cont">
2349 <p>
2350 Requires Postgresql 9.4 or higher. Percentile-cont returns a value
2351 corresponding to the specified fraction in the ordering, interpolating
2352 between adjacent input items if needed. There are two required keyword
2353 parameters :fraction and :order-by. If the fraction value is an array,
2354 then it returns an array of results matching the shape of the fractions
2355 parameter, with each non-null element replaced by the value corresponding
2356 to that percentile. Examples:
2357 </p>
2358 <div class="org-src-container">
2359 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:percentile-cont</span> <span style="color: #23d7d7;">:fraction</span> 0.5 <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
2360 <span style="color: #23d7d7;">:from</span> 'schools))
2361
2362 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:percentile-cont</span> <span style="color: #23d7d7;">:fraction</span> array[0.25 0.5 0.75 1]
2363 <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
2364 <span style="color: #23d7d7;">:from</span> 'schools))
2365 </pre>
2366 </div>
2367 </div>
2368 </div>
2369
2370
2371 <div id="outline-container-sql-op-percentile-dist" class="outline-3">
2372 <h3 id="sql-op-percentile-dist">sql-op :percentile-dist (&amp;rest args)</h3>
2373 <div class="outline-text-3" id="text-sql-op-percentile-dist">
2374 <p>
2375 Requires Postgresql 9.4 or higher. There are two required keyword parameters
2376 :fraction and :order-by. Percentile-dist returns the first input value whose
2377 position in the ordering equals or exceeds the specified fraction. If the
2378 fraction parameter is an array eturns an array of results matching the shape
2379 of the fractions parameter, with each non-null element replaced by the input
2380 value corresponding to that percentile. Examples:
2381 </p>
2382
2383 <div class="org-src-container">
2384 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:percentile-dist</span> <span style="color: #23d7d7;">:fraction</span> 0.5
2385 <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
2386 <span style="color: #23d7d7;">:from</span> 'schools))
2387
2388 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:percentile-dist</span> <span style="color: #23d7d7;">:fraction</span> array[0.25 0.5 0.75 1]
2389 <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
2390 <span style="color: #23d7d7;">:from</span> 'schools))
2391
2392 </pre>
2393 </div>
2394 </div>
2395 </div>
2396
2397 <div id="outline-container-sql-op-corr" class="outline-3">
2398 <h3 id="sql-op-corr">sql-op :corr (y x)</h3>
2399 <div class="outline-text-3" id="text-sql-op-corr">
2400 <p>
2401 The corr function returns the correlation coefficient between a set of
2402 dependent and independent variables. Example:
2403 </p>
2404 <div class="org-src-container">
2405 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:corr</span> 'height 'weight)
2406 <span style="color: #23d7d7;">:from</span> 'people))
2407 </pre>
2408 </div>
2409 </div>
2410 </div>
2411
2412 <div id="outline-container-sql-op-covar-pop" class="outline-3">
2413 <h3 id="sql-op-covar-pop">sql-op :covar-pop (y x)</h3>
2414 <div class="outline-text-3" id="text-sql-op-covar-pop">
2415 <p>
2416 The covar-pop function returns the population covariance between a set of
2417 dependent and independent variables. Example:
2418 </p>
2419 <div class="org-src-container">
2420 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:covar-pop</span> 'height 'weight)
2421 <span style="color: #23d7d7;">:from</span> 'people))
2422 </pre>
2423 </div>
2424 </div>
2425 </div>
2426
2427 <div id="outline-container-sql-op-covar-samp" class="outline-3">
2428 <h3 id="sql-op-covar-samp">sql-op :covar-samp (y x)</h3>
2429 <div class="outline-text-3" id="text-sql-op-covar-samp">
2430 <div class="org-src-container">
2431 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:covar-samp</span> 'height 'weight)
2432 <span style="color: #23d7d7;">:from</span> 'people))
2433 </pre>
2434 </div>
2435 <p>
2436 The covar-samp function returns the sample covariance between a set of
2437 dependent and independent variables. Example:
2438 </p>
2439 </div>
2440 </div>
2441
2442 <div id="outline-container-sql-op-string-agg" class="outline-3">
2443 <h3 id="sql-op-string-agg">sql-op :string-agg (&amp;rest args)</h3>
2444 <div class="outline-text-3" id="text-sql-op-string-agg">
2445 <p>
2446 String-agg allows you to concatenate strings using different types of
2447 delimiter symbols. Allowable optional keyword parameters are :distinct,
2448 :order-by and :filter Note that order-by in string-agg requires
2449 postgresql 9.0 or later. Filter requires postgresql 9.4 or later.
2450 See tests.lisp for more examples.
2451 </p>
2452 <div class="org-src-container">
2453 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:string-agg</span> 'bp.step-type \",\" )
2454 'step-summary)
2455 <span style="color: #23d7d7;">:from</span> 'business-process))
2456
2457 (query (<span style="color: #23d7d7;">:select</span> 'mid (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:string-agg</span> 'y \",\" <span style="color: #23d7d7;">:distinct</span> <span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:desc</span> 'y))
2458 'words)
2459 <span style="color: #23d7d7;">:from</span> 'moves))
2460
2461 (query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:string-agg</span> 'name <span style="color: #e67128;">","</span> <span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:desc</span> 'name) <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:&lt;</span> 'id 4))
2462 <span style="color: #23d7d7;">:from</span> 'employee))
2463 </pre>
2464 </div>
2465 </div>
2466 </div>
2467
2468 <div id="outline-container-sql-op-array-agg" class="outline-3">
2469 <h3 id="sql-op-array-agg">sql-op :array-agg (&amp;rest args)</h3>
2470 <div class="outline-text-3" id="text-sql-op-array-agg">
2471 <p>
2472 Array-agg returns a list of values concatenated into an arrays.
2473 Allowable optional keyword parameters are :distinct, :order-by
2474 and :filter.
2475 </p>
2476
2477 <p>
2478 Note that order-by in array-agg requires postgresql 9.0 or later.
2479 Filter requires postgresql 9.4 or later. See <a href="array-notes.html">array-notes.html</a> for more
2480 detailed notes on the use of arrays.
2481 </p>
2482
2483 <p>
2484 Example with Filter:
2485 </p>
2486 <div class="org-src-container">
2487 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'g.id
2488 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:array-agg</span> 'g.users <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 'g.canonical \"Y\"))
2489 'canonical-users)
2490 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:array-agg</span> 'g.users <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 'g.canonical \"N\"))
2491 'non-canonical-users)
2492 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'groups 'g)
2493 <span style="color: #23d7d7;">:group-by</span> 'g.id))
2494 </pre>
2495 </div>
2496 </div>
2497 </div>
2498
2499 <div id="outline-container-sql-op-mode" class="outline-3">
2500 <h3 id="sql-op-mode">sql-op :mode (&amp;rest args)</h3>
2501 <div class="outline-text-3" id="text-sql-op-mode">
2502 <p>
2503 Mode is used to find the most frequent input value in a group.
2504 See e.g. <a href="https://www.postgresql.org/docs/10/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE">https://www.postgresql.org/docs/10/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE</a>
2505 and article at <a href="https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function">https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function</a>
2506 </p>
2507 <div class="org-src-container">
2508 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:mode</span> 'items)
2509 <span style="color: #23d7d7;">:from</span> 'item-table))
2510 </pre>
2511 </div>
2512 </div>
2513 </div>
2514
2515 <div id="outline-container-sql-op-regr-avgx" class="outline-3">
2516 <h3 id="sql-op-regr-avgx">sql-op :regr_avgx (y x)</h3>
2517 <div class="outline-text-3" id="text-sql-op-regr-avgx">
2518 <p>
2519 The regr_avgx function returns the average of the independent variable
2520 (sum(X)/N) Example:
2521 </p>
2522 <div class="org-src-container">
2523 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_avgx</span> 'height 'weight)
2524 <span style="color: #23d7d7;">:from</span> 'people))
2525 </pre>
2526 </div>
2527 </div>
2528 </div>
2529
2530 <div id="outline-container-sql-op-regr-avgy" class="outline-3">
2531 <h3 id="sql-op-regr-avgy">sql-op :regr_avgy (y x)</h3>
2532 <div class="outline-text-3" id="text-sql-op-regr-avgy">
2533 <p>
2534 The regr_avgy function returns the average of the dependent variable
2535 (sum(Y)/N). Example:
2536 </p>
2537 <div class="org-src-container">
2538 <pre class="src src-lisp">
2539 </pre>
2540 </div>
2541 <p>
2542 (query (:select (:regr_avgy 'height 'weight)
2543 :from 'people))
2544 </p>
2545 </div>
2546 </div>
2547 <div id="outline-container-sql-op-regr-count" class="outline-3">
2548 <h3 id="sql-op-regr-count">sql-op :regr_count (y x)</h3>
2549 <div class="outline-text-3" id="text-sql-op-regr-count">
2550 <p>
2551 The regr_count function returns the number of input rows in which both
2552 expressions are nonnull. Example:
2553 </p>
2554 <div class="org-src-container">
2555 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_count</span> 'height 'weight)
2556 <span style="color: #23d7d7;">:from</span> 'people))
2557 </pre>
2558 </div>
2559 </div>
2560 </div>
2561
2562 <div id="outline-container-sql-op-regr-intercept" class="outline-3">
2563 <h3 id="sql-op-regr-intercept">sql-op :regr_intercept (y x)</h3>
2564 <div class="outline-text-3" id="text-sql-op-regr-intercept">
2565 <p>
2566 The regr_intercept function returns the y-intercept of the least-squares-fit
2567 linear equation determined by the (X, Y) pairs. Example:
2568 </p>
2569 <div class="org-src-container">
2570 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_intercept</span> 'height 'weight)
2571 <span style="color: #23d7d7;">:from</span> 'people))
2572 </pre>
2573 </div>
2574 </div>
2575 </div>
2576
2577 <div id="outline-container-sql-op-regr-r2" class="outline-3">
2578 <h3 id="sql-op-regr-r2">sql-op :regr_r2 (y x)</h3>
2579 <div class="outline-text-3" id="text-sql-op-regr-r2">
2580 <p>
2581 The regr_r2 function returns the square of the correlation coefficient. Example:
2582 </p>
2583 <div class="org-src-container">
2584 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_r2</span> 'height 'weight)
2585 <span style="color: #23d7d7;">:from</span> 'people))
2586 </pre>
2587 </div>
2588 </div>
2589 </div>
2590
2591 <div id="outline-container-sql-op-regr-slope" class="outline-3">
2592 <h3 id="sql-op-regr-slope">sql-op :regr_slope (y x)</h3>
2593 <div class="outline-text-3" id="text-sql-op-regr-slope">
2594 <p>
2595 The regr_slope function returns the slope of the least-squares-fit linear
2596 equation determined by the (X, Y) pairs. Example:
2597 </p>
2598 <div class="org-src-container">
2599 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_slope</span> 'height 'weight)
2600 <span style="color: #23d7d7;">:from</span> 'people))
2601 </pre>
2602 </div>
2603 </div>
2604 </div>
2605
2606 <div id="outline-container-sql-op-regr-sxx" class="outline-3">
2607 <h3 id="sql-op-regr-sxx">sql-op :regr_sxx (y x)</h3>
2608 <div class="outline-text-3" id="text-sql-op-regr-sxx">
2609 <p>
2610 The regr_sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares” of
2611 the independent variable). Example:
2612 </p>
2613 <div class="org-src-container">
2614 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_sxx</span> 'height 'weight)
2615 <span style="color: #23d7d7;">:from</span> 'people))
2616 </pre>
2617 </div>
2618 </div>
2619 </div>
2620
2621 <div id="outline-container-sql-op-regr-sxy" class="outline-3">
2622 <h3 id="sql-op-regr-sxy">sql-op :regr_sxy (y x)</h3>
2623 <div class="outline-text-3" id="text-sql-op-regr-sxy">
2624 <p>
2625 The regr_sxy function returns the sum(X*Y) - sum(X) * sum(Y)/N (“sum of products”
2626 of independent times dependent variable). Example:
2627 </p>
2628 <div class="org-src-container">
2629 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_sxy</span> 'height 'weight)
2630 <span style="color: #23d7d7;">:from</span> 'people))
2631 </pre>
2632 </div>
2633 </div>
2634 </div>
2635
2636 <div id="outline-container-sql-op-regr-syy" class="outline-3">
2637 <h3 id="sql-op-regr-syy">sql-op :regr_syy (y x)</h3>
2638 <div class="outline-text-3" id="text-sql-op-regr-syy">
2639 <p>
2640 The regr_syy function returns the sum(Y^2) - sum(Y)^2/N (“sum of squares”
2641 of the dependent variable). Example:
2642 </p>
2643 <div class="org-src-container">
2644 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_syy</span> 'salary 'age)
2645 <span style="color: #23d7d7;">:from</span> 'employee))
2646 </pre>
2647 </div>
2648 </div>
2649 </div>
2650
2651 <div id="outline-container-sql-op-stddev" class="outline-3">
2652 <h3 id="sql-op-stddev">sql-op :stddev (&amp;rest args)</h3>
2653 <div class="outline-text-3" id="text-sql-op-stddev">
2654 <p>
2655 The stddev function returns the the sample standard deviation of the input
2656 values. It is a historical alias for stddev-samp. Example:
2657 </p>
2658 <div class="org-src-container">
2659 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev</span> 'salary)
2660 <span style="color: #23d7d7;">:from</span> 'employee))
2661 </pre>
2662 </div>
2663 </div>
2664 </div>
2665
2666 <div id="outline-container-sql-op-stddev-pop" class="outline-3">
2667 <h3 id="sql-op-stddev-pop">sql-op :stddev-pop (&amp;rest args)</h3>
2668 <div class="outline-text-3" id="text-sql-op-stddev-pop">
2669 <p>
2670 The stddev-pop function returns the population standard deviation of the
2671 input values. Example:
2672 </p>
2673 <div class="org-src-container">
2674 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev-pop</span> 'salary)
2675 <span style="color: #23d7d7;">:from</span> 'employee))
2676 </pre>
2677 </div>
2678 </div>
2679 </div>
2680
2681 <div id="outline-container-sql-op-stddev-samp" class="outline-3">
2682 <h3 id="sql-op-stddev-samp">sql-op :stddev-samp (&amp;rest args)</h3>
2683 <div class="outline-text-3" id="text-sql-op-stddev-samp">
2684 <p>
2685 The stddev-samp function returns the sample standard deviation of the
2686 input values. Example:
2687 </p>
2688 <div class="org-src-container">
2689 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev-samp</span> 'salary)
2690 <span style="color: #23d7d7;">:from</span> 'employee))
2691 </pre>
2692 </div>
2693 </div>
2694 </div>
2695
2696 <div id="outline-container-sql-op-variance" class="outline-3">
2697 <h3 id="sql-op-variance">sql-op :variance (&amp;rest args)</h3>
2698 <div class="outline-text-3" id="text-sql-op-variance">
2699 <p>
2700 Variance is a historical alias for var_samp. The variance function returns
2701 the sample variance of the input values (square of the sample standard deviation).
2702 Example:
2703 </p>
2704 <div class="org-src-container">
2705 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:variance</span> 'salary)
2706 <span style="color: #23d7d7;">:from</span> 'employee))
2707 </pre>
2708 </div>
2709 </div>
2710 </div>
2711
2712 <div id="outline-container-sql-op-var-pop" class="outline-3">
2713 <h3 id="sql-op-var-pop">sql-op :var-pop (&amp;rest args)</h3>
2714 <div class="outline-text-3" id="text-sql-op-var-pop">
2715 <p>
2716 The var-pop function returns the population variance of the input values
2717 (square of the population standard deviation). Example:
2718 </p>
2719 <div class="org-src-container">
2720 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:var-pop</span> 'salary)
2721 <span style="color: #23d7d7;">:from</span> 'employee)
2722 <span style="color: #23d7d7;">:single</span>)
2723 </pre>
2724 </div>
2725 </div>
2726 </div>
2727
2728 <div id="outline-container-sql-op-var-samp" class="outline-3">
2729 <h3 id="sql-op-var-samp">sql-op :var-samp (&amp;rest args)</h3>
2730 <div class="outline-text-3" id="text-sql-op-var-samp">
2731 <p>
2732 The var-samp function returns the sample variance of the input values
2733 (square of the sample standard deviation). Example:
2734 </p>
2735 <div class="org-src-container">
2736 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:var-samp</span> 'salary)
2737 <span style="color: #23d7d7;">:from</span> 'employee)
2738 <span style="color: #23d7d7;">:single</span>)
2739 </pre>
2740 </div>
2741
2742 <p>
2743 Window Functions
2744 </p>
2745 </div>
2746 </div>
2747 <div id="outline-container-org2555f16" class="outline-3">
2748 <h3 id="org2555f16">sql-op :range-between (&amp;rest args)</h3>
2749 <div class="outline-text-3" id="text-org2555f16">
2750 <p>
2751 Range-between allows window functions to apply to different segments of a result set.
2752 It accepts the following keywords: :order-by, :rows-between, :range-between,
2753 :unbounded-preceding, :current-row and :unbounded-following. Use of :preceding or
2754 :following will generate errors.
2755 See <a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS</a> for Postgresql documentation on usage.
2756 </p>
2757
2758 <p>
2759 An example which calculates a running total could look like this:
2760 </p>
2761 <div class="org-src-container">
2762 <pre class="src src-lisp">(query
2763 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'country 'country-name)
2764 (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
2765 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
2766 (<span style="color: #23d7d7;">:range-between</span> <span style="color: #23d7d7;">:order-by</span> 'country
2767 <span style="color: #23d7d7;">:unbounded-preceding</span> <span style="color: #23d7d7;">:current-row</span>))
2768 'global-population)
2769 <span style="color: #23d7d7;">:from</span> 'population
2770 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'iso2)
2771 (<span style="color: #23d7d7;">:=</span> 'year 1976))))
2772 </pre>
2773 </div>
2774 </div>
2775 </div>
2776
2777 <div id="outline-container-orgfb601b4" class="outline-3">
2778 <h3 id="orgfb601b4">sql-op :rows-between (&amp;rest args)</h3>
2779 <div class="outline-text-3" id="text-orgfb601b4">
2780 <p>
2781 Rows-between allows window functions to apply to different segments of a result set.
2782 It accepts the following keywords:
2783 :order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
2784 :current-row, :unbounded-following and :following. See <a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS</a> for Postgresql documentation on usage.
2785 </p>
2786
2787 <p>
2788 An example could look like this :
2789 </p>
2790 <div class="org-src-container">
2791 <pre class="src src-lisp">(query
2792 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'country 'country-name)
2793 (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
2794 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
2795 (<span style="color: #23d7d7;">:rows-between</span> <span style="color: #23d7d7;">:order-by</span> 'country <span style="color: #23d7d7;">:preceding</span> 2 <span style="color: #23d7d7;">:following</span> 2))
2796 'global-population)
2797 <span style="color: #23d7d7;">:from</span> 'population
2798 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'iso2)
2799 (<span style="color: #23d7d7;">:=</span> 'year 1976))))
2800 </pre>
2801 </div>
2802 </div>
2803 </div>
2804
2805 <div id="outline-container-sql-op-over" class="outline-3">
2806 <h3 id="sql-op-over">sql-op :over (form &amp;rest args)</h3>
2807 <div class="outline-text-3" id="text-sql-op-over">
2808 <p>
2809 Over, partition-by and window are so-called window functions. A window
2810 function performs a calculation across a set of table rows that are
2811 somehow related to the current row and adds that as an additional column to
2812 the result. The following collects individual salaries and the total salaries.
2813 </p>
2814 <div class="org-src-container">
2815 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'salary (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'salary))
2816 <span style="color: #23d7d7;">:from</span> 'empsalary))
2817 </pre>
2818 </div>
2819 <p>
2820 A more complicated version that calculates a running total might look like:
2821 </p>
2822 <div class="org-src-container">
2823 <pre class="src src-lisp">(query
2824 (<span style="color: #23d7d7;">:select</span> 'name
2825 (<span style="color: #23d7d7;">:as</span> 'salary 'individual-salary)
2826 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'salary)
2827 (<span style="color: #23d7d7;">:range-between</span> <span style="color: #23d7d7;">:order-by</span> 'name <span style="color: #23d7d7;">:unbounded-preceding</span>
2828 <span style="color: #23d7d7;">:current-row</span>))
2829 'running-total-salary)
2830 <span style="color: #23d7d7;">:from</span> 'empsalary))
2831 </pre>
2832 </div>
2833 </div>
2834 </div>
2835
2836 <div id="outline-container-sql-op-partition-by" class="outline-3">
2837 <h3 id="sql-op-partition-by">sql-op :partition-by (&amp;rest args)</h3>
2838 <div class="outline-text-3" id="text-sql-op-partition-by">
2839 <p>
2840 Args is a list of one or more columns to partition by, optionally
2841 followed by other keywords. Partition-by accepts the following keywords:
2842 :order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
2843 :current-row, :unbounded-following and :following. See <a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS">https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS</a> for Postgresql documentation on usage.
2844 </p>
2845 <div class="org-src-container">
2846 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'depname 'subdepname 'empno 'salary
2847 (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:avg</span> 'salary)
2848 (<span style="color: #23d7d7;">:partition-by</span> 'depname 'subdepname))
2849 <span style="color: #23d7d7;">:from</span> 'empsalary))
2850 </pre>
2851 </div>
2852
2853 <p>
2854 Note the use of :order-by without parens:
2855 </p>
2856 <div class="org-src-container">
2857 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'depname 'empno 'salary
2858 (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:rank</span>)
2859 (<span style="color: #23d7d7;">:partition-by</span> 'depname <span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:desc</span> 'salary)))
2860 <span style="color: #23d7d7;">:from</span> 'empsalary))
2861 </pre>
2862 </div>
2863
2864 <p>
2865 The following example shows a query for country population in 1976 with running total population by region.
2866 </p>
2867 <div class="org-src-container">
2868 <pre class="src src-lisp">(query
2869 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'population.country 'country-name)
2870 (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
2871 'region-name
2872 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
2873 (<span style="color: #23d7d7;">:partition-by</span> 'region-name <span style="color: #23d7d7;">:order-by</span> 'region-name
2874 <span style="color: #23d7d7;">:rows-between</span> <span style="color: #23d7d7;">:unbounded-preceding</span> <span style="color: #23d7d7;">:current-row</span>))
2875 'regional-population)
2876 <span style="color: #23d7d7;">:from</span> 'population
2877 <span style="color: #23d7d7;">:inner-join</span> 'regions
2878 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'population.iso3 'regions.iso3)
2879 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'population.iso2)
2880 (<span style="color: #23d7d7;">:=</span> 'year 1976))))
2881 </pre>
2882 </div>
2883 </div>
2884 </div>
2885
2886 <div id="outline-container-sql-op-window" class="outline-3">
2887 <h3 id="sql-op-window">sql-op :window (form)</h3>
2888 <div class="outline-text-3" id="text-sql-op-window">
2889 <div class="org-src-container">
2890 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'salary) 'w)
2891 (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:avg</span> 'salary) 'w)
2892 <span style="color: #23d7d7;">:from</span> 'empsalary <span style="color: #23d7d7;">:window</span>
2893 (<span style="color: #23d7d7;">:as</span> 'w (<span style="color: #23d7d7;">:partition-by</span> 'depname <span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:desc</span> 'salary)))))
2894 </pre>
2895 </div>
2896 </div>
2897 </div>
2898
2899 <div id="outline-container-sql-op-with" class="outline-3">
2900 <h3 id="sql-op-with">sql-op :with (&amp;rest args)</h3>
2901 <div class="outline-text-3" id="text-sql-op-with">
2902 <p>
2903 With provides a way to write auxillary statements for use in a larger query,
2904 often referred to as Common Table Expressions or CTEs.
2905 </p>
2906 <div class="org-src-container">
2907 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:with</span> (<span style="color: #23d7d7;">:as</span> 'upd
2908 (<span style="color: #23d7d7;">:parens</span>
2909 (<span style="color: #23d7d7;">:update</span> 'employees <span style="color: #23d7d7;">:set</span> 'sales-count (<span style="color: #23d7d7;">:+</span> 'sales-count 1)
2910 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id
2911 (<span style="color: #23d7d7;">:select</span> 'sales-person
2912 <span style="color: #23d7d7;">:from</span> 'accounts
2913 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'name <span style="color: #e67128;">"Acme Corporation"</span>)))
2914 <span style="color: #23d7d7;">:returning</span> '*)))
2915 (<span style="color: #23d7d7;">:insert-into</span> 'employees-log
2916 (<span style="color: #23d7d7;">:select</span> '* (<span style="color: #23d7d7;">:current-timestamp</span>) <span style="color: #23d7d7;">:from</span>
2917 'upd))))
2918 </pre>
2919 </div>
2920 </div>
2921 </div>
2922
2923 <div id="outline-container-sql-op-with-recursive" class="outline-3">
2924 <h3 id="sql-op-with-recursive">sql-op :with-recursive (&amp;rest args)</h3>
2925 <div class="outline-text-3" id="text-sql-op-with-recursive">
2926 <p>
2927 Recursive modifier to a WITH statement, allowing the query to refer to its own output.
2928 </p>
2929 <div class="org-src-container">
2930 <pre class="src src-lisp">(query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
2931 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:t1</span> 'n)
2932 (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> 1))
2933 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'n 1)
2934 <span style="color: #23d7d7;">:from</span> 't1
2935 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'n 100))))
2936 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:sum</span> 'n) <span style="color: #23d7d7;">:from</span> 't1)))
2937
2938 (query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
2939 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:included_parts</span> 'sub-part 'part 'quantity)
2940 (<span style="color: #23d7d7;">:union-all</span>
2941 (<span style="color: #23d7d7;">:select</span> 'sub-part 'part 'quantity
2942 <span style="color: #23d7d7;">:from</span> 'parts
2943 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'part <span style="color: #e67128;">"our-product"</span>))
2944 (<span style="color: #23d7d7;">:select</span> 'p.sub-part 'p.part 'p.quantity
2945 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'included-parts 'pr)
2946 (<span style="color: #23d7d7;">:as</span> 'parts 'p)
2947 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'p.part 'pr.sub-part) )))
2948 (<span style="color: #23d7d7;">:select</span> 'sub-part (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'quantity) 'total-quantity)
2949 <span style="color: #23d7d7;">:from</span> 'included-parts
2950 <span style="color: #23d7d7;">:group-by</span> 'sub-part)))
2951
2952 (query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
2953 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:search-graph</span> 'id 'link 'data 'depth)
2954 (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data 1
2955 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g))
2956 (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data (<span style="color: #23d7d7;">:+</span> 'sg.depth 1)
2957 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g) (<span style="color: #23d7d7;">:as</span> 'search-graph 'sg)
2958 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'g.id 'sg.link))))
2959 (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'search-graph)))
2960
2961 (query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
2962 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:search-graph</span> 'id 'link 'data'depth 'path 'cycle)
2963 (<span style="color: #23d7d7;">:union-all</span>
2964 (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data 1
2965 (<span style="color: #23d7d7;">:[]</span> 'g.f1 'g.f2) nil
2966 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g))
2967 (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data (<span style="color: #23d7d7;">:+</span> 'sg.depth 1)
2968 (:|| 'path (<span style="color: #23d7d7;">:row</span> 'g.f1 'g.f2))
2969 (<span style="color: #23d7d7;">:=</span> (<span style="color: #23d7d7;">:row</span> 'g.f1 'g.f2)
2970 (<span style="color: #23d7d7;">:any*</span> 'path))
2971 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g)
2972 (<span style="color: #23d7d7;">:as</span> 'search-graph 'sg)
2973 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'g.id 'sg.link)
2974 (<span style="color: #23d7d7;">:not</span> 'cycle)))))
2975 (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'search-graph)))
2976 </pre>
2977 </div>
2978 </div>
2979 </div>
2980 <div id="outline-container-org6f9c3d3" class="outline-3">
2981 <h3 id="org6f9c3d3">sql-op :with-ordinality, :with-ordinality-as</h3>
2982 <div class="outline-text-3" id="text-org6f9c3d3">
2983 <p>
2984 Selects can use :with-ordinality or :with-ordinality-as parameters. Postgresql will give the new ordinality column the name of ordinality. :with-ordinality-as allows you to set different names for the columns in the result set.
2985 </p>
2986 <div class="org-src-container">
2987 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '*
2988 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:generate-series</span> 4 1 -1)
2989 <span style="color: #23d7d7;">:with-ordinality</span>))
2990
2991 (query (<span style="color: #23d7d7;">:select</span> 't1.*
2992 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:json-object-keys</span> <span style="color: #e67128;">"{\"a1\":\"1\",\"a2\":\"2\",\"a3\":\"3\"}"</span>)
2993 <span style="color: #23d7d7;">:with-ordinality-as</span> (<span style="color: #23d7d7;">:t1</span> 'keys 'n)
2994 </pre>
2995 </div>
2996 </div>
2997 </div>
2998 </div>
2999
3000 <div id="outline-container-table-functions" class="outline-2">
3001 <h2 id="table-functions">Table Functions</h2>
3002 <div class="outline-text-2" id="text-table-functions">
3003 </div>
3004 <div id="outline-container-sql-op-for-update" class="outline-3">
3005 <h3 id="sql-op-for-update">sql-op :for-update (query &amp;key of nowait)</h3>
3006 <div class="outline-text-3" id="text-sql-op-for-update">
3007 <p>
3008 Locks the selected rows against concurrent updates. This will prevent the
3009 rows from being modified or deleted by other transactions until the current
3010 transaction ends. The :of keyword should be followed by one or more table
3011 names. If provided, PostgreSQL will lock these tables instead of the ones
3012 detected in the select statement. The :nowait keyword should be provided
3013 by itself (with no argument attached to it), after all the :of arguments.
3014 If :nowait is provided, PostgreSQL will throw an error if a table cannot be
3015 locked immediately, instead of pausing until it's possible.
3016 </p>
3017 <div class="org-src-container">
3018 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:for-update</span> (<span style="color: #23d7d7;">:select</span> <span style="color: #23d7d7;">:*</span> <span style="color: #23d7d7;">:from</span> 'foo 'bar 'baz) <span style="color: #23d7d7;">:of</span> 'bar 'baz <span style="color: #23d7d7;">:nowait</span>))
3019 </pre>
3020 </div>
3021 </div>
3022 </div>
3023
3024 <div id="outline-container-sql-op-for-share" class="outline-3">
3025 <h3 id="sql-op-for-share">sql-op :for-share (query &amp;key of nowait)</h3>
3026 <div class="outline-text-3" id="text-sql-op-for-share">
3027 <p>
3028 Similar to :for-update, except it acquires a shared lock on the table,
3029 allowing other transactions to perform :for-share selects on the locked
3030 tables.
3031 </p>
3032 </div>
3033 </div>
3034
3035 <div id="outline-container-sql-op-insert-into" class="outline-3">
3036 <h3 id="sql-op-insert-into">sql-op :insert-into (table &amp;rest rest)</h3>
3037 <div class="outline-text-3" id="text-sql-op-insert-into">
3038 <p>
3039 You can use insert-into when you are:
3040 </p>
3041
3042 <ol class="org-ol">
3043 <li>Inserting from a select clause and you do not need to specify specific columns:</li>
3044 </ol>
3045 <div class="org-src-container">
3046 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
3047 (<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))
3048 </pre>
3049 </div>
3050 <ol class="org-ol">
3051 <li>Inserting from a select clause and you specifying the columns which will be filled with values from the select clause</li>
3052 </ol>
3053 <div class="org-src-container">
3054 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 't11
3055 <span style="color: #23d7d7;">:columns</span> 'region 'subregion 'country
3056 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'region-name 'region)
3057 (<span style="color: #23d7d7;">:as</span> 'sub-region-name 'subregion)
3058 'country
3059 <span style="color: #23d7d7;">:from</span> 'regions)))
3060 </pre>
3061 </div>
3062 <p>
3063 or
3064 </p>
3065 <ol class="org-ol">
3066 <li>You are alternating specific columns and values for a single row:</li>
3067 </ol>
3068 <div class="org-src-container">
3069 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'my-table <span style="color: #23d7d7;">:set</span> 'field-1 42 'field-2 <span style="color: #e67128;">"foobar"</span>))
3070 </pre>
3071 </div>
3072 <p>
3073 You can use parameterized variables in the insert statement.
3074 </p>
3075 <div class="org-src-container">
3076 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((name <span style="color: #e67128;">"test-cat4"</span>))
3077 (query (<span style="color: #23d7d7;">:insert-into</span> 'categories <span style="color: #23d7d7;">:set</span> 'name '$1) name))
3078 </pre>
3079 </div>
3080 <p>
3081 It is possible to add :returning, followed by a list of field names or
3082 expressions, at the end of the :insert-into form. This will cause the
3083 query to return the values of these expressions as a single row.
3084 </p>
3085 <div class="org-src-container">
3086 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'my-table
3087 <span style="color: #23d7d7;">:set</span> 'field-1 42 'field-2 <span style="color: #e67128;">"foobar"</span>
3088 <span style="color: #23d7d7;">:returning</span> '*))
3089
3090 (query (<span style="color: #23d7d7;">:insert-into</span> 'my-table
3091 <span style="color: #23d7d7;">:set</span> 'field-1 42 'field-2 <span style="color: #e67128;">"foobar"</span>
3092 <span style="color: #23d7d7;">:returning</span> 'id))
3093 </pre>
3094 </div>
3095 <p>
3096 In Postgresql versions 9.5 and above, it is possible to add
3097 :on-conflict-do-nothing (if the item already exists, do nothing). If you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing.
3098 </p>
3099 <div class="org-src-container">
3100 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'test-table <span style="color: #23d7d7;">:set</span> 'column-A '$1 'column-B '$2
3101 <span style="color: #23d7d7;">:on-conflict</span> 'column-A <span style="color: #23d7d7;">:do-nothing</span>
3102 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
3103 <span style="color: #23d7d7;">:returning</span> '*)
3104 <span style="color: #e67128;">"c"</span> 37)
3105
3106 (query (<span style="color: #23d7d7;">:insert-into</span> 'test-table <span style="color: #23d7d7;">:set</span> 'column-A '$1 'column-B '$2
3107 <span style="color: #23d7d7;">:on-conflict-do-nothing</span> 'column-A
3108 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
3109 <span style="color: #23d7d7;">:returning</span> '*)
3110 <span style="color: #e67128;">"c"</span> 37)
3111 </pre>
3112 </div>
3113 <p>
3114 If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:
3115 </p>
3116 <div class="org-src-container">
3117 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
3118 <span style="color: #23d7d7;">:set</span> 'c1 <span style="color: #e67128;">"A"</span> 'c2 <span style="color: #e67128;">"B"</span>
3119 <span style="color: #23d7d7;">:overriding-system-value</span>))
3120 </pre>
3121 </div>
3122 <p>
3123 To create what is commonly known as an upsert, use :on-conflict-update
3124 (if the item already exists, update the values)
3125 followed by a list of field names which are checked for the conflict
3126 then using :update-set followed by a list of field names or expressions
3127 following the syntax for updating a table. This is sometimes called
3128 an "upsert". Note that as per the postgresql sql documentation you must
3129 prepend the table name to the column in the where statement if you are updating.
3130 </p>
3131 <div class="org-src-container">
3132 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'test-table <span style="color: #23d7d7;">:set</span> 'column-A '$1 'column-B '$2
3133 <span style="color: #23d7d7;">:on-conflict-update</span> 'column-A
3134 <span style="color: #23d7d7;">:update-set</span> 'column-B '$2
3135 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
3136 <span style="color: #23d7d7;">:returning</span> '*)
3137 <span style="color: #e67128;">"c"</span> 37)
3138 </pre>
3139 </div>
3140 <p>
3141 If the destination table has identity columns and you want to override those identity columns with specific values, you should specify :overriding-system-value.
3142 </p>
3143 <div class="org-src-container">
3144 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'test-table
3145 <span style="color: #23d7d7;">:set</span> 'column-A '$1 'column-B '$2
3146 <span style="color: #23d7d7;">:overriding-system-value</span>
3147 <span style="color: #23d7d7;">:on-conflict-update</span> 'column-A
3148 <span style="color: #23d7d7;">:update-set</span> 'column-B '$2
3149 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
3150 <span style="color: #23d7d7;">:returning</span> '*)
3151 <span style="color: #e67128;">"c"</span> 37)
3152 </pre>
3153 </div>
3154 <p>
3155 If you are selecting from another table which has column names the same as your destination table and you want to keep the destination table's identity column values, then you can use :overriding-user-value. E.g.
3156 </p>
3157 <div class="org-src-container">
3158 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
3159 <span style="color: #23d7d7;">:overriding-user-value</span>
3160 (<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))
3161 </pre>
3162 </div>
3163 </div>
3164 </div>
3165 <div id="outline-container-sql-op-insert-rows-into" class="outline-3">
3166 <h3 id="sql-op-insert-rows-into">sql-op :insert-rows-into (table &amp;rest rest)</h3>
3167 <div class="outline-text-3" id="text-sql-op-insert-rows-into">
3168 <p>
3169 Insert-rows-into provides the ability to insert multiple rows into a table without using a select statement. (Insert-rows-into keeps the VALUES key word in the resulting sql. If you do use a select statement, Postgresql requires that it only return one row.)
3170 </p>
3171
3172 <p>
3173 Specify the columns first with the keyword :columns then provide a list of lists of the values as a parameter to the keyword :values. Example:
3174 </p>
3175 <div class="org-src-container">
3176 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'my-table
3177 <span style="color: #23d7d7;">:columns</span> 'field-1 'field-2
3178 <span style="color: #23d7d7;">:values</span> '((42 <span style="color: #e67128;">"foobar"</span>) (23 <span style="color: #e67128;">"foobaz"</span>))))
3179 </pre>
3180 </div>
3181 <p>
3182 An example using a select statement returning one row:
3183 </p>
3184 <div class="org-src-container">
3185 <pre class="src src-lisp">(squery (<span style="color: #23d7d7;">:insert-rows-into</span> 't6
3186 <span style="color: #23d7d7;">:columns</span> 'tags
3187 <span style="color: #23d7d7;">:values</span> '(((<span style="color: #23d7d7;">:select</span> 'id
3188 <span style="color: #23d7d7;">:from</span> 't5)))))
3189 </pre>
3190 </div>
3191 <p>
3192 If you will use the default columns, this can be simplified and the :columns
3193 parameters can be dropped. Example:
3194 </p>
3195 <div class="org-src-container">
3196 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'my-table
3197 <span style="color: #23d7d7;">:values</span> '((42 <span style="color: #e67128;">"foobar"</span>) (23 <span style="color: #e67128;">"foobaz"</span>))))
3198 </pre>
3199 </div>
3200 <p>
3201 If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:
3202 </p>
3203 <div class="org-src-container">
3204 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'table1
3205 <span style="color: #23d7d7;">:columns</span> 'c1 'c2
3206 <span style="color: #23d7d7;">:overriding-system-value</span>
3207 <span style="color: #23d7d7;">:values</span> '((1 <span style="color: #e67128;">"a"</span>) (2 <span style="color: #e67128;">"b"</span>))))
3208
3209 (query (<span style="color: #23d7d7;">:insert-rows-into</span> 'table1
3210 <span style="color: #23d7d7;">:overriding-system-value</span>
3211 <span style="color: #23d7d7;">:values</span> '(((<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))))
3212 </pre>
3213 </div>
3214 <p>
3215 Similarly to :insert-into, :insert-rows-into allows the "upsert" use of :on-conflict. Again, if you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing. The following example uses :on-conflict-do-nothing
3216 </p>
3217 <div class="org-src-container">
3218 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
3219 <span style="color: #23d7d7;">:columns</span> 'did 'dname
3220 <span style="color: #23d7d7;">:values</span> '((10 <span style="color: #e67128;">"Conrad International"</span>))
3221 <span style="color: #23d7d7;">:on-conflict-do-nothing</span>
3222 <span style="color: #23d7d7;">:where</span> 'is-active))
3223
3224 (query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
3225 <span style="color: #23d7d7;">:columns</span> 'did 'dname
3226 <span style="color: #23d7d7;">:values</span> '((10 <span style="color: #e67128;">"Conrad International"</span>))
3227 <span style="color: #23d7d7;">:on-conflict</span> 'did
3228 <span style="color: #23d7d7;">:do-nothing</span>
3229 <span style="color: #23d7d7;">:where</span> 'is-active))
3230 </pre>
3231 </div>
3232 <p>
3233 or :on-conflict-update
3234 </p>
3235 <div class="org-src-container">
3236 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
3237 <span style="color: #23d7d7;">:columns</span> 'did 'dname
3238 <span style="color: #23d7d7;">:values</span> '((5 <span style="color: #e67128;">"Gizmo Transglobal"</span>) (6 <span style="color: #e67128;">"Associated Computing Inc."</span>))
3239 <span style="color: #23d7d7;">:on-conflict-update</span> 'did
3240 <span style="color: #23d7d7;">:update-set</span> 'dname 'excluded.dname))
3241 </pre>
3242 </div>
3243 <p>
3244 You can use :on-conflict-on-constraint to check for conflicts on constraints.
3245 </p>
3246 <div class="org-src-container">
3247 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'test <span style="color: #23d7d7;">:columns</span> 'some-key 'some-val
3248 <span style="color: #23d7d7;">:values</span> '((<span style="color: #e67128;">"a"</span> 3) (<span style="color: #e67128;">"b"</span> 6) (<span style="color: #e67128;">"c"</span> 7))
3249 <span style="color: #23d7d7;">:on-conflict-on-constraint</span> 'somekey
3250 <span style="color: #23d7d7;">:do-nothing</span>
3251 <span style="color: #23d7d7;">:returning</span> '*))
3252
3253 (query (<span style="color: #23d7d7;">:insert-rows-into</span> 'test <span style="color: #23d7d7;">:columns</span> 'some-key 'some-val
3254 <span style="color: #23d7d7;">:values</span> '((<span style="color: #e67128;">"a"</span> 2) (<span style="color: #e67128;">"b"</span> 6) (<span style="color: #e67128;">"c"</span> 7))
3255 <span style="color: #23d7d7;">:on-conflict-on-constraint</span> 'somekey
3256 <span style="color: #23d7d7;">:update-set</span> 'some-val 'excluded.some-val
3257 <span style="color: #23d7d7;">:returning</span> '*))
3258 </pre>
3259 </div>
3260 </div>
3261 </div>
3262 <div id="outline-container-sql-op-update" class="outline-3">
3263 <h3 id="sql-op-update">sql-op :update (table &amp;rest rest)</h3>
3264 <div class="outline-text-3" id="text-sql-op-update">
3265 <p>
3266 Update values in a table. There are two ways to update the values
3267 </p>
3268
3269 <p>
3270 The first method uses the keyword :set and any number of alternating field names and values, like
3271 for :insert-into. Next comes the optional keyword :from, followed by at
3272 least one table name and then any number of join statements, like for
3273 :select. After the joins, an optional :where keyword followed by the condition,
3274 and :returning keyword followed by a list of field names or expressions
3275 indicating values to be returned as query result.
3276 </p>
3277 <div class="org-src-container">
3278 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:update</span> 'weather
3279 <span style="color: #23d7d7;">:set</span> 'temp-lo (<span style="color: #23d7d7;">:+</span> 'temp-lo 1)
3280 'temp-hi (<span style="color: #23d7d7;">:+</span> 'temp-lo 15)
3281 'prcp <span style="color: #23d7d7;">:default</span>
3282 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'city <span style="color: #e67128;">"San Francisco"</span>)
3283 (<span style="color: #23d7d7;">:=</span> 'date <span style="color: #e67128;">"2003-07-03"</span>))
3284 <span style="color: #23d7d7;">:returning</span> 'temp-lo 'temp-hi 'prcp))
3285 </pre>
3286 </div>
3287 <p>
3288 The second method uses the :columns keyword to specify which columns get created and allows the use of either :set or :select (both of which need to be enclosed in a form) to provide the values, allowing update queries like:
3289 </p>
3290 <div class="org-src-container">
3291 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:update</span> 'weather
3292 <span style="color: #23d7d7;">:columns</span> 'temp-lo 'temp-hi 'prcp
3293 (<span style="color: #23d7d7;">:set</span> (<span style="color: #23d7d7;">:+</span> 'temp-lo 1) (<span style="color: #23d7d7;">:+</span> 'temp-lo 15) <span style="color: #23d7d7;">:DEFAULT</span>)
3294 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'city <span style="color: #e67128;">"San Francisco"</span>)
3295 (<span style="color: #23d7d7;">:=</span> 'date <span style="color: #e67128;">"2003-07-03"</span>))))
3296
3297 (query (<span style="color: #23d7d7;">:update</span> 't1
3298 <span style="color: #23d7d7;">:columns</span> 'database-name 'encoding
3299 (<span style="color: #23d7d7;">:select</span> 'x.datname 'x.encoding
3300 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg-database 'x)
3301 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'x.oid 't1.oid))))
3302 </pre>
3303 </div>
3304 </div>
3305 </div>
3306 <div id="outline-container-sql-op-delete-from" class="outline-3">
3307 <h3 id="sql-op-delete-from">sql-op :delete-from (table &amp;rest rest)</h3>
3308 <div class="outline-text-3" id="text-sql-op-delete-from">
3309 <p>
3310 Delete rows from the named table. Can be given a :where argument followed
3311 by a condition, and a :returning argument, followed by one or more
3312 expressions that should be returned for every deleted row.
3313 </p>
3314 <div class="org-src-container">
3315 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:delete-from</span> 'cd.bookings <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id 5)))
3316 </pre>
3317 </div>
3318 </div>
3319 </div>
3320 <div id="outline-container-sql-op-create-table" class="outline-3">
3321 <h3 id="sql-op-create-table">sql-op :create-table (name (&amp;rest columns) &amp;rest options)</h3>
3322 <div class="outline-text-3" id="text-sql-op-create-table">
3323 <p>
3324 Create a new table. The simplest example would pass two parameters,
3325 the table name and a list of lists providing information for each column.
3326 For example:
3327 </p>
3328 <div class="org-src-container">
3329 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-table</span> 'george ((id <span style="color: #23d7d7;">:type</span> integer))))
3330 </pre>
3331 </div>
3332 <p>
3333 where 'george is the name of the table, it has 1 column named id
3334 which is limited to integers. There are no indexes or keys in this
3335 example.
3336 </p>
3337
3338 <p>
3339 See <a href="create-tables.html">create-tables.html</a> for more detailed examples.
3340 </p>
3341 </div>
3342
3343 <div id="outline-container-sql-op-column-definition-parameters" class="outline-4">
3344 <h4 id="sql-op-column-definition-parameters">Column Definition parameters</h4>
3345 <div class="outline-text-4" id="text-sql-op-column-definition-parameters">
3346 <p>
3347 After the table name a list of column definitions
3348 follows, which are lists that start with a name, followed by one or
3349 more of the following keyword arguments:
3350 </p>
3351
3352 <ul class="org-ul">
3353 <li>:type</li>
3354 </ul>
3355
3356 <p>
3357 This one is required. It specifies the type of the column. Use a type like
3358 (or db-null integer) to specify a column that may have NULL values.
3359 </p>
3360
3361 <ul class="org-ul">
3362 <li>:default</li>
3363 </ul>
3364
3365 <p>
3366 Provides a default value for the field.
3367 </p>
3368
3369 <ul class="org-ul">
3370 <li>:unique</li>
3371 </ul>
3372
3373 <p>
3374 If this argument is non-nil, the values of the column must be unique.
3375 </p>
3376
3377 <ul class="org-ul">
3378 <li>:primary-key</li>
3379 </ul>
3380
3381 <p>
3382 When non-nil, the column is a primary key of the table.
3383 </p>
3384
3385 <ul class="org-ul">
3386 <li>:check</li>
3387 </ul>
3388
3389 <p>
3390 Adds a constraint to this column. The value provided for this argument must
3391 be an S-SQL expression that returns a boolean value. It can refer to other
3392 columns in the table if needed.
3393 </p>
3394
3395 <ul class="org-ul">
3396 <li>:references</li>
3397 </ul>
3398
3399 <p>
3400 Adds a foreign key constraint to this table. The argument provided must be a
3401 list of the form (target &amp;optional on-delete on-update). When target is a
3402 symbol, it names the table to whose primary key this constraint refers. When
3403 it is a list, its first element is the table, and its second element the
3404 column within that table that the key refers to. on-delete and on-update
3405 can be used to specify the actions that must be taken when the row that this
3406 key refers to is deleted or changed. Allowed values are :restrict, :set-null,
3407 </p>
3408
3409 <ul class="org-ul">
3410 <li>:set-default, :cascade, and :no-action.</li>
3411 </ul>
3412 </div>
3413 </div>
3414
3415 <div id="outline-container-sql-op-table-constraints" class="outline-4">
3416 <h4 id="sql-op-table-constraints">Table Constraints</h4>
3417 <div class="outline-text-4" id="text-sql-op-table-constraints">
3418 <p>
3419 After the list of columns, zero or more extra options (table constraints) can
3420 be specified. These are lists starting with one of the following keywords:
3421 </p>
3422
3423 <ul class="org-ul">
3424 <li>:check</li>
3425 </ul>
3426
3427 <p>
3428 Adds a constraint to the table. Takes a single S-SQL expression that produces
3429 a boolean as its argument.
3430 </p>
3431
3432 <ul class="org-ul">
3433 <li>:primary-key</li>
3434 </ul>
3435
3436 <p>
3437 Specifies a primary key for the table. The arguments to this option are the
3438 names of the columns that this key consists of.
3439 </p>
3440
3441 <ul class="org-ul">
3442 <li>:unique</li>
3443 </ul>
3444
3445 <p>
3446 Adds a unique constraint to a group of columns. Again, the arguments are a
3447 list of symbols that indicate the relevant columns.
3448 </p>
3449
3450 <ul class="org-ul">
3451 <li>:foreign-key</li>
3452 </ul>
3453
3454 <p>
3455 Create a foreign key. The arguments should have the form
3456 (columns target &amp;optional on-delete on-update), where columns is a list of
3457 columns that are used by this key, while the rest of the arguments have
3458 the same meaning as they have in the :references option for columns.
3459 Every list can start with :constraint name to create a specifically named
3460 constraint.
3461 </p>
3462
3463 <p>
3464 Note that, unlike most other operators, :create-table expects most of its
3465 arguments to be unquoted symbols. The exception to this is the value
3466 of :check constraints: These must be normal S-SQL expressions, which means
3467 that any column names they contain should be quoted. When programmatically
3468 generating table definitions, sql-compile is usually more practical than
3469 the sql macro.
3470 </p>
3471
3472 <p>
3473 Here is an example of a :create-table form:
3474 </p>
3475 <div class="org-src-container">
3476 <pre class="src src-lisp">(<span style="color: #23d7d7;">:create-table</span> enemy
3477 ((name <span style="color: #23d7d7;">:type</span> string <span style="color: #23d7d7;">:primary-key</span> t)
3478 (age <span style="color: #23d7d7;">:type</span> integer)
3479 (address <span style="color: #23d7d7;">:type</span> (or db-null string) <span style="color: #23d7d7;">:references</span> (important-addresses <span style="color: #23d7d7;">:cascade</span> <span style="color: #23d7d7;">:cascade</span>))
3480 (fatal-weakness <span style="color: #23d7d7;">:type</span> text <span style="color: #23d7d7;">:default</span> <span style="color: #e67128;">"None"</span>)
3481 (identifying-color <span style="color: #23d7d7;">:type</span> (string 20) <span style="color: #23d7d7;">:unique</span> t))
3482 (<span style="color: #23d7d7;">:foreign-key</span> (identifying-color) (colors name))
3483 (<span style="color: #23d7d7;">:constraint</span> enemy-age-check <span style="color: #23d7d7;">:check</span> (<span style="color: #23d7d7;">:&gt;</span> 'age 12)))
3484 </pre>
3485 </div>
3486 <p>
3487 For more detail and examples on building tables
3488 using the s-sql approach, see <a href="create-tables.html">create-tables.html</a>
3489 </p>
3490 </div>
3491 </div>
3492 </div>
3493
3494 <div id="outline-container-sql-op-alter-table" class="outline-3">
3495 <h3 id="sql-op-alter-table">sql-op :alter-table (name action &amp;rest args)</h3>
3496 <div class="outline-text-3" id="text-sql-op-alter-table">
3497 <p>
3498 Alters named table. Currently changing a column's data type is not supported.
3499 The meaning of args depends on action:
3500 </p>
3501
3502 <ul class="org-ul">
3503 <li>:add-column</li>
3504 </ul>
3505
3506 <p>
3507 Adds column to table. args should be a column in the same form as for :create-table.
3508 </p>
3509 <div class="org-src-container">
3510 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:alter-table</span> <span style="color: #e67128;">"packages"</span> <span style="color: #23d7d7;">:add-column</span> 'system-data-p <span style="color: #23d7d7;">:type</span> (or boolean db-null)))
3511 </pre>
3512 </div>
3513
3514 <ul class="org-ul">
3515 <li>:set-default</li>
3516 </ul>
3517
3518 <p>
3519 Adds or changes a default value for a column
3520 </p>
3521 <div class="org-src-container">
3522 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:alter-table</span> 'countries <span style="color: #23d7d7;">:alter-column</span> 'updated-at <span style="color: #23d7d7;">:set-default</span> (<span style="color: #23d7d7;">:now</span>)))
3523 </pre>
3524 </div>
3525
3526 <ul class="org-ul">
3527 <li>:drop-column</li>
3528 </ul>
3529
3530 <p>
3531 Drops a column from the table.
3532 </p>
3533 <div class="org-src-container">
3534 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:alter-table</span> <span style="color: #e67128;">"test-uniq"</span> <span style="color: #23d7d7;">:drop-column</span> 'address))
3535 </pre>
3536 </div>
3537
3538 <ul class="org-ul">
3539 <li>:add-constraint</li>
3540 </ul>
3541
3542 <p>
3543 Adds a named constraint to the table.
3544 </p>
3545 <div class="org-src-container">
3546 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:alter-table</span> <span style="color: #e67128;">"test-uniq"</span> <span style="color: #23d7d7;">:add-constraint</span> silly-key <span style="color: #23d7d7;">:primary-key</span> 'code 'title))
3547
3548 (query (<span style="color: #23d7d7;">:alter-table</span> enemy <span style="color: #23d7d7;">:add-constraint</span> enemy-age-check <span style="color: #23d7d7;">:check</span> (<span style="color: #23d7d7;">:&gt;</span> 'age 21)))
3549 </pre>
3550 </div>
3551
3552 <ul class="org-ul">
3553 <li>:drop-constraint</li>
3554 </ul>
3555
3556 <p>
3557 Drops constraint. First of args should name a constraint to be dropped; second,
3558 optional argument specifies behaviour regarding objects dependent on the
3559 constraint and it may equal :cascade or :restrict.
3560 </p>
3561 <div class="org-src-container">
3562 <pre class="src src-lisp">(query (alter-table enemy <span style="color: #23d7d7;">:drop-constraint</span> enemy-age-check))
3563 </pre>
3564 </div>
3565
3566 <ul class="org-ul">
3567 <li>:add</li>
3568 </ul>
3569
3570 <p>
3571 Adds an unnamed constraint to table. args should be a constraint in the same
3572 form as for :create-table. (This is for backwards-compatibility, you should
3573 use named constraints.)
3574 </p>
3575
3576 <ul class="org-ul">
3577 <li>:rename</li>
3578 </ul>
3579
3580 <p>
3581 Adds the ability to rename a table.
3582 </p>
3583
3584 <ul class="org-ul">
3585 <li>:rename-column</li>
3586 </ul>
3587
3588 <p>
3589 Adds the ability to rename a column of a table.
3590 </p>
3591 <div class="org-src-container">
3592 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:alter-table</span> <span style="color: #e67128;">"test-uniq"</span> <span style="color: #23d7d7;">:rename-column</span> 'address 'city))
3593 </pre>
3594 </div>
3595 </div>
3596 </div>
3597
3598 <div id="outline-container-sql-op-drop-table" class="outline-3">
3599 <h3 id="sql-op-drop-table">sql-op :drop-table (name)</h3>
3600 <div class="outline-text-3" id="text-sql-op-drop-table">
3601 <p>
3602 Drops the named table. You may optionally pass :if-exists before the name
3603 to suppress the error message if the table does not exist. You can also
3604 optionally pass :cascade after the name to indicate that it should also
3605 drop any other tables, indices, etc which depend on that table.
3606 Accepts strings, variable or symbol as the identifier.
3607 </p>
3608 <div class="org-src-container">
3609 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-table</span> 'table1))
3610 (query (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> 'table1))
3611 (query (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> 'table1 <span style="color: #23d7d7;">:cascade</span>))
3612 (query (<span style="color: #23d7d7;">:drop-table</span> (<span style="color: #23d7d7;">:if-exists</span> 'table1-with-longer-name) <span style="color: #23d7d7;">:cascade</span>))
3613 (<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 <span style="color: #e67128;">"table1"</span>))
3614 (is (equal (sql (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> table-var1 <span style="color: #23d7d7;">:cascade</span>))
3615 <span style="color: #e67128;">"DROP TABLE IF EXISTS table1 CASCADE"</span>))
3616 (<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 'table-1))
3617 (is (equal (sql (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> table-var1 <span style="color: #23d7d7;">:cascade</span>))
3618 <span style="color: #e67128;">"DROP TABLE IF EXISTS table_1 CASCADE"</span>))
3619 </pre>
3620 </div>
3621 </div>
3622 </div>
3623
3624 <div id="outline-container-sql-op-truncate" class="outline-3">
3625 <h3 id="sql-op-truncate">sql-op :truncate (&amp;rest args)</h3>
3626 <div class="outline-text-3" id="text-sql-op-truncate">
3627 <p>
3628 Truncates one or more tables, deleting all the rows. Optional keyword arguments are
3629 allowed in the following order. Note that :continue-identity and :restart-identity
3630 make no sense if both are included.
3631 </p>
3632
3633 <ul class="org-ul">
3634 <li>:only (if not specified, the table and its descendants are truncated).</li>
3635 <li>:continue-identity (the values of sequences will not be changed. This is the default)</li>
3636 <li>:restart-identity (the values of sequences owned by the table(s) will be restarted)</li>
3637 <li>:cascade (will cascade the truncation through tables using foreign keys.)</li>
3638 </ul>
3639
3640 <p>
3641 Example calls would be:
3642 </p>
3643 <div class="org-src-container">
3644 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable))
3645
3646 (query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:only</span>))
3647
3648 (query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:only</span> <span style="color: #23d7d7;">:continue-identity</span>))
3649
3650 (query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:restart-identity</span>))
3651
3652 (query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:only</span> <span style="color: #23d7d7;">:restart-identity</span> <span style="color: #23d7d7;">:cascade</span> ))
3653
3654 </pre>
3655 </div>
3656 </div>
3657 </div>
3658
3659 <div id="outline-container-sql-op-create-index" class="outline-3">
3660 <h3 id="sql-op-create-index">sql-op :create-index (name &amp;rest args)</h3>
3661 <div class="outline-text-3" id="text-sql-op-create-index">
3662 <p>
3663 Create an index on a table. After the name of the index the keyword :on should
3664 follow, with the table name after it. Then the keyword :fields, followed by
3665 one or more column names. Optionally, a :where clause with a condition can
3666 be added at the end to make a partial index.
3667 </p>
3668
3669 <div class="org-src-container">
3670 <pre class="src src-lisp">(sql (<span style="color: #23d7d7;">:create-index</span> 'gin-idx <span style="color: #23d7d7;">:on</span> <span style="color: #e67128;">"historical-events"</span> <span style="color: #23d7d7;">:using</span> gin <span style="color: #23d7d7;">:fields</span> 'data))
3671
3672 <span style="color: #e67128;">"CREATE INDEX gin_idx ON historical_events USING GIN (data)"</span>
3673 </pre>
3674 </div>
3675 </div>
3676 </div>
3677
3678
3679 <div id="outline-container-sql-op-create-unique-index" class="outline-3">
3680 <h3 id="sql-op-create-unique-index">sql-op :create-unique-index (name &amp;rest args)</h3>
3681 <div class="outline-text-3" id="text-sql-op-create-unique-index">
3682 <p>
3683 Works like :create-index, except that the index created is unique.
3684 </p>
3685 </div>
3686 </div>
3687
3688 <div id="outline-container-sql-op-drop-index" class="outline-3">
3689 <h3 id="sql-op-drop-index">sql-op :drop-index (name)</h3>
3690 <div class="outline-text-3" id="text-sql-op-drop-index">
3691 <p>
3692 Drop an index. Takes :if-exists and/or :cascade arguments like :drop-table.
3693 Accepts strings, variable or symbol as the identifier.
3694 </p>
3695 <div class="org-src-container">
3696 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-index</span> 'index1))
3697 (query (<span style="color: #23d7d7;">:drop-index</span> <span style="color: #23d7d7;">:if-exists</span> 'index1))
3698 (query (<span style="color: #23d7d7;">:drop-index</span> <span style="color: #23d7d7;">:if-exists</span> 'index1 <span style="color: #23d7d7;">:cascade</span>))
3699 (<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 <span style="color: #e67128;">"table1"</span>))
3700 (is (equal (sql (<span style="color: #23d7d7;">:drop-index</span> <span style="color: #23d7d7;">:if-exists</span> table-var1 <span style="color: #23d7d7;">:cascade</span>))
3701 <span style="color: #e67128;">"DROP INDEX IF EXISTS table1 CASCADE"</span>))
3702 </pre>
3703 </div>
3704 </div>
3705 </div>
3706
3707 <div id="outline-container-sql-op-create-sequence" class="outline-3">
3708 <h3 id="sql-op-create-sequence">sql-op :create-sequence (name &amp;key increment min-value max-value start cache cycle)</h3>
3709 <div class="outline-text-3" id="text-sql-op-create-sequence">
3710 <p>
3711 Create a sequence with the given name. The rest of the arguments control
3712 the way the sequence selects values.
3713 </p>
3714 </div>
3715 </div>
3716
3717 <div id="outline-container-sql-op-alter-sequence" class="outline-3">
3718 <h3 id="sql-op-alter-sequence">sql-op :alter-sequence (name)</h3>
3719 <div class="outline-text-3" id="text-sql-op-alter-sequence">
3720 <p>
3721 Alters a sequence. See <a href="https://www.postgresql.org/docs/10/static/sql-altersequence.html">Postgresql documentation</a> for parameters.
3722 </p>
3723
3724 <ul class="org-ul">
3725 <li>:increment</li>
3726 </ul>
3727
3728 <p>
3729 Sets the amount by which each subsequent increment will be increased.
3730 </p>
3731
3732 <ul class="org-ul">
3733 <li>:min-value</li>
3734
3735 <li>:max-value</li>
3736
3737 <li>:no-min</li>
3738
3739 <li>:no-max</li>
3740
3741 <li>:start</li>
3742
3743 <li>:restart</li>
3744
3745 <li>:cache</li>
3746
3747 <li>:cycle</li>
3748
3749 <li>:no-cycle</li>
3750
3751 <li>:owned-by</li>
3752
3753 <li>:if-exists before the name to suppress the error message.</li>
3754 </ul>
3755 </div>
3756 </div>
3757
3758 <div id="outline-container-sql-op-drop-sequence" class="outline-3">
3759 <h3 id="sql-op-drop-sequence">sql-op :drop-sequence (name)</h3>
3760 <div class="outline-text-3" id="text-sql-op-drop-sequence">
3761 <p>
3762 Drop a sequence. Takes :if-exists and/or :cascade arguments like :drop-table.
3763 Accepts strings, variable or symbol as the identifier.
3764 </p>
3765 <div class="org-src-container">
3766 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-sequence</span> 'sequence1))
3767 (query (<span style="color: #23d7d7;">:drop-sequence</span> <span style="color: #23d7d7;">:if-exists</span> 'sequence1))
3768 (query (<span style="color: #23d7d7;">:drop-sequence</span> <span style="color: #23d7d7;">:if-exists</span> 'sequence1 <span style="color: #23d7d7;">:cascade</span>))
3769 </pre>
3770 </div>
3771 </div>
3772 </div>
3773
3774 <div id="outline-container-sql-op-create-view" class="outline-3">
3775 <h3 id="sql-op-create-view">sql-op :create-view (name query)</h3>
3776 <div class="outline-text-3" id="text-sql-op-create-view">
3777 <p>
3778 Create a view from an S-SQL-style query.
3779 </p>
3780 </div>
3781 </div>
3782
3783 <div id="outline-container-sql-op-drop-view" class="outline-3">
3784 <h3 id="sql-op-drop-view">sql-op :drop-view (name)</h3>
3785 <div class="outline-text-3" id="text-sql-op-drop-view">
3786 <p>
3787 Drop a view. Takes optional :if-exists argument.
3788 Accepts strings, variable or symbol as the identifier.
3789 </p>
3790 </div>
3791 </div>
3792 <div id="outline-container-sql-op-set-constraints" class="outline-3">
3793 <h3 id="sql-op-set-constraints">sql-op :set-constraints (state &amp;rest constraints)</h3>
3794 <div class="outline-text-3" id="text-sql-op-set-constraints">
3795 <p>
3796 Configure whether deferrable constraints should be checked when a statement
3797 is executed, or when the transaction containing that statement is completed.
3798 The provided state must be either :immediate, indicating the former,
3799 or :deferred, indicating the latter. The constraints must be either the
3800 names of the constraints to be configured, or unspecified, indicating that
3801 all deferrable constraints should be thus configured.
3802 </p>
3803 </div>
3804 </div>
3805
3806 <div id="outline-container-sql-op-listen" class="outline-3">
3807 <h3 id="sql-op-listen">sql-op :listen (channel)</h3>
3808 <div class="outline-text-3" id="text-sql-op-listen">
3809 <p>
3810 Tell the server to listen for notification events on channel channel,
3811 a string, on the current connection.
3812 </p>
3813 </div>
3814 </div>
3815
3816 <div id="outline-container-sql-op-unlisten" class="outline-3">
3817 <h3 id="sql-op-unlisten">sql-op :unlisten (channel)</h3>
3818 <div class="outline-text-3" id="text-sql-op-unlisten">
3819 <p>
3820 Stop listening for events on channel.
3821 </p>
3822 </div>
3823 </div>
3824
3825 <div id="outline-container-sql-op-notify" class="outline-3">
3826 <h3 id="sql-op-notify">sql-op :notify (channel &amp;optional payload)</h3>
3827 <div class="outline-text-3" id="text-sql-op-notify">
3828 <p>
3829 Signal a notification event on channel channel, a string. The optional
3830 payload string can be used to send additional event information to the listeners.
3831 </p>
3832 </div>
3833 </div>
3834
3835 <div id="outline-container-sql-op-create-role" class="outline-3">
3836 <h3 id="sql-op-create-role">sql-op :create-role (role &amp;rest args)</h3>
3837 <div class="outline-text-3" id="text-sql-op-create-role">
3838 <p>
3839 Create a new role (user). Following the role name are optional keywords
3840 arguments:
3841 </p>
3842
3843 <ul class="org-ul">
3844 <li>:options</li>
3845 </ul>
3846
3847 <p>
3848 One or more of the no-parameter options to PostgreSQL's CREATE ROLE SQL command.
3849 </p>
3850
3851 <ul class="org-ul">
3852 <li>:password</li>
3853 </ul>
3854
3855 <p>
3856 Sets the role's password. (A password is only of use for roles having the LOGIN
3857 attribute, but you can nonetheless define one for roles without it.) If you do
3858 not plan to use password authentication you can omit this option. If no
3859 password is specified, the password will be set to null and password
3860 authentication will always fail for that user.
3861 </p>
3862
3863 <ul class="org-ul">
3864 <li>:connection-limit</li>
3865 </ul>
3866
3867 <p>
3868 If role can log in, this specifies how many concurrent connections the role can
3869 make. -1 (the default) means no limit.
3870 </p>
3871
3872 <ul class="org-ul">
3873 <li>:valid-until</li>
3874 </ul>
3875
3876 <p>
3877 The :valid-until clause sets a date and time after which the role's password
3878 is no longer valid. If this clause is omitted the password will be valid for
3879 all time.
3880 </p>
3881
3882 <ul class="org-ul">
3883 <li>:role</li>
3884 </ul>
3885
3886 <p>
3887 Lists one or more existing roles which are automatically added as members of
3888 the new role. (This in effect makes the new role a “group”.)
3889 </p>
3890
3891 <ul class="org-ul">
3892 <li>:in-role</li>
3893 </ul>
3894
3895 <p>
3896 Lists one or more existing roles to which the new role will be immediately
3897 added as a new member.
3898 </p>
3899
3900 <p>
3901 Here is an example of a :create-role form:
3902 </p>
3903 <div class="org-src-container">
3904 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-role</span> 'user23
3905 <span style="color: #23d7d7;">:options</span> 'SUPERUSER 'NOINHERIT 'LOGIN
3906 <span style="color: #23d7d7;">:password</span> <span style="color: #e67128;">"mypassword"</span>
3907 <span style="color: #23d7d7;">:connection-limit</span> 100 <span style="color: #23d7d7;">:role</span> 'users))
3908 </pre>
3909 </div>
3910 </div>
3911 </div>
3912
3913 <div id="outline-container-sql-op-create-database" class="outline-3">
3914 <h3 id="sql-op-create-database">sql-op :create-database (name)</h3>
3915 <div class="outline-text-3" id="text-sql-op-create-database">
3916 <p>
3917 Create a new database with the given name.
3918 </p>
3919 </div>
3920 </div>
3921
3922 <div id="outline-container-sql-op-drop-database" class="outline-3">
3923 <h3 id="sql-op-drop-database">sql-op :drop-database (name)</h3>
3924 <div class="outline-text-3" id="text-sql-op-drop-database">
3925 <p>
3926 Drops the named database. You may optionally pass :if-exists before the
3927 name to suppress the error message. Examples:
3928 </p>
3929 <div class="org-src-container">
3930 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-database</span> 'database-name))
3931 (query (<span style="color: #23d7d7;">:drop-database</span> <span style="color: #23d7d7;">:if-exists</span> 'database-name))
3932 (<span style="color: #ffad29; font-weight: bold;">let</span> ((var 'my-database)) (query (<span style="color: #23d7d7;">:drop-database</span> var)))
3933 </pre>
3934 </div>
3935 </div>
3936 </div>
3937
3938 <div id="outline-container-sql-op-copy" class="outline-3">
3939 <h3 id="sql-op-copy">sql-op :copy (table &amp;rest args)</h3>
3940 <div class="outline-text-3" id="text-sql-op-copy">
3941 <p>
3942 Move data between Postgres tables and filesystem files. Table name is required
3943 followed by one or more of the following keyword arguments. Documentation for
3944 the copy command provides a full reference. An example from the Greenplum
3945 tutorial:
3946 </p>
3947 <div class="org-src-container">
3948 <pre class="src src-lisp">(query
3949 (<span style="color: #23d7d7;">:copy</span> 'faa.d_airlines
3950 <span style="color: #23d7d7;">:columns</span> 'airlineid 'airline_desc
3951 <span style="color: #23d7d7;">:from</span> <span style="color: #e67128;">"/home/gpadmin/gpdb-sandbox-tutorials/faa/L_AIRLINE_ID.csv"</span>
3952 <span style="color: #23d7d7;">:on-segment</span> t
3953 <span style="color: #23d7d7;">:binary</span> t
3954 <span style="color: #23d7d7;">:oids</span> t
3955 <span style="color: #23d7d7;">:header</span> t
3956 <span style="color: #23d7d7;">:delimiter</span> <span style="color: #e67128;">","</span>
3957 <span style="color: #23d7d7;">:null</span> <span style="color: #e67128;">"NULL"</span>
3958 <span style="color: #23d7d7;">:escape</span> <span style="color: #e67128;">"my-escape-string"</span>
3959 <span style="color: #23d7d7;">:newline</span> <span style="color: #e67128;">"CR"</span>
3960 <span style="color: #23d7d7;">:csv</span> t
3961 <span style="color: #23d7d7;">:log-errors</span> t
3962 <span style="color: #23d7d7;">:segment-reject-limit</span> 100 'ROWS))
3963
3964 </pre>
3965 </div>
3966 </div>
3967 </div>
3968 </div>
3969 <div id="outline-container-dynamic-queries-composition-and-parameterized-queries" class="outline-2">
3970 <h2 id="dynamic-queries-composition-and-parameterized-queries">Dynamic Queries, Composition and Parameterized Queries</h2>
3971 <div class="outline-text-2" id="text-dynamic-queries-composition-and-parameterized-queries">
3972 </div>
3973
3974 <div id="outline-container-dynamic-queries-overview" class="outline-3">
3975 <h3 id="dynamic-queries-overview">Overview</h3>
3976 <div class="outline-text-3" id="text-dynamic-queries-overview">
3977 <p>
3978 The question gets asked how to build dynamic or composable queries in
3979 postmodern. First we need to understand the context - is the programmer
3980 building the query or are you taking data from a user and using that to
3981 build a query?
3982 </p>
3983 </div>
3984
3985 <div id="outline-container-programmer-built-queries" class="outline-4">
3986 <h4 id="programmer-built-queries">Programmer Built Queries</h4>
3987 <div class="outline-text-4" id="text-programmer-built-queries">
3988 <p>
3989 The question gets asked how to build dynamic or composable queries in
3990 postmodern. First we need to understand the context - is the programmer
3991 building the query or are you taking data from a user and using that to
3992 build a query? We need to remember that the query macro assumes that everything
3993 that is not a list starting with a keyword will evaluate to a string.
3994 </p>
3995
3996 <p>
3997 In any case you will need to ensure that either you have control over the inputs
3998 or they still result in parameterized queries. If not you have opened yourself up
3999 to an sql injection attack.
4000 </p>
4001
4002 <p>
4003 If you are not using s-sql, then it becomes easy. The query macro
4004 assumes that everything that is not a list starting with a keyword will
4005 evaluate to a string. That means you can build it with a simple format
4006 string
4007 </p>
4008 <div class="org-src-container">
4009 <pre class="src src-lisp">(query (format nil <span style="color: #e67128;">"select ~a from ~a where ~a"</span> <span style="color: #e67128;">"carrots"</span> <span style="color: #e67128;">"garden"</span> <span style="color: #e67128;">"length &gt; 3"</span>))
4010 </pre>
4011 </div>
4012
4013 <p>
4014 With s-sql, there are generally three approaches to building dynamic or
4015 composible queries: pass symbols and values as variables, use sql-compile
4016 or use :raw.
4017 </p>
4018
4019 <p>
4020 For purposes of this example, we will use the following employee table:
4021 </p>
4022
4023 <div class="org-src-container">
4024 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-table</span> employee ((id <span style="color: #23d7d7;">:type</span> int)
4025 (name <span style="color: #23d7d7;">:type</span> text)
4026 (salary <span style="color: #23d7d7;">:type</span> numeric)
4027 (start_date <span style="color: #23d7d7;">:type</span> date)
4028 (city <span style="color: #23d7d7;">:type</span> text)
4029 (region <span style="color: #23d7d7;">:type</span> char)
4030 (age <span style="color: #23d7d7;">:type</span> int))))
4031
4032 (query (<span style="color: #23d7d7;">:insert-rows-into</span> 'employee
4033 <span style="color: #23d7d7;">:columns</span> 'id 'name 'salary 'start-date 'city 'region 'age
4034 <span style="color: #23d7d7;">:values</span> '((1 <span style="color: #e67128;">"Jason"</span> 40420 <span style="color: #e67128;">"02/01/94"</span> <span style="color: #e67128;">"New York"</span> <span style="color: #e67128;">"W"</span> 29)
4035 (2 <span style="color: #e67128;">"Robert"</span> 14420 <span style="color: #e67128;">"01/02/95"</span> <span style="color: #e67128;">"Vancouver"</span> <span style="color: #e67128;">"N"</span> 21)
4036 (3 <span style="color: #e67128;">"Celia"</span> 24020 <span style="color: #e67128;">"12/03/96"</span> <span style="color: #e67128;">"Toronto"</span> <span style="color: #e67128;">"W"</span> 24)
4037 (4 <span style="color: #e67128;">"Linda"</span> 40620 <span style="color: #e67128;">"11/04/97"</span> <span style="color: #e67128;">"New York"</span> <span style="color: #e67128;">"N"</span> 28)
4038 (5 <span style="color: #e67128;">"David"</span> 80026 <span style="color: #e67128;">"10/05/98"</span> <span style="color: #e67128;">"Vancouver"</span> <span style="color: #e67128;">"W"</span> 31)
4039 (6 <span style="color: #e67128;">"James"</span> 70060 <span style="color: #e67128;">"09/06/99"</span> <span style="color: #e67128;">"Toronto"</span> <span style="color: #e67128;">"N"</span> 26)
4040 (7 <span style="color: #e67128;">"Alison"</span> 90620 <span style="color: #e67128;">"08/07/00"</span> <span style="color: #e67128;">"New York"</span> <span style="color: #e67128;">"W"</span> 38)
4041 (8 <span style="color: #e67128;">"Chris"</span> 26020 <span style="color: #e67128;">"07/08/01"</span> <span style="color: #e67128;">"Vancouver"</span> <span style="color: #e67128;">"N"</span> 22)
4042 (9 <span style="color: #e67128;">"Mary"</span> 60020 <span style="color: #e67128;">"06/08/02"</span> <span style="color: #e67128;">"Toronto"</span> <span style="color: #e67128;">"W"</span> 34))))
4043 </pre>
4044 </div>
4045 </div>
4046 <ul class="org-ul">
4047 <li><a id="symbols-in-variables"></a>Approach #1 Using symbols in variables<br>
4048 <div class="outline-text-5" id="text-symbols-in-variables">
4049 </div>
4050 <ul class="org-ul">
4051 <li><a id="orgb3aab1e"></a>Select Statements<br>
4052 <div class="outline-text-6" id="text-orgb3aab1e">
4053 <p>
4054 Consider the following two toy examples where we determine the table and columns
4055 to be selected using symbols (either keyword or quoted) inside variables.
4056 </p>
4057 <div class="org-src-container">
4058 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 <span style="color: #23d7d7;">:id</span>) (col2 <span style="color: #23d7d7;">:name</span>) (id 3))
4059 (query (<span style="color: #23d7d7;">:select</span> col1 col2 <span style="color: #23d7d7;">:from</span> table <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id '$1)) id))
4060 ((3 <span style="color: #e67128;">"Celia"</span>))
4061
4062 (<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 'name) (col2 'salary) (id 3))
4063 (query (<span style="color: #23d7d7;">:select</span> col1 col2 <span style="color: #23d7d7;">:from</span> table <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id '$1)) id))
4064 ((<span style="color: #e67128;">"Celia"</span> 24020))
4065 </pre>
4066 </div>
4067 <p>
4068 This will not work if you use strings instead of symbols because sql-expand
4069 will wrap the strings in the variables in escape format as if they were string
4070 constants and Postgresql will throw an error because it is not expecting
4071 string constants in the middle of a select statement.
4072 </p>
4073 </div>
4074 </li>
4075
4076 <li><a id="org0a3c30e"></a>Update Statements<br>
4077 <div class="outline-text-6" id="text-org0a3c30e">
4078 <p>
4079 This works with update statements as well
4080 </p>
4081 <div class="org-src-container">
4082 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 <span style="color: #23d7d7;">:id</span>) (col2 <span style="color: #23d7d7;">:name</span>) (new-name <span style="color: #e67128;">"Celeste"</span>) (id 3))
4083 (query (<span style="color: #23d7d7;">:update</span> table <span style="color: #23d7d7;">:set</span> col2 new-name <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> col1 '$1)) id)
4084 (query (<span style="color: #23d7d7;">:select</span> col1 col2 <span style="color: #23d7d7;">:from</span> table <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id '$1)) id))
4085 ((3 <span style="color: #e67128;">"Celeste"</span>))
4086 </pre>
4087 </div>
4088 </div>
4089 </li>
4090 <li><a id="orga8790bf"></a>Insert Statements<br>
4091 <div class="outline-text-6" id="text-orga8790bf">
4092 <p>
4093 This works with insert-into statements as well
4094 </p>
4095 <div class="org-src-container">
4096 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 'id) (col2 'name) (new-name <span style="color: #e67128;">"Rochelle"</span>)
4097 (id 10) (col3 'salary) (col3-value 3452) (col4 'start-date)
4098 (col4-value <span style="color: #e67128;">"02/01/03"</span>) (col5 'city) (col5-value <span style="color: #e67128;">"Victoria"</span>)
4099 (col6 'region) (col6-value <span style="color: #e67128;">"N"</span>) (col7 'age) (col7-value 32))
4100 (query (<span style="color: #23d7d7;">:insert-into</span> table <span style="color: #23d7d7;">:set</span> col1 id col2 new-name col3 col3-value
4101 col4 col4-value col5 col5-value col6 col6-value
4102 col7 col7-value)))
4103
4104 (query (<span style="color: #23d7d7;">:select</span> 'id 'name 'salary <span style="color: #23d7d7;">:from</span> 'employee <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id 10 )))
4105 ((10 <span style="color: #e67128;">"Rochelle"</span> 3452))
4106 </pre>
4107 </div>
4108 </div>
4109 </li>
4110 </ul>
4111 </li>
4112 <li><a id="org7846f41"></a>Delete Statements<br>
4113 <div class="outline-text-5" id="text-org7846f41">
4114 <p>
4115 This works with delete statements as well
4116 </p>
4117 <div class="org-src-container">
4118 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 <span style="color: #23d7d7;">:id</span>) (col1-value 10))
4119 (query (<span style="color: #23d7d7;">:delete-from</span> table <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> col1 col1-value))))
4120 </pre>
4121 </div>
4122 </div>
4123 </li>
4124 </ul>
4125 </div>
4126 <div id="outline-container-approach-use-sql-compile" class="outline-4">
4127 <h4 id="approach-use-sql-compile">Approach #2 Use sql-compile</h4>
4128 <div class="outline-text-4" id="text-approach-use-sql-compile">
4129 <p>
4130 Sql-compile does a run-time compilation of an s-sql expression. In the
4131 following example, we create a function that accepts a where-clause, a
4132 table-name, 3 columns to select and two parameters to go into the where
4133 clause.
4134 </p>
4135
4136 <div class="org-src-container">
4137 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defun</span> <span style="color: #00ede1; font-weight: bold;">toy-example</span> (where-clause table-name col1 col2 col3 arg1 arg2)
4138 (<span style="color: #ffad29; font-weight: bold;">with-test-connection</span>
4139 (query (sql-compile
4140 (append `(<span style="color: #23d7d7;">:select</span> ,col1 ,col2 ,col3 <span style="color: #23d7d7;">:from</span> ,table-name <span style="color: #23d7d7;">:where</span>)
4141 where-clause))
4142 arg1 arg2)))
4143
4144 (toy-example '((<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'city '$1) (<span style="color: #23d7d7;">:&gt;</span> 'salary '$2))) 'employee 'id 'name 'city <span style="color: #e67128;">"Toronto"</span> 45000)
4145
4146 ((6 <span style="color: #e67128;">"James"</span> <span style="color: #e67128;">"Toronto"</span>) (9 <span style="color: #e67128;">"Mary"</span> <span style="color: #e67128;">"Toronto"</span>))
4147 </pre>
4148 </div>
4149
4150 <p>
4151 If we just look at what this call to sql-compile in toy-example
4152 generates, it would look like:
4153 </p>
4154
4155 <div class="org-src-container">
4156 <pre class="src src-lisp"><span style="color: #e67128;">"(SELECT id, name, city FROM employee WHERE ((city = $1) and (salary &gt; $2)))"</span>
4157 </pre>
4158 </div>
4159
4160 <p>
4161 This example is still a parameterized query but for security reasons you
4162 will need to be very careful how you generate the where clause.
4163 </p>
4164
4165 <p>
4166 Another example with sql-compile and append, in this case updating a
4167 table and setting two columns to NULL.
4168 </p>
4169
4170 <div class="org-src-container">
4171 <pre class="src src-lisp">(sql-compile (append '(<span style="color: #23d7d7;">:update</span> <span style="color: #23d7d7;">:table1</span> <span style="color: #23d7d7;">:set</span>)
4172 (<span style="color: #ffad29; font-weight: bold;">loop</span> for a in '(<span style="color: #e67128;">"col1"</span> <span style="color: #e67128;">"col2"</span>)
4173 collect a
4174 collect <span style="color: #23d7d7;">:NULL</span>)))
4175
4176 <span style="color: #e67128;">"UPDATE table1 SET E'col1' = NULL, E'col2' = NULL"</span>
4177 </pre>
4178 </div>
4179
4180 <p>
4181 Lets think about it differently. What if we know the universe of columns
4182 we want to select, but want to conditionally select some of them.
4183 Suppose we know our targetted table has columns:
4184 </p>
4185
4186 <div class="org-src-container">
4187 <pre class="src src-lisp">'id 'name 'salary 'start-date 'city 'region 'age.
4188 </pre>
4189 </div>
4190
4191 <p>
4192 We may decide we always want name, city and age, but salary and
4193 start-date are conditional.
4194 </p>
4195
4196 <div class="org-src-container">
4197 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defun</span> <span style="color: #00ede1; font-weight: bold;">toy-example-2</span> (salaryp start-date-p)
4198 (sql-compile
4199 (remove nil `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age
4200 ,(<span style="color: #ffad29; font-weight: bold;">if</span> salaryp 'salary nil)
4201 ,(<span style="color: #ffad29; font-weight: bold;">if</span> start-date-p 'start-date nil)
4202 <span style="color: #23d7d7;">:from</span> 'employee))))
4203
4204 (query (toy-example-2 t t))
4205
4206 ((<span style="color: #e67128;">"Jason"</span> <span style="color: #e67128;">"New York"</span> 29 40420 #&lt;SIMPLE-DATE:DATE 01-02-1994&gt;)
4207 (<span style="color: #e67128;">"Robert"</span> <span style="color: #e67128;">"Vancouver"</span> 21 14420 #&lt;SIMPLE-DATE:DATE 02-01-1995&gt;)
4208 (<span style="color: #e67128;">"Celia"</span> <span style="color: #e67128;">"Toronto"</span> 24 24020 #&lt;SIMPLE-DATE:DATE 03-12-1996&gt;)
4209 (<span style="color: #e67128;">"Linda"</span> <span style="color: #e67128;">"New York"</span> 28 40620 #&lt;SIMPLE-DATE:DATE 04-11-1997&gt;)
4210 (<span style="color: #e67128;">"David"</span> <span style="color: #e67128;">"Vancouver"</span> 31 80026 #&lt;SIMPLE-DATE:DATE 05-10-1998&gt;)
4211 (<span style="color: #e67128;">"James"</span> <span style="color: #e67128;">"Toronto"</span> 26 70060 #&lt;SIMPLE-DATE:DATE 06-09-1999&gt;)
4212 (<span style="color: #e67128;">"Alison"</span> <span style="color: #e67128;">"New York"</span> 38 90620 #&lt;SIMPLE-DATE:DATE 07-08-2000&gt;)
4213 (<span style="color: #e67128;">"Chris"</span> <span style="color: #e67128;">"Vancouver"</span> 22 26020 #&lt;SIMPLE-DATE:DATE 08-07-2001&gt;)
4214 (<span style="color: #e67128;">"Mary"</span> <span style="color: #e67128;">"Toronto"</span> 34 60020 #&lt;SIMPLE-DATE:DATE 08-06-2002&gt;))
4215
4216 (query (toy-example-2 t nil))
4217
4218 ((<span style="color: #e67128;">"Jason"</span> <span style="color: #e67128;">"New York"</span> 29 40420) (<span style="color: #e67128;">"Robert"</span> <span style="color: #e67128;">"Vancouver"</span> 21 14420)
4219 (<span style="color: #e67128;">"Celia"</span> <span style="color: #e67128;">"Toronto"</span> 24 24020) (<span style="color: #e67128;">"Linda"</span> <span style="color: #e67128;">"New York"</span> 28 40620)
4220 (<span style="color: #e67128;">"David"</span> <span style="color: #e67128;">"Vancouver"</span> 31 80026) (<span style="color: #e67128;">"James"</span> <span style="color: #e67128;">"Toronto"</span> 26 70060)
4221 (<span style="color: #e67128;">"Alison"</span> <span style="color: #e67128;">"New York"</span> 38 90620) (<span style="color: #e67128;">"Chris"</span> <span style="color: #e67128;">"Vancouver"</span> 22 26020)
4222 (<span style="color: #e67128;">"Mary"</span> <span style="color: #e67128;">"Toronto"</span> 34 60020))
4223 </pre>
4224 </div>
4225
4226 <p>
4227 You could skip the (remove nil&#x2026; portion and substitute t for nil. E.g.
4228 </p>
4229
4230 <div class="org-src-container">
4231 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defun</span> <span style="color: #00ede1; font-weight: bold;">toy-example-2</span> (salaryp start-date-p)
4232 (sql-compile
4233 `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age
4234 ,(<span style="color: #ffad29; font-weight: bold;">if</span> salaryp 'salary t)
4235 ,(<span style="color: #ffad29; font-weight: bold;">if</span> start-date-p 'start-date t)
4236 <span style="color: #23d7d7;">:from</span> 'employee)))
4237 </pre>
4238 </div>
4239
4240 <p>
4241 But I prefer to remove those segments completely from the query.
4242 </p>
4243
4244 <p>
4245 Following on this same thread of thought, you can define a portion of
4246 the sql in a let clause:
4247 </p>
4248
4249 <div class="org-src-container">
4250 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((sql1 '(<span style="color: #23d7d7;">:=</span> name <span style="color: #e67128;">"Jason"</span>)))
4251 (query (sql-compile
4252 `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age <span style="color: #23d7d7;">:from</span> 'employee <span style="color: #23d7d7;">:where</span> ,sql1))))
4253
4254 ((<span style="color: #e67128;">"Jason"</span> <span style="color: #e67128;">"New York"</span> 29))
4255 </pre>
4256 </div>
4257
4258 <p>
4259 An example of this would be getting more columns depending on the
4260 postgresql server versionr:
4261 </p>
4262
4263 <div class="org-src-container">
4264 <pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">defun</span> <span style="color: #00ede1; font-weight: bold;">more-table-info</span> (table-name)
4265 <span style="color: #e67128;">"Returns variable amounts of information depending on the postgresql server version"</span>
4266 (<span style="color: #ffad29; font-weight: bold;">let*</span> ((version&gt;11 (postgresql-version-at-least <span style="color: #e67128;">"12.0"</span> =*database*=))
4267 (version&gt;10 (postgresql-version-at-least <span style="color: #e67128;">"11.0"</span> =*database*=))
4268 (select-query (sql-compile
4269 `(<span style="color: #23d7d7;">:order-by</span>
4270 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'a.attnum 'ordinal-position)
4271 (<span style="color: #23d7d7;">:as</span> 'a.attname 'column-name)
4272 (<span style="color: #23d7d7;">:as</span> 'tn.typname 'data-type)
4273 ,(<span style="color: #ffad29; font-weight: bold;">if</span> version&gt;10 'a.attidentity t)
4274 ,(<span style="color: #ffad29; font-weight: bold;">if</span> version&gt;11 'a.attgenerated t)
4275 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg_class 'c)
4276 (<span style="color: #23d7d7;">:as</span> 'pg_attribute 'a)
4277 (<span style="color: #23d7d7;">:as</span> 'pg_type 'tn)
4278 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
4279 (<span style="color: #23d7d7;">:=</span> 'c.relname '$1)
4280 (<span style="color: #23d7d7;">:&gt;</span> 'a.attnum 0)
4281 (<span style="color: #23d7d7;">:=</span> 'a.attrelid 'c.oid)
4282 (<span style="color: #23d7d7;">:=</span> 'a.atttypid 'tn.oid)))
4283 'a.attnum))))
4284 (query select-query
4285 (to-sql-name table-name))))
4286 </pre>
4287 </div>
4288 </div>
4289 </div>
4290
4291 <div id="outline-container-raw-approach" class="outline-4">
4292 <h4 id="raw-approach">Approach #3 Use :raw</h4>
4293 <div class="outline-text-4" id="text-raw-approach">
4294 <p>
4295 To quote Marijn, the :raw keyword takes a string and inserts it straight
4296 into the query. I try to stay away from :raw if possible, but
4297 sometimes&#x2026;
4298 </p>
4299
4300 <div class="org-src-container">
4301 <pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:raw</span> <span style="color: #e67128;">"tmp1.name"</span>) <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'baz (<span style="color: #23d7d7;">:raw</span> <span style="color: #e67128;">"tmp1"</span>))))
4302 </pre>
4303 </div>
4304 </div>
4305 </div>
4306
4307 <div id="outline-container-queries-with-user-input" class="outline-4">
4308 <h4 id="queries-with-user-input">Queries with User Input</h4>
4309 <div class="outline-text-4" id="text-queries-with-user-input">
4310 <p>
4311 In any of the above approaches to building queries you will need to
4312 ensure that either you have control over the inputs or they still result
4313 in parameterized queries. If not, you have opened yourself up to an sql
4314 injection attack.
4315 </p>
4316 </div>
4317 </div>
4318 </div>
4319 </div>
4320 </div>
4321 </body>
4322 </html>