File: s-sql.html

package info (click to toggle)
cl-postmodern 20211113.git9d4332f-3
links: PTS, VCS
area: main
in suites: bookworm
size: 3,524 kB
sloc: lisp: 22,909; sql: 76; makefile: 2
file content (4322 lines) | stat: -rw-r--r-- 218,898 bytes
parent folder | download | duplicates (2)
<!DOCTYPE html>
<html lang="en">
<head>
<!-- 2021-04-25 Sun 12:36 -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>S-SQL Reference Manual</title>
<meta name="generator" content="Org mode">
<meta name="author" content="Sabra Crolleton">
<style type="text/css">
 <!--/*--><![CDATA[/*><!--*/
  .title  { text-align: center;
             margin-bottom: .2em; }
  .subtitle { text-align: center;
              font-size: medium;
              font-weight: bold;
              margin-top:0; }
  .todo   { font-family: monospace; color: red; }
  .done   { font-family: monospace; color: green; }
  .priority { font-family: monospace; color: orange; }
  .tag    { background-color: #eee; font-family: monospace;
            padding: 2px; font-size: 80%; font-weight: normal; }
  .timestamp { color: #bebebe; }
  .timestamp-kwd { color: #5f9ea0; }
  .org-right  { margin-left: auto; margin-right: 0px;  text-align: right; }
  .org-left   { margin-left: 0px;  margin-right: auto; text-align: left; }
  .org-center { margin-left: auto; margin-right: auto; text-align: center; }
  .underline { text-decoration: underline; }
  #postamble p, #preamble p { font-size: 90%; margin: .2em; }
  p.verse { margin-left: 3%; }
  pre {
    border: 1px solid #ccc;
    box-shadow: 3px 3px 3px #eee;
    padding: 8pt;
    font-family: monospace;
    overflow: auto;
    margin: 1.2em;
  }
  pre.src {
    position: relative;
    overflow: auto;
    padding-top: 1.2em;
  }
  pre.src:before {
    display: none;
    position: absolute;
    background-color: white;
    top: -10px;
    right: 10px;
    padding: 3px;
    border: 1px solid black;
  }
  pre.src:hover:before { display: inline; margin-top: 14px;}
  /* Languages per Org manual */
  pre.src-asymptote:before { content: 'Asymptote'; }
  pre.src-awk:before { content: 'Awk'; }
  pre.src-C:before { content: 'C'; }
  /* pre.src-C++ doesn't work in CSS */
  pre.src-clojure:before { content: 'Clojure'; }
  pre.src-css:before { content: 'CSS'; }
  pre.src-D:before { content: 'D'; }
  pre.src-ditaa:before { content: 'ditaa'; }
  pre.src-dot:before { content: 'Graphviz'; }
  pre.src-calc:before { content: 'Emacs Calc'; }
  pre.src-emacs-lisp:before { content: 'Emacs Lisp'; }
  pre.src-fortran:before { content: 'Fortran'; }
  pre.src-gnuplot:before { content: 'gnuplot'; }
  pre.src-haskell:before { content: 'Haskell'; }
  pre.src-hledger:before { content: 'hledger'; }
  pre.src-java:before { content: 'Java'; }
  pre.src-js:before { content: 'Javascript'; }
  pre.src-latex:before { content: 'LaTeX'; }
  pre.src-ledger:before { content: 'Ledger'; }
  pre.src-lisp:before { content: 'Lisp'; }
  pre.src-lilypond:before { content: 'Lilypond'; }
  pre.src-lua:before { content: 'Lua'; }
  pre.src-matlab:before { content: 'MATLAB'; }
  pre.src-mscgen:before { content: 'Mscgen'; }
  pre.src-ocaml:before { content: 'Objective Caml'; }
  pre.src-octave:before { content: 'Octave'; }
  pre.src-org:before { content: 'Org mode'; }
  pre.src-oz:before { content: 'OZ'; }
  pre.src-plantuml:before { content: 'Plantuml'; }
  pre.src-processing:before { content: 'Processing.js'; }
  pre.src-python:before { content: 'Python'; }
  pre.src-R:before { content: 'R'; }
  pre.src-ruby:before { content: 'Ruby'; }
  pre.src-sass:before { content: 'Sass'; }
  pre.src-scheme:before { content: 'Scheme'; }
  pre.src-screen:before { content: 'Gnu Screen'; }
  pre.src-sed:before { content: 'Sed'; }
  pre.src-sh:before { content: 'shell'; }
  pre.src-sql:before { content: 'SQL'; }
  pre.src-sqlite:before { content: 'SQLite'; }
  /* additional languages in org.el's org-babel-load-languages alist */
  pre.src-forth:before { content: 'Forth'; }
  pre.src-io:before { content: 'IO'; }
  pre.src-J:before { content: 'J'; }
  pre.src-makefile:before { content: 'Makefile'; }
  pre.src-maxima:before { content: 'Maxima'; }
  pre.src-perl:before { content: 'Perl'; }
  pre.src-picolisp:before { content: 'Pico Lisp'; }
  pre.src-scala:before { content: 'Scala'; }
  pre.src-shell:before { content: 'Shell Script'; }
  pre.src-ebnf2ps:before { content: 'ebfn2ps'; }
  /* additional language identifiers per "defun org-babel-execute"
       in ob-*.el */
  pre.src-cpp:before  { content: 'C++'; }
  pre.src-abc:before  { content: 'ABC'; }
  pre.src-coq:before  { content: 'Coq'; }
  pre.src-groovy:before  { content: 'Groovy'; }
  /* additional language identifiers from org-babel-shell-names in
     ob-shell.el: ob-shell is the only babel language using a lambda to put
     the execution function name together. */
  pre.src-bash:before  { content: 'bash'; }
  pre.src-csh:before  { content: 'csh'; }
  pre.src-ash:before  { content: 'ash'; }
  pre.src-dash:before  { content: 'dash'; }
  pre.src-ksh:before  { content: 'ksh'; }
  pre.src-mksh:before  { content: 'mksh'; }
  pre.src-posh:before  { content: 'posh'; }
  /* Additional Emacs modes also supported by the LaTeX listings package */
  pre.src-ada:before { content: 'Ada'; }
  pre.src-asm:before { content: 'Assembler'; }
  pre.src-caml:before { content: 'Caml'; }
  pre.src-delphi:before { content: 'Delphi'; }
  pre.src-html:before { content: 'HTML'; }
  pre.src-idl:before { content: 'IDL'; }
  pre.src-mercury:before { content: 'Mercury'; }
  pre.src-metapost:before { content: 'MetaPost'; }
  pre.src-modula-2:before { content: 'Modula-2'; }
  pre.src-pascal:before { content: 'Pascal'; }
  pre.src-ps:before { content: 'PostScript'; }
  pre.src-prolog:before { content: 'Prolog'; }
  pre.src-simula:before { content: 'Simula'; }
  pre.src-tcl:before { content: 'tcl'; }
  pre.src-tex:before { content: 'TeX'; }
  pre.src-plain-tex:before { content: 'Plain TeX'; }
  pre.src-verilog:before { content: 'Verilog'; }
  pre.src-vhdl:before { content: 'VHDL'; }
  pre.src-xml:before { content: 'XML'; }
  pre.src-nxml:before { content: 'XML'; }
  /* add a generic configuration mode; LaTeX export needs an additional
     (add-to-list 'org-latex-listings-langs '(conf " ")) in .emacs */
  pre.src-conf:before { content: 'Configuration File'; }

  table { border-collapse:collapse; }
  caption.t-above { caption-side: top; }
  caption.t-bottom { caption-side: bottom; }
  td, th { vertical-align:top;  }
  th.org-right  { text-align: center;  }
  th.org-left   { text-align: center;   }
  th.org-center { text-align: center; }
  td.org-right  { text-align: right;  }
  td.org-left   { text-align: left;   }
  td.org-center { text-align: center; }
  dt { font-weight: bold; }
  .footpara { display: inline; }
  .footdef  { margin-bottom: 1em; }
  .figure { padding: 1em; }
  .figure p { text-align: center; }
  .equation-container {
    display: table;
    text-align: center;
    width: 100%;
  }
  .equation {
    vertical-align: middle;
  }
  .equation-label {
    display: table-cell;
    text-align: right;
    vertical-align: middle;
  }
  .inlinetask {
    padding: 10px;
    border: 2px solid gray;
    margin: 10px;
    background: #ffffcc;
  }
  #org-div-home-and-up
   { text-align: right; font-size: 70%; white-space: nowrap; }
  textarea { overflow-x: auto; }
  .linenr { font-size: smaller }
  .code-highlighted { background-color: #ffff00; }
  .org-info-js_info-navigation { border-style: none; }
  #org-info-js_console-label
    { font-size: 10px; font-weight: bold; white-space: nowrap; }
  .org-info-js_search-highlight
    { background-color: #ffff00; color: #000000; font-weight: bold; }
  .org-svg { width: 90%; }
  /*]]>*/-->
</style>
<link rel="stylesheet" type="text/css" href="style.css" />
<style>pre.src{background:#343131;color:white;} </style>
<script type="text/javascript">
// @license magnet:?xt=urn:btih:e95b018ef3580986a04669f1b5879592219e2a7a&dn=public-domain.txt Public Domain
<!--/*--><![CDATA[/*><!--*/
     function CodeHighlightOn(elem, id)
     {
       var target = document.getElementById(id);
       if(null != target) {
         elem.classList.add("code-highlighted");
         target.classList.add("code-highlighted");
       }
     }
     function CodeHighlightOff(elem, id)
     {
       var target = document.getElementById(id);
       if(null != target) {
         elem.classList.remove("code-highlighted");
         target.classList.remove("code-highlighted");
       }
     }
    /*]]>*///-->
// @license-end
</script>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        displayAlign: "center",
        displayIndent: "0em",

        "HTML-CSS": { scale: 100,
                        linebreaks: { automatic: "false" },
                        webFont: "TeX"
                       },
        SVG: {scale: 100,
              linebreaks: { automatic: "false" },
              font: "TeX"},
        NativeMML: {scale: 100},
        TeX: { equationNumbers: {autoNumber: "AMS"},
               MultLineWidth: "85%",
               TagSide: "right",
               TagIndent: ".8em"
             }
});
</script>
<script type="text/javascript"
        src="file:///usr/share/javascript/mathjax/MathJax.js?config=TeX-AMS_HTML"></script>
</head>
<body>
<div id="content">
<header>
<h1 class="title">S-SQL Reference Manual</h1>
</header><nav id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#interface">Interface</a>
<ul>
<li><a href="#macro-sql">macro sql (form)</a></li>
<li><a href="#function-sql-compile">function sql-compile (form)</a></li>
<li><a href="#function-sql-template">function sql-template (form)</a></li>
<li><a href="#function-enable-s-sql-syntax">function enable-s-sql-syntax (&amp;optional (char #\Q))</a></li>
<li><a href="#function-sql-escape-string">function sql-escape-string (string)</a></li>
<li><a href="#method-sql-escape">method sql-escape (value)</a></li>
<li><a href="#variable-downcase-symbols">variable <code>*downcase-symbols*</code></a></li>
<li><a href="#variable-standard-sql-strings">variable <code>*standard-sql-strings*</code></a></li>
<li><a href="#variable-postgres-reserved-words">variable <code>*postgres-reserved-words*</code> hashtable</a></li>
<li><a href="#variable-escape-sql-names-p">variable <code>*escape-sql-names-p*</code></a></li>
<li><a href="#function-sql-type-name">function sql-type-name (type)</a></li>
<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>
<li><a href="#function-from-sql-name">function from-sql-name (string)</a></li>
<li><a href="#macro-register-sql-operators">macro register-sql-operators (arity &amp;rest names)</a></li>
</ul>
</li>
<li><a href="#sql-types">SQL Types</a>
<ul>
<li><a href="#type-db-null">type db-null</a></li>
</ul>
</li>
<li><a href="#sql-syntax">SQL Syntax</a>
<ul>
<li><a href="#sql-op-select">sql-op :select (&amp;rest args)</a></li>
<li><a href="#e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">Joins</a>
<ul>
<li><a href="#40e45849-5e9d-4b4c-830b-53f79f0b21e2">Cross Join/ Cross Join Lateral</a></li>
<li><a href="#85c25a7d-3660-4d38-85f0-2b9c9dc88684">Inner Join / Inner Join Lateral</a></li>
<li><a href="#ee0a6fef-de2f-407e-9cc9-3667de7775dc">Outer Join / Outer Join Lateral</a></li>
<li><a href="#3061c378-d2d1-4dda-833a-f1b3f8569018">Left Join / Left Join Lateral / Right Join / Right Join Lateral</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#defined-operators">Defined Operators</a>
<ul>
<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>
<li><a href="#sql-op-or">sql-op :or</a></li>
<li><a href="#sql-op-intersect">sql-op :intersect</a></li>
<li><a href="#026a2773-c17f-4cb2-a86c-34babf8c48a2">sql-op :union, :union-all</a></li>
<li><a href="#4f77625f-4b6e-417d-8b56-d76835d6832d">sql-op :except, :except-all</a></li>
<li><a href="#sql-op-not">sql-op :~, :not (arg)</a></li>
<li><a href="#sql-op-any">sql-op :any, :any*</a></li>
<li><a href="#sql-op-function">sql-op :function (name (&amp;rest arg-types) return-type stability body)</a></li>
<li><a href="#sql-op-pattern">sql-op :~, :~*, :!~, :!~* (string pattern)</a></li>
<li><a href="#sql-op-like">sql-op :like, :ilike (string pattern)</a></li>
<li><a href="#sql-op-double-ampersand">sql-op :@@</a></li>
<li><a href="#sql-op-desc">sql-op :desc (column)</a></li>
<li><a href="#sql-op-nulls-first">sql-op :nulls-first, :nulls-last (column)</a></li>
<li><a href="#sql-op-as">sql-op :as (form name &amp;rest fields)</a></li>
<li><a href="#sql-op-cast">sql-op :cast (query)</a></li>
<li><a href="#sql-op-type-query">sql-op :type (query)</a></li>
<li><a href="#sql-op-type-form">sql-op :type (form type)</a></li>
<li><a href="#sql-op-create-composite-type">sql-op :create-composite-type (type-name &amp;rest args)</a></li>
<li><a href="#sql-op-exists">sql-op :exists (query)</a></li>
<li><a href="#sql-op-is-false">sql-op :is-false (arg)</a></li>
<li><a href="#sql-op-is-true">sql-op :is-true (arg)</a></li>
<li><a href="#sql-op-is-null">sql-op :is-null (arg)</a></li>
<li><a href="#sql-op-not-null">sql-op :not-null (arg)</a></li>
<li><a href="#sql-op-in">sql-op :in (value set)</a></li>
<li><a href="#sql-op-not-in">sql-op :not-in (value set)</a></li>
<li><a href="#sql-op-set">sql-op :set (&amp;rest elements)</a></li>
<li><a href="#sql-op-array">sql-op :array (query)</a></li>
<li><a href="#sql-op-array-rest">sql-op :array[] (&amp;rest args)</a></li>
<li><a href="#sql-op-square-brackets">sql-op :[] (form start &amp;optional end)</a></li>
<li><a href="#sql-op-extract">sql-op :extract (unit form)</a></li>
<li><a href="#sql-op-case">sql-op :case (&amp;rest clauses)</a></li>
<li><a href="#sql-op-between">sql-op :between (n start end)</a></li>
<li><a href="#sql-op-between-symmetric">sql-op :between-symmetric (n start end)</a></li>
<li><a href="#sql-op-dot">sql-op :dot (&amp;rest names)</a></li>
<li><a href="#sql-op-raw-string">sql-op :raw (string)</a></li>
<li><a href="#sql-op-fetch">sql-op :fetch (form amount &amp;optional offset)</a></li>
<li><a href="#sql-op-limit">sql-op :limit (query amount &amp;optional offset)</a></li>
<li><a href="#sql-op-order-by">sql-op :order-by (query &amp;rest exprs)</a></li>
<li><a href="#sql-op-values">sql-op :values</a></li>
<li><a href="#sql-op-empty-set">sql-op :empty-set</a></li>
<li><a href="#sql-op-group-by">sql-op :group-by</a></li>
<li><a href="#sql-op-grouping-sets">sql-op :grouping-sets</a></li>
</ul>
</li>
<li><a href="#sql-op-time-date-and-interval">Time, Date and Interval Operators</a>
<ul>
<li><a href="#sql-op-interval">sql-op :interval (arg)</a></li>
<li><a href="#sql-op-current-date">sql-op :current-date ()</a></li>
<li><a href="#sql-op-current-time">sql-op :current-time ()</a></li>
<li><a href="#sql-op-current-timestamp">sql-op :current-timestamp ()</a></li>
<li><a href="#sql-op-timestamp">sql-op :timestamp (arg)</a></li>
<li><a href="#sql-op-age">sql-op :age (&amp;rest args)</a></li>
<li><a href="#sql-op-date">sql-op :date (arg)</a></li>
<li><a href="#sql-op-make-insterval">sql-op :make-interval (&amp;rest args)</a></li>
<li><a href="#sql-op-make-timestamp">sql-op :make-timestamp (&amp;rest args)</a></li>
<li><a href="#sql-op-make-timestamptz">sql-op :make-timestamptz (&amp;rest args)</a></li>
</ul>
</li>
<li><a href="#sql-op-aggregation-operators">Aggregation Operators</a>
<ul>
<li><a href="#sql-op-count">sql-op :count (&amp;rest args)</a></li>
<li><a href="#sql-op-avg">sql-op :avg (&amp;rest rest args)</a></li>
<li><a href="#sql-op-sum">sql-op :sum (&amp;rest rest args)</a></li>
<li><a href="#sql-op-max">sql-op ::max (&amp;rest args)</a></li>
<li><a href="#sql-op-min">sql-op ::min (&amp;rest args)</a></li>
<li><a href="#sql-op-every">sql-op ::every (&amp;rest args)</a></li>
<li><a href="#sql-op-percentile-cont">sql-op :percentile-cont (&amp;rest args)</a></li>
<li><a href="#sql-op-percentile-dist">sql-op :percentile-dist (&amp;rest args)</a></li>
<li><a href="#sql-op-corr">sql-op :corr (y x)</a></li>
<li><a href="#sql-op-covar-pop">sql-op :covar-pop (y x)</a></li>
<li><a href="#sql-op-covar-samp">sql-op :covar-samp (y x)</a></li>
<li><a href="#sql-op-string-agg">sql-op :string-agg (&amp;rest args)</a></li>
<li><a href="#sql-op-array-agg">sql-op :array-agg (&amp;rest args)</a></li>
<li><a href="#sql-op-mode">sql-op :mode (&amp;rest args)</a></li>
<li><a href="#sql-op-regr-avgx">sql-op :regr_avgx (y x)</a></li>
<li><a href="#sql-op-regr-avgy">sql-op :regr_avgy (y x)</a></li>
<li><a href="#sql-op-regr-count">sql-op :regr_count (y x)</a></li>
<li><a href="#sql-op-regr-intercept">sql-op :regr_intercept (y x)</a></li>
<li><a href="#sql-op-regr-r2">sql-op :regr_r2 (y x)</a></li>
<li><a href="#sql-op-regr-slope">sql-op :regr_slope (y x)</a></li>
<li><a href="#sql-op-regr-sxx">sql-op :regr_sxx (y x)</a></li>
<li><a href="#sql-op-regr-sxy">sql-op :regr_sxy (y x)</a></li>
<li><a href="#sql-op-regr-syy">sql-op :regr_syy (y x)</a></li>
<li><a href="#sql-op-stddev">sql-op :stddev (&amp;rest args)</a></li>
<li><a href="#sql-op-stddev-pop">sql-op :stddev-pop (&amp;rest args)</a></li>
<li><a href="#sql-op-stddev-samp">sql-op :stddev-samp (&amp;rest args)</a></li>
<li><a href="#sql-op-variance">sql-op :variance (&amp;rest args)</a></li>
<li><a href="#sql-op-var-pop">sql-op :var-pop (&amp;rest args)</a></li>
<li><a href="#sql-op-var-samp">sql-op :var-samp (&amp;rest args)</a></li>
<li><a href="#org2555f16">sql-op :range-between (&amp;rest args)</a></li>
<li><a href="#orgfb601b4">sql-op :rows-between (&amp;rest args)</a></li>
<li><a href="#sql-op-over">sql-op :over (form &amp;rest args)</a></li>
<li><a href="#sql-op-partition-by">sql-op :partition-by (&amp;rest args)</a></li>
<li><a href="#sql-op-window">sql-op :window (form)</a></li>
<li><a href="#sql-op-with">sql-op :with (&amp;rest args)</a></li>
<li><a href="#sql-op-with-recursive">sql-op :with-recursive (&amp;rest args)</a></li>
<li><a href="#org6f9c3d3">sql-op :with-ordinality, :with-ordinality-as</a></li>
</ul>
</li>
<li><a href="#table-functions">Table Functions</a>
<ul>
<li><a href="#sql-op-for-update">sql-op :for-update (query &amp;key of nowait)</a></li>
<li><a href="#sql-op-for-share">sql-op :for-share (query &amp;key of nowait)</a></li>
<li><a href="#sql-op-insert-into">sql-op :insert-into (table &amp;rest rest)</a></li>
<li><a href="#sql-op-insert-rows-into">sql-op :insert-rows-into (table &amp;rest rest)</a></li>
<li><a href="#sql-op-update">sql-op :update (table &amp;rest rest)</a></li>
<li><a href="#sql-op-delete-from">sql-op :delete-from (table &amp;rest rest)</a></li>
<li><a href="#sql-op-create-table">sql-op :create-table (name (&amp;rest columns) &amp;rest options)</a>
<ul>
<li><a href="#sql-op-column-definition-parameters">Column Definition parameters</a></li>
<li><a href="#sql-op-table-constraints">Table Constraints</a></li>
</ul>
</li>
<li><a href="#sql-op-alter-table">sql-op :alter-table (name action &amp;rest args)</a></li>
<li><a href="#sql-op-drop-table">sql-op :drop-table (name)</a></li>
<li><a href="#sql-op-truncate">sql-op :truncate (&amp;rest args)</a></li>
<li><a href="#sql-op-create-index">sql-op :create-index (name &amp;rest args)</a></li>
<li><a href="#sql-op-create-unique-index">sql-op :create-unique-index (name &amp;rest args)</a></li>
<li><a href="#sql-op-drop-index">sql-op :drop-index (name)</a></li>
<li><a href="#sql-op-create-sequence">sql-op :create-sequence (name &amp;key increment min-value max-value start cache cycle)</a></li>
<li><a href="#sql-op-alter-sequence">sql-op :alter-sequence (name)</a></li>
<li><a href="#sql-op-drop-sequence">sql-op :drop-sequence (name)</a></li>
<li><a href="#sql-op-create-view">sql-op :create-view (name query)</a></li>
<li><a href="#sql-op-drop-view">sql-op :drop-view (name)</a></li>
<li><a href="#sql-op-set-constraints">sql-op :set-constraints (state &amp;rest constraints)</a></li>
<li><a href="#sql-op-listen">sql-op :listen (channel)</a></li>
<li><a href="#sql-op-unlisten">sql-op :unlisten (channel)</a></li>
<li><a href="#sql-op-notify">sql-op :notify (channel &amp;optional payload)</a></li>
<li><a href="#sql-op-create-role">sql-op :create-role (role &amp;rest args)</a></li>
<li><a href="#sql-op-create-database">sql-op :create-database (name)</a></li>
<li><a href="#sql-op-drop-database">sql-op :drop-database (name)</a></li>
<li><a href="#sql-op-copy">sql-op :copy (table &amp;rest args)</a></li>
</ul>
</li>
<li><a href="#dynamic-queries-composition-and-parameterized-queries">Dynamic Queries, Composition and Parameterized Queries</a>
<ul>
<li><a href="#dynamic-queries-overview">Overview</a>
<ul>
<li><a href="#programmer-built-queries">Programmer Built Queries</a></li>
<li><a href="#approach-use-sql-compile">Approach #2 Use sql-compile</a></li>
<li><a href="#raw-approach">Approach #3 Use :raw</a></li>
<li><a href="#queries-with-user-input">Queries with User Input</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</nav>
<p>
This is the reference manual for the S-SQL component of the postmodern library.
</p>

<p>
S-SQL provides a lispy syntax for SQL queries, and knows how to convert various
lisp types to their textual SQL representation. It takes care to do as much of
the work as possible at compile-time, so that at runtime a string concatenation
is all that is needed to produce the final SQL query.
</p>

<div id="outline-container-interface" class="outline-2">
<h2 id="interface">Interface</h2>
<div class="outline-text-2" id="text-interface">
</div>
<div id="outline-container-macro-sql" class="outline-3">
<h3 id="macro-sql">macro sql (form)</h3>
<div class="outline-text-3" id="text-macro-sql">
<p>
→ string
</p>

<p>
Convert the given form (a list starting with a keyword) to an SQL query string
at compile time, according to the rules described here. For example:
</p>
<div class="org-src-container">
<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)))

 <span style="color: #e67128;">"(SELECT * FROM country WHERE (a = 1))"</span>
</pre>
</div>

<p>
but
</p>
<div class="org-src-container">
<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)))
</pre>
</div>

<p>
would throw an error. For the later case you need to use sql-compile.
</p>
</div>
</div>

<div id="outline-container-function-sql-compile" class="outline-3">
<h3 id="function-sql-compile">function sql-compile (form)</h3>
<div class="outline-text-3" id="text-function-sql-compile">
<p>
→ string
</p>

<p>
This is the run-time variant of the sql macro. It converts the given list to
an SQL query, with the same rules except that symbols in this list do not
have to be quoted to be interpreted as identifiers. For example:
</p>
<div class="org-src-container">
<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)))

\"(SELECT * FROM country WHERE (a = 1))\"
</pre>
</div>

<p>
but
</p>
<div class="org-src-container">
<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)))
</pre>
</div>

<p>
would throw an error. For the later case you need to use sql.
</p>
</div>
</div>

<div id="outline-container-function-sql-template" class="outline-3">
<h3 id="function-sql-template">function sql-template (form)</h3>
<div class="outline-text-3" id="text-function-sql-template">
<p>
In cases where you do need to build the query at run time, yet you do not
want to re-compile it all the time, this function can be used to compile it
once and store the result. It takes an S-SQL form, which may contain
\[ placeholder symbols, and returns a function that takes one argument for
every \]. When called, this returned function produces an SQL string in
which the placeholders have been replaced by the values of the arguments.
</p>
</div>
</div>

<div id="outline-container-function-enable-s-sql-syntax" class="outline-3">
<h3 id="function-enable-s-sql-syntax">function enable-s-sql-syntax (&amp;optional (char #\Q))</h3>
<div class="outline-text-3" id="text-function-enable-s-sql-syntax">
<p>
Modifies the current readtable to add a #Q syntax that is read as (sql &#x2026;).
The character to use can be overridden by passing an argument.
</p>
</div>
</div>
<div id="outline-container-function-sql-escape-string" class="outline-3">
<h3 id="function-sql-escape-string">function sql-escape-string (string)</h3>
<div class="outline-text-3" id="text-function-sql-escape-string">
<p>
→ string
</p>

<p>
<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.
Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(sql-escape-string \"Puss in 'Boots'\")

\"E'Puss in ''Boots'''\"
</pre>
</div>
</div>
</div>

<div id="outline-container-method-sql-escape" class="outline-3">
<h3 id="method-sql-escape">method sql-escape (value)</h3>
<div class="outline-text-3" id="text-method-sql-escape">
<p>
→ string
</p>

<p>
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
converted to SQL names. Examples:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(sql-escape <span style="color: #e67128;">"tr'-x"</span>)

<span style="color: #e67128;">"E'tr''-x'"</span>

(sql-escape (/ 1 13))

<span style="color: #e67128;">"0.0769230769230769230769230769230769230"</span>

(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>))

<span style="color: #e67128;">"ARRAY[E'Baden-Wurttemberg', E'Bavaria', E'Berlin', E'Brandenburg']"</span>
</pre>
</div>
</div>
</div>
<div id="outline-container-variable-downcase-symbols" class="outline-3">
<h3 id="variable-downcase-symbols">variable <code>*downcase-symbols*</code></h3>
<div class="outline-text-3" id="text-variable-downcase-symbols">
<p>
When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.
</p>
</div>
</div>
<div id="outline-container-variable-standard-sql-strings" class="outline-3">
<h3 id="variable-standard-sql-strings">variable <code>*standard-sql-strings*</code></h3>
<div class="outline-text-3" id="text-variable-standard-sql-strings">
<p>
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.
</p>
</div>
</div>

<div id="outline-container-variable-postgres-reserved-words" class="outline-3">
<h3 id="variable-postgres-reserved-words">variable <code>*postgres-reserved-words*</code> hashtable</h3>
<div class="outline-text-3" id="text-variable-postgres-reserved-words">
<p>
A set of all Postgresql's reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway.
 '("all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" "authorization"
   "between" "binary" "both" "case" "cast" "check" "collate" "column" "concurrently"
   "constraint" "create" "cross" "current-catalog" "current-date" "current-role" "current-schema"
   "current-time" "current-timestamp" "current-user" "default" "deferrable"
   "desc" "distinct" "do" "else" "end" "except" "false" "fetch" "filter"
   "for" "foreign" "freeze" "from" "full" "grant" "group" "having" "ilike" "in" "initially"
   "inner" "intersect" "into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit"
   "localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" "off" "offset" "old"
   "on" "only" "or" "order" "outer" "overlaps" "placing" "primary" "references" "returning"
   "right" "select" "session-user" "Share" "similar" "some" "symmetric" "table" "then" "to" "trailing" "true"
   "union" "unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with"))
</p>
</div>
</div>

<div id="outline-container-variable-escape-sql-names-p" class="outline-3">
<h3 id="variable-escape-sql-names-p">variable <code>*escape-sql-names-p*</code></h3>
<div class="outline-text-3" id="text-variable-escape-sql-names-p">
<p>
Determines whether double quotes are added around column, table, and ** function names in
queries. Valid values:
</p>

<ul class="org-ul">
<li>T, in which case every name is escaped,</li>
<li>NIL, in which case no name is escape,</li>
<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>
<li>:literal which is the same as :auto except it has added consequence in to-sql-name (see below).</li>
</ul>

<p>
The default value is :auto.
</p>

<p>
Be careful when binding this with let and such ― since a lot of SQL compilation tends to happen at
compile-time, the result might not be what you expect. Mixed case sensitivity is not currently
well supported. Postgresql itself will downcase unquoted identifiers. This will be revisited in the future if requested.
</p>
</div>
</div>

<div id="outline-container-function-sql-type-name" class="outline-3">
<h3 id="function-sql-type-name">function sql-type-name (type)</h3>
<div class="outline-text-3" id="text-function-sql-type-name">
<p>
→ string
</p>

<p>
Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol's name.
</p>
</div>
</div>

<div id="outline-container-function-to-sql-name" class="outline-3">
<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>
<div class="outline-text-3" id="text-function-to-sql-name">
<p>
→ string
</p>

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

<p>
Ignore-reserved-words is only used internally for column names which are allowed to be reserved words, but it is not recommended.
</p>
</div>
</div>

<div id="outline-container-function-from-sql-name" class="outline-3">
<h3 id="function-from-sql-name">function from-sql-name (string)</h3>
<div class="outline-text-3" id="text-function-from-sql-name">
<p>
→ keyword
</p>

<p>
Convert a string that represents an SQL identifier to a keyword by uppercasing
it and converting the underscores to dashes.
</p>
</div>
</div>

<div id="outline-container-macro-register-sql-operators" class="outline-3">
<h3 id="macro-register-sql-operators">macro register-sql-operators (arity &amp;rest names)</h3>
<div class="outline-text-3" id="text-macro-register-sql-operators">
<p>
Define simple SQL operators. Arity is one of :unary (like 'not'), :unary-postfix
(the operator comes after the operand), :n-ary (like '\+': the operator falls away
when there is only one operand), :2+-ary (like '=', which is meaningless for one
operand), or :n-or-unary (like '-', where the operator is kept in the unary case).
After the arity may follow any number of operators, either just a keyword, in
which case the downcased symbol name is used as the SQL operator, or a two-element
list containing a keyword and a name string.
</p>
</div>
</div>
</div>
<div id="outline-container-sql-types" class="outline-2">
<h2 id="sql-types">SQL Types</h2>
<div class="outline-text-2" id="text-sql-types">
<p>
S-SQL knows the SQL equivalents to a number of Lisp types, and defines some
extra types that can be used to denote other SQL types. The following
table (yes, I know this table is duplicated on other pages) shows the correspondence:
</p>

<table>


<colgroup>
<col  class="org-left">

<col  class="org-left">

<col  class="org-left">
</colgroup>
<thead>
<tr>
<th scope="col" class="org-left">Lisp type</th>
<th scope="col" class="org-left">SQL type</th>
<th scope="col" class="org-left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="org-left">integer</td>
<td class="org-left">smallint</td>
<td class="org-left">-32,768 to +32,768 2-byte storage</td>
</tr>

<tr>
<td class="org-left">integer</td>
<td class="org-left">integer</td>
<td class="org-left">-2147483648 to +2147483647 integer, 4-byte storage</td>
</tr>

<tr>
<td class="org-left">integer</td>
<td class="org-left">bigint</td>
<td class="org-left">-9223372036854775808 to 9223372036854775807 8-byte storage</td>
</tr>

<tr>
<td class="org-left">(numeric X Y)</td>
<td class="org-left">numeric(X, Y)</td>
<td class="org-left">see discussion below</td>
</tr>

<tr>
<td class="org-left">float, real</td>
<td class="org-left">real</td>
<td class="org-left">float, 6 decimal digit precision 4-byte storage</td>
</tr>

<tr>
<td class="org-left">double-float</td>
<td class="org-left">double-precision</td>
<td class="org-left">float, 15 decimal digit precision 8-byte storage</td>
</tr>

<tr>
<td class="org-left">string, text</td>
<td class="org-left">text</td>
<td class="org-left">variable length string, no limit specified</td>
</tr>

<tr>
<td class="org-left">string</td>
<td class="org-left">char(X)</td>
<td class="org-left">char(length), blank-padded string, fixed storage length</td>
</tr>

<tr>
<td class="org-left">string</td>
<td class="org-left">varchar(X)</td>
<td class="org-left">varchar(length), non-blank-padded string, variable storage</td>
</tr>

<tr>
<td class="org-left">boolean</td>
<td class="org-left">boolean</td>
<td class="org-left">boolean, 'true'/'false', 1 byte</td>
</tr>

<tr>
<td class="org-left">bytea</td>
<td class="org-left">bytea</td>
<td class="org-left">binary string which allows non-printable octets</td>
</tr>

<tr>
<td class="org-left">date</td>
<td class="org-left">date</td>
<td class="org-left">date range: 4713 BC to 5874897 AD</td>
</tr>

<tr>
<td class="org-left"><a href="interval-notes.html">interval</a></td>
<td class="org-left">interval</td>
<td class="org-left">time intervals</td>
</tr>

<tr>
<td class="org-left">array</td>
<td class="org-left">array</td>
<td class="org-left">See <a href="array-notes.html">Array-Notes</a></td>
</tr>
</tbody>
</table>

<p>
Numeric and decimal are variable storage size numbers with user specified precision.
Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
The syntax is numeric(precision, scale). Numeric columns with a specified scale will coerce input
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>
</p>
</div>

<div id="outline-container-type-db-null" class="outline-3">
<h3 id="type-db-null">type db-null</h3>
<div class="outline-text-3" id="text-type-db-null">
<p>
This is a type of which only the keyword :null is a member. It is used to represent
NULL values from the database.
</p>
</div>
</div>
</div>

<div id="outline-container-sql-syntax" class="outline-2">
<h2 id="sql-syntax">SQL Syntax</h2>
<div class="outline-text-2" id="text-sql-syntax">
<p>
An S-SQL form is converted to a query through the following rules:
</p>

<ul class="org-ul">
<li>Lists starting with a keyword are operators. They are expanded as
described below if they are known, otherwise they are expanded in the
standard way: operator(arguments, &#x2026;)</li>

<li>Quoted symbols or keywords are interpreted as names of columns or
tables, and converted to strings with to-sql-name.</li>

<li>Anything else is evaluated and the resulting Lisp value is converted
to its textual SQL representation (or an error is raised when there is
no rule for converting objects of this type). Self-quoting atoms may
be converted to strings at compile-time.</li>
</ul>
</div>

<div id="outline-container-sql-op-select" class="outline-3">
<h3 id="sql-op-select">sql-op :select (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-select">
<p>
Creates a select query. The arguments are split on the keywords found among
them. The group of arguments immediately after :select is interpreted as
the expressions that should be selected. After this, an optional :distinct
may follow, which will cause the query to only select distinct rows, or
alternatively :distinct-on followed by a group of row names. Next comes the
optional keyword :from, followed by at least one table name and then any
number of join statements.
</p>

<p>
Join statements start with one of :join, :left-join,
:right-join, :inner-join, :outer-join, :cross-join (or those with -lateral,
e.g :join-lateral, :left-join-lateral, :right-join-lateral, :inner-join-lateral, :outer-join-lateral).
S-sql will accept :join, but best usage is to explicitly use :inner-join instead.
</p>

<p>
Then comes a table name or subquery,
</p>

<p>
Then there is an optional :with-ordinality or :with-ordinality-as alisa
</p>

<p>
Then the keyword :on or :using, if applicable, and then a form.
A join can be preceded by :natural (leaving off the :on clause) to use a
natural join.
</p>

<p>
After the joins an optional :where followed by a single form may occur.
</p>

<p>
Finally :group-by and :having can optionally be specified.
The first takes any number of arguments, and the second only one.
</p>

<p>
A few examples:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'item <span style="color: #23d7d7;">:distinct</span>
        <span style="color: #23d7d7;">:from</span> 'item-table
        <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'col1 <span style="color: #e67128;">"Albania"</span>)))

(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'field-1 100) 'field-5
        <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'my-table 'x)
        <span style="color: #23d7d7;">:left-join</span> 'your-table
        <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'x.field-2 'your-table.field-1)
        <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not-null</span> 'a.field-3)))

(query (<span style="color: #23d7d7;">:order-by</span>
         (<span style="color: #23d7d7;">:select</span> 'regions.name
            (<span style="color: #23d7d7;">:count</span> 'regions.name)
          <span style="color: #23d7d7;">:from</span> 'countries 'regions
          <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id)
          <span style="color: #23d7d7;">:group-by</span> 'regions.name)
        'regions.name))

(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> 'c.id) 'r.name
        <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'countries 'c)
        <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'regions 'r)
        <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'c.region-id 'r.id)
        <span style="color: #23d7d7;">:group-by</span> 'r.name
        <span style="color: #23d7d7;">:having</span> (<span style="color: #23d7d7;">:&lt;</span> (<span style="color: #23d7d7;">:count</span> 'c.id) 10)))

(query (<span style="color: #23d7d7;">:select</span> 'i.* 'p.*
        <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'individual 'i)
        <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'publisher 'p)
        <span style="color: #23d7d7;">:using</span> ('individualid)
        <span style="color: #23d7d7;">:left-join-lateral</span> (<span style="color: #23d7d7;">:as</span> 'anothertable 'a)
        <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'a.identifier 'i.individualid)
        <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'a.something \"something\")))

(query (<span style="color: #23d7d7;">:select</span> 't1.id 'a.elem 'a.nr
        <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 't12 't1)
        <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>))
        <span style="color: #23d7d7;">:with-ordinality-as</span> (<span style="color: #23d7d7;">:a</span> 'elem 'nr)
        <span style="color: #23d7d7;">:on</span> 't))
</pre>
</div>
<p>
Other examples can be found in s-sql/tests/tests.lisp
</p>
</div>
</div>
<div id="outline-container-e0f01ac7-cb3c-4b38-8902-dc4a981a15e8" class="outline-3">
<h3 id="e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">Joins</h3>
<div class="outline-text-3" id="text-e0f01ac7-cb3c-4b38-8902-dc4a981a15e8">
<p>
Allowable join keywords are:
</p>
<ul class="org-ul">
<li>:left-join</li>
<li>:right-join</li>
<li>:inner-join</li>
<li>:outer-join</li>
<li>:cross-join</li>
<li>:join-lateral</li>
<li>:left-join-lateral (left join with an additional sql keyword LATERAL)</li>
<li>:right-join-lateral (right join with an additional sql keyword LATERAL)</li>
<li>:inner-join-lateral (inner join with an additional sql keyword LATERAL)</li>
<li>:outer-join-lateral (outer join with an additional sql keyword LATERAL)</li>
<li>:cross-join-lateral (cross join with an additional sql keyword LATERAL)</li>
</ul>

<p>
The lateral joins will not be discussed separately.
</p>
</div>
<div id="outline-container-40e45849-5e9d-4b4c-830b-53f79f0b21e2" class="outline-4">
<h4 id="40e45849-5e9d-4b4c-830b-53f79f0b21e2">Cross Join/ Cross Join Lateral</h4>
<div class="outline-text-4" id="text-40e45849-5e9d-4b4c-830b-53f79f0b21e2">
<p>
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."
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '* from 'employee <span style="color: #23d7d7;">:cross-join</span> 'compensation))
</pre>
</div>
</div>
</div>

<div id="outline-container-85c25a7d-3660-4d38-85f0-2b9c9dc88684" class="outline-4">
<h4 id="85c25a7d-3660-4d38-85f0-2b9c9dc88684">Inner Join / Inner Join Lateral</h4>
<div class="outline-text-4" id="text-85c25a7d-3660-4d38-85f0-2b9c9dc88684">
<p>
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.
</p>

<p>
A sample of standard sql using two inner joins to collect information from three
tables could look like this:
</p>
<div class="org-src-container">
<pre class="src src-sql">(<span style="color: #ffad29; font-weight: bold;">SELECT</span> foo, bar, baz
 <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
 <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
 <span style="color: #ffad29; font-weight: bold;">ON</span> (tmp1.id = tmp2.id)
 <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
 <span style="color: #ffad29; font-weight: bold;">ON</span> (tmp2.id = tmp3.id))
</pre>
</div>

<p>
The same query could be expressed in s-sql as:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'foo 'bar 'baz
                    <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span>
                           (<span style="color: #23d7d7;">:select</span> 'foo
                                    <span style="color: #23d7d7;">:from</span> 'x
                                    <span style="color: #23d7d7;">:where</span> 'x) <span style="color: #ff4242; font-weight: bold;">'tmp1)</span>
                    <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span>
                                 (<span style="color: #23d7d7;">:select</span> 'bar
                                          <span style="color: #23d7d7;">:from</span> 'x
                                          <span style="color: #23d7d7;">:where</span> 'x) <span style="color: #ff4242; font-weight: bold;">'tmp2)</span>
                    <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.id 'tmp2.id)
                    <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span>
                                 (<span style="color: #23d7d7;">:select</span> 'baz
                                          <span style="color: #23d7d7;">:from</span> 'x
                                          <span style="color: #23d7d7;">:where</span> 'x)
                                 'tmp3)
                    <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp2.id 'tmp3.id)))
</pre>
</div>

<p>
The pre-ansi shorthand example, using a countries and regions tables would look like this:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
                <span style="color: #23d7d7;">:from</span> 'countries 'regions
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'countries.region-id 'regions.id)
                             (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>))))
</pre>
</div>
<p>
The full portable ansi version, using inner join would look like this.
</p>
<div class="org-src-container">
<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
                                               <span style="color: #23d7d7;">:from</span> 'countries)
                                 'tmp1)
                <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'id
                                          <span style="color: #23d7d7;">:from</span> 'regions
                                          <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'name <span style="color: #e67128;">"North America"</span>))
                                 'tmp2)
                <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.region-id 'tmp2.id)))
</pre>
</div>
</div>
</div>

<div id="outline-container-ee0a6fef-de2f-407e-9cc9-3667de7775dc" class="outline-4">
<h4 id="ee0a6fef-de2f-407e-9cc9-3667de7775dc">Outer Join / Outer Join Lateral</h4>
<div class="outline-text-4" id="text-ee0a6fef-de2f-407e-9cc9-3667de7775dc">
<p>
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.
</p>

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

<div id="outline-container-3061c378-d2d1-4dda-833a-f1b3f8569018" class="outline-4">
<h4 id="3061c378-d2d1-4dda-833a-f1b3f8569018">Left Join / Left Join Lateral / Right Join / Right Join Lateral</h4>
<div class="outline-text-4" id="text-3061c378-d2d1-4dda-833a-f1b3f8569018">
<p>
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?
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
                               <span style="color: #23d7d7;">:distinct</span> <span style="color: #23d7d7;">:from</span> 'countries
      <span style="color: #23d7d7;">:left-join</span> 'countries-topics
      <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'countries.id 'countries-topics.country-id)
      <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:is-null</span> 'countries-topics.country-id))
                      'countries.id))
</pre>
</div>

<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'tmp2.name 'tmp1.name
         <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'id 'name
                     <span style="color: #23d7d7;">:from</span> 'regions)
                'tmp2)
         <span style="color: #23d7d7;">:left-join</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:select</span> 'name 'region-id
                          <span style="color: #23d7d7;">:from</span> 'countries
                          <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'region-id 3))
                     'tmp1)
         <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'tmp1.region-id 'tmp2.id)))
</pre>
</div>
</div>
</div>
</div>
</div>

<div id="outline-container-defined-operators" class="outline-2">
<h2 id="defined-operators">Defined Operators</h2>
<div class="outline-text-2" id="text-defined-operators">
<p>
The following operators are defined:
</p>
</div>

<div id="outline-container-sql-op-misc" class="outline-3">
<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>
<div class="outline-text-3" id="text-sql-op-misc">
<p>
These are expanded as infix operators. When meaningful, they allow more than
two arguments. :- can also be used as a unary operator to negate a value.
Note that the arguments to :union, :union-all, :intersect, and :except
should be queries (:select forms).
</p>

<p>
Note that you'll have to escape pipe characters to enter them as keywords. S-SQL
handles the empty keyword symbol (written :||) specially, and treats it like :\|\|,
so that it can be written without escapes. With :\|, this doesn't work.
</p>
</div>
</div>
<div id="outline-container-sql-op-or" class="outline-3">
<h3 id="sql-op-or">sql-op :or</h3>
<div class="outline-text-3" id="text-sql-op-or">
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
                <span style="color: #23d7d7;">:from</span> 'countries 'regions
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                        (<span style="color: #23d7d7;">:or</span> (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
                             (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Central America"</span>))
                        (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id))))
</pre>
</div>
<p>
or using parameterized queries
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'countries.name
                <span style="color: #23d7d7;">:from</span> 'countries 'regions
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                        (<span style="color: #23d7d7;">:or</span> (<span style="color: #23d7d7;">:=</span> 'regions.name '$1)
                             (<span style="color: #23d7d7;">:=</span> 'regions.name '$2))
                        (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id)))
  <span style="color: #e67128;">"North America"</span> <span style="color: #e67128;">"Central America"</span>)
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-intersect" class="outline-3">
<h3 id="sql-op-intersect">sql-op :intersect</h3>
<div class="outline-text-3" id="text-sql-op-intersect">
<p>
Intersect produces a result contain rows that appear on all the sub-selects.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:intersect</span> (<span style="color: #23d7d7;">:select</span> 'countries.name
                            <span style="color: #23d7d7;">:from</span> 'countries
                            <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'latitude 16.44))
                   (<span style="color: #23d7d7;">:select</span> 'countries.name
                            <span style="color: #23d7d7;">:from</span> 'countries 'regions
                            <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'region-id 'regions.id)
                                         (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Caribbean"</span>)))))
</pre>
</div>
</div>
</div>
<div id="outline-container-026a2773-c17f-4cb2-a86c-34babf8c48a2" class="outline-3">
<h3 id="026a2773-c17f-4cb2-a86c-34babf8c48a2">sql-op :union, :union-all</h3>
<div class="outline-text-3" id="text-026a2773-c17f-4cb2-a86c-34babf8c48a2">
<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:union-all</span>
                            (<span style="color: #23d7d7;">:select</span> 'id 'name <span style="color: #23d7d7;">:from</span> 'countries
                                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;=</span> 'name <span style="color: #e67128;">"B"</span> ))
                            (<span style="color: #23d7d7;">:select</span> 'id 'name <span style="color: #23d7d7;">:from</span> 'countries
                                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&gt;=</span> 'name <span style="color: #e67128;">"V"</span> )))
                           'a)))

(query (<span style="color: #23d7d7;">:select</span> 'a.id 'a.name 'a.region
                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:union-all</span>
                            (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
                                     (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
                                     <span style="color: #23d7d7;">:from</span> 'countries 'regions
                                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                                             (<span style="color: #23d7d7;">:&lt;=</span> 'countries.name <span style="color: #e67128;">"B"</span> )
                                             (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id )))
                            (<span style="color: #23d7d7;">:select</span> 'countries.id 'countries.name
                                     (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
                                     <span style="color: #23d7d7;">:from</span> 'countries 'regions
                                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                                             (<span style="color: #23d7d7;">:&gt;=</span> 'countries.name <span style="color: #e67128;">"V"</span> )
                                             (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id ))))
                           'a)
                <span style="color: #23d7d7;">:group-by</span> 'a.id 'a.region 'a.name))
</pre>
</div>
</div>
</div>

<div id="outline-container-4f77625f-4b6e-417d-8b56-d76835d6832d" class="outline-3">
<h3 id="4f77625f-4b6e-417d-8b56-d76835d6832d">sql-op :except, :except-all</h3>
<div class="outline-text-3" id="text-4f77625f-4b6e-417d-8b56-d76835d6832d">
<p>
:except removes all matches. :except-all is slightly different.
If the first select statement has two rows that match a single row in the second
select statement, only one is removed.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:except</span> (<span style="color: #23d7d7;">:select</span> 'id 'name
                         <span style="color: #23d7d7;">:from</span> 'countries
                         <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%New%"</span>))
                (<span style="color: #23d7d7;">:select</span> 'id 'name
                         <span style="color: #23d7d7;">:from</span> 'countries
                         <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%Zealand%"</span>))))

(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)))

</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-not" class="outline-3">
<h3 id="sql-op-not">sql-op :~, :not (arg)</h3>
<div class="outline-text-3" id="text-sql-op-not">
<p>
Unary operators for bitwise and logical negation.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:select</span> 'recommendedby
                           (<span style="color: #23d7d7;">:count</span> '*)
                           <span style="color: #23d7d7;">:from</span> 'cd.members
                           <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:not</span> (<span style="color: #23d7d7;">:is-null</span> 'recommendedby))
                           <span style="color: #23d7d7;">:group-by</span> 'recommendedby)
                  'recommendedby))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-any" class="outline-3">
<h3 id="sql-op-any">sql-op :any, :any*</h3>
<div class="outline-text-3" id="text-sql-op-any">
<p>
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.
</p>

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

<p>
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:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query <span style="color: #e67128;">"select countries.name</span>
<span style="color: #e67128;">        from countries,regions</span>
<span style="color: #e67128;">        where regions.id=region_id</span>
<span style="color: #e67128;">              and regions.name='North America'</span>
<span style="color: #e67128;">              and longitude &gt; any(select longitude</span>
<span style="color: #e67128;">                                         from countries, regions</span>
<span style="color: #e67128;">                                         where region_id = regions.id</span>
<span style="color: #e67128;">                                         and regions.name='South America')"</span>)
((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
</pre>
</div>

<p>
This can be re-phrased in s-sql as
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
 (<span style="color: #23d7d7;">:select</span> 'countries.name
          <span style="color: #23d7d7;">:from</span> 'countries 'regions
          <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
                       (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
                       (<span style="color: #23d7d7;">:&gt;</span> 'longitude
                           (<span style="color: #23d7d7;">:any</span>
                            (<span style="color: #23d7d7;">:select</span> 'longitude
                                     <span style="color: #23d7d7;">:from</span> 'countries 'regions
                                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
                                                  (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"South America"</span>))))))))
((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
</pre>
</div>

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

<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query <span style="color: #e67128;">"select name from countries where id=any($1)"</span>
       (vector 21 22))
((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))

(<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
     (query <span style="color: #e67128;">"select name from countries where id=any($1)"</span>
       toy-query))
((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))

</pre>
</div>

<p>
Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
  (query (<span style="color: #23d7d7;">:select</span> 'name
                  <span style="color: #23d7d7;">:from</span> 'countries
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any</span> '$1)))
         toy-query))
<span style="color: #74af68;">; </span><span style="color: #74af68;">Evaluation aborted on #&lt;CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}&gt;.</span>

(<span style="color: #ffad29; font-weight: bold;">let</span> ((toy-query (vector 21 22)))
  (query (<span style="color: #23d7d7;">:select</span> 'name
                  <span style="color: #23d7d7;">:from</span> 'countries
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any*</span> '$1)))
         toy-query))
((<span style="color: #e67128;">"Iceland"</span>) (<span style="color: #e67128;">"US"</span>))
</pre>
</div>
<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((South-America
       (coerce
         (query (<span style="color: #23d7d7;">:select</span> 'longitude
                         <span style="color: #23d7d7;">:from</span> 'countries 'regions
                         <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
                                      (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"South America"</span>)))
                 <span style="color: #23d7d7;">:column</span>))
        'vector))
  (query (<span style="color: #23d7d7;">:select</span> 'countries.name
                  <span style="color: #23d7d7;">:from</span> 'countries 'regions
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
                               (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"North America"</span>)
                               (<span style="color: #23d7d7;">:&gt;</span> 'longitude
                                   (<span style="color: #23d7d7;">:any*</span> South-America))))))
((<span style="color: #e67128;">"Bermuda"</span>) (<span style="color: #e67128;">"Greenland"</span>))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-function" class="outline-3">
<h3 id="sql-op-function">sql-op :function (name (&amp;rest arg-types) return-type stability body)</h3>
<div class="outline-text-3" id="text-sql-op-function">
<p>
Create a stored procedure. The argument and return types are interpreted as
type names and not evaluated. Stability should be one of :immutable, :stable,
or :volatile (see the PostgreSQL documentation). For example, a function that
gets foobars by id:
</p>
<div class="org-src-container">
<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)))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-pattern" class="outline-3">
<h3 id="sql-op-pattern">sql-op :~, :~*, :!~, :!~* (string pattern)</h3>
<div class="outline-text-3" id="text-sql-op-pattern">
<p>
Regular expression matching operators. The exclamation mark means 'does not match',
the asterisk makes the match case-insensitive.
</p>
<div class="org-src-container">
<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>)

#(<span style="color: #e67128;">"barbeque"</span>)

(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>)

<span style="color: #23d7d7;">:NULL</span>

(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>)

t

(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>)

nil

(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>)

nil

(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>)

t

(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>)

nil

(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>)

t

(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>)))

</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-like" class="outline-3">
<h3 id="sql-op-like">sql-op :like, :ilike (string pattern)</h3>
<div class="outline-text-3" id="text-sql-op-like">
<p>
Simple SQL string matching operators (:ilike is case-insensitive).
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:like</span> 'name <span style="color: #e67128;">"%New%"</span>)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-double-ampersand" class="outline-3">
<h3 id="sql-op-double-ampersand">sql-op :@@</h3>
<div class="outline-text-3" id="text-sql-op-double-ampersand">
<p>
Fast Text Search match operator.
</p>
</div>
</div>

<div id="outline-container-sql-op-desc" class="outline-3">
<h3 id="sql-op-desc">sql-op :desc (column)</h3>
<div class="outline-text-3" id="text-sql-op-desc">
<p>
Used to invert the meaning of an operator in an :order-by clause.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
        (<span style="color: #23d7d7;">:select</span> 'location 'time 'report
                 <span style="color: #23d7d7;">:distinct-on</span> 'location
                 <span style="color: #23d7d7;">:from</span> 'weather-reports)
        'location  (<span style="color: #23d7d7;">:desc</span> 'time)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-nulls-first" class="outline-3">
<h3 id="sql-op-nulls-first">sql-op :nulls-first, :nulls-last (column)</h3>
<div class="outline-text-3" id="text-sql-op-nulls-first">
<p>
Used to determine where :null values appear in an :order-by clause.
</p>
</div>
</div>

<div id="outline-container-sql-op-as" class="outline-3">
<h3 id="sql-op-as">sql-op :as (form name &amp;rest fields)</h3>
<div class="outline-text-3" id="text-sql-op-as">
<p>
Also known in some explanations as "alias". This assigns a name to a column or
table in a :select form. When fields are given, they are added after the name,
in parentheses. For example, (:as 'table1 't1 'foo 'bar)
becomes table1 AS t1(foo, bar). When you need to specify types for the fields,
you can do something like (:as 'table2 't2 ('foo integer)). Note that names are
quoted, types are not (when using sql-compile or sql-template, you can leave
out the quotes entirely).
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'countries.name 'country)
                (<span style="color: #23d7d7;">:as</span> 'regions.name 'region)
                <span style="color: #23d7d7;">:from</span> 'countries 'regions
                       <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'regions.id 'region-id)
                                     (<span style="color: #23d7d7;">:=</span> 'regions.name <span style="color: #e67128;">"Central America"</span>)))
        <span style="color: #23d7d7;">:alists</span>)

(((<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>)
(<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>)
(<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>)
(<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>))
((<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>)))
</pre>
</div>
<p>
The following uses aliases for both columns and tables in the from and inner-join clauses:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
        (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'recs.firstname 'firstname)
                 (<span style="color: #23d7d7;">:as</span> 'recs.surname 'surname)
                 <span style="color: #23d7d7;">:distinct</span>
                 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'mems)
                 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'recs)
                 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'recs.memid 'mems.recommendedby))
        'surname 'firstname))
</pre>
</div>

<p>
Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:
</p>
<div class="org-src-container">
<pre class="src src-lisp"><span style="color: #e67128;">"select sum(slots as "</span>Total Slots<span style="color: #e67128;">" from cd.bookings"</span>
</pre>
</div>

<p>
without using :raw
</p>
</div>
</div>

<div id="outline-container-sql-op-cast" class="outline-3">
<h3 id="sql-op-cast">sql-op :cast (query)</h3>
<div class="outline-text-3" id="text-sql-op-cast">
<p>
The CAST operator. Takes a query as an argument, and returns the result
explicitly cast by postgresql to a specific type. Unlike :type, :cast can
pass the type as a variable.
</p>
<div class="org-src-container">
<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)))
        <span style="color: #23d7d7;">:single</span>)
20

(<span style="color: #ffad29; font-weight: bold;">let</span> ((type 'text))
   (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)))
     <span style="color: #23d7d7;">:single</span>))
<span style="color: #e67128;">"20"</span>

(<span style="color: #ffad29; font-weight: bold;">let</span> ((type 'integer))
   (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)))
     <span style="color: #23d7d7;">:single</span>))
20

(query (<span style="color: #23d7d7;">:union</span> (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 1 'real))
               (<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)))))
((1.0) (2.2))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-type-query" class="outline-3">
<h3 id="sql-op-type-query">sql-op :type (query)</h3>
<div class="outline-text-3" id="text-sql-op-type-query">
<p>
Is similar to cast but uses the postgresql :: formating. Unlike cast it will not
accept a variable as the type.
</p>

<p>
E.g.
</p>
<div class="org-src-container">
<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)))

<span style="color: #e67128;">"(SELECT (now()::DATE - x) AS some_date FROM generate_series(1, 10) AS x)"</span>
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-type-form" class="outline-3">
<h3 id="sql-op-type-form">sql-op :type (form type)</h3>
<div class="outline-text-3" id="text-sql-op-type-form">
<p>
Add a type declaration to a value, as in in "4.3::real". The second
argument is not evaluated normally, but put through sql-type-name to
get a type identifier.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:type</span> 1.0 int)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-create-composite-type" class="outline-3">
<h3 id="sql-op-create-composite-type">sql-op :create-composite-type (type-name &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-create-composite-type">
<p>
Creates a composite type with a type-name and two or more columns. E.g.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-composite-type</span> 'fullname (first-name text) (last-name text)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-exists" class="outline-3">
<h3 id="sql-op-exists">sql-op :exists (query)</h3>
<div class="outline-text-3" id="text-sql-op-exists">
<p>
The EXISTS operator. Takes a query as an argument, and returns true or false
depending on whether that query returns any rows. In the example below, it is
applied to a subquery.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'id 'name
                <span style="color: #23d7d7;">:from</span> 'regions
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:exists</span>
                        (<span style="color: #23d7d7;">:select</span> 'region-id
                         <span style="color: #23d7d7;">:from</span> 'countries
                         <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                                  (<span style="color: #23d7d7;">:=</span> 'countries.name <span style="color: #e67128;">"Costa Rica"</span>)
                                  (<span style="color: #23d7d7;">:=</span> 'regions.id 'countries.region-id))))))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-is-false" class="outline-3">
<h3 id="sql-op-is-false">sql-op :is-false (arg)</h3>
<div class="outline-text-3" id="text-sql-op-is-false">
<p>
Test whether a binary value is false.
</p>
<div class="org-src-container">
<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)))

(select-dao 'account (:is-false 'active))</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-is-true" class="outline-3">
<h3 id="sql-op-is-true">sql-op :is-true (arg)</h3>
<div class="outline-text-3" id="text-sql-op-is-true">
<p>
Test whether a binary value is true.
</p>
<div class="org-src-container">
<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)))

(select-dao 'account (:is-true 'active))</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-is-null" class="outline-3">
<h3 id="sql-op-is-null">sql-op :is-null (arg)</h3>
<div class="outline-text-3" id="text-sql-op-is-null">
<p>
Test whether a value is null.
</p>
<div class="org-src-container">
<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))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-not-null" class="outline-3">
<h3 id="sql-op-not-null">sql-op :not-null (arg)</h3>
<div class="outline-text-3" id="text-sql-op-not-null">
<p>
Test whether a value is not null.
</p>
<div class="org-src-container">
<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)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-in" class="outline-3">
<h3 id="sql-op-in">sql-op :in (value set)</h3>
<div class="outline-text-3" id="text-sql-op-in">
<p>
Test whether a value is in a set of values.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
                            (<span style="color: #23d7d7;">:set</span> 20 21 23))))

(query (<span style="color: #23d7d7;">:select</span> 'region 'product (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'quantity) 'product-units)
                (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'amount) 'product-sales)
                <span style="color: #23d7d7;">:from</span> 'orders
                <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))
                <span style="color: #23d7d7;">:group-by</span> 'region 'product))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-not-in" class="outline-3">
<h3 id="sql-op-not-in">sql-op :not-in (value set)</h3>
<div class="outline-text-3" id="text-sql-op-not-in">
<p>
Inverse of the above.
</p>
</div>
</div>

<div id="outline-container-sql-op-set" class="outline-3">
<h3 id="sql-op-set">sql-op :set (&amp;rest elements)</h3>
<div class="outline-text-3" id="text-sql-op-set">
<p>
Denote a set of values. This operator has two interfaces. When
the elements are known at compile-time, they can be given as
multiple arguments to the operator. When they are not, a
single argument that evaluates to a list should be used.
</p>

<p>
The following would be the syntax in postmodern sql where the set is a list. If
you want to use a vector, then you need to use Any:
</p>

<p>
The following are equivalent
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
                            (<span style="color: #23d7d7;">:set</span> 20 21 23))))

(<span style="color: #ffad29; font-weight: bold;">let</span> ((x (list 20 21 23)))
  (query (<span style="color: #23d7d7;">:select</span> 'name
                  <span style="color: #23d7d7;">:from</span> 'countries
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
                              (<span style="color: #23d7d7;">:set</span> x)))))

(query (<span style="color: #23d7d7;">:select</span> 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id (<span style="color: #23d7d7;">:set</span> (list 20 21 23)))))

</pre>
</div>
<p>
However, the following will generate an error about inability to convert to an sql literal
</p>

<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:in</span> 'id
                            (<span style="color: #23d7d7;">:set</span> '(20 21 23)))))
</pre>
</div>
<p>
Now with selecting a dao
</p>
<div class="org-src-container">
<pre class="src src-lisp">(select-dao 'countries
            (<span style="color: #23d7d7;">:in</span> 'id
                 (<span style="color: #23d7d7;">:set</span> (list 20 21 23
</pre>
</div>
<p>
Now with selecting from a vector. Note both the use of any* and := instead of :in.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((x (vector 20 21 23)))
  (query (<span style="color: #23d7d7;">:select</span> 'name
                  <span style="color: #23d7d7;">:from</span> 'countries
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id (<span style="color: #23d7d7;">:any*</span> x)))))
</pre>
</div>
<p>
Note that the responses will still come back in a list of lists
</p>

<p>
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."
</p>
</div>
</div>

<div id="outline-container-sql-op-array" class="outline-3">
<h3 id="sql-op-array">sql-op :array (query)</h3>
<div class="outline-text-3" id="text-sql-op-array">
<p>
This is used when calling a select query into an array.  See <a href="array-notes.html">array-notes.html</a>
for more detailed notes on the use of arrays.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
        (<span style="color: #23d7d7;">:select</span> 'r.rolename
                 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:array</span>
                       (<span style="color: #23d7d7;">:select</span> 'b.rolename
                                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg_catalog.pg-auth-members 'm)
                                <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'pg-catalog.pg-roles 'b)
                                <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'm.roleid 'b.oid)
                                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'm.member 'r.oid )))
                      'memberof)

                 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg-catalog.pg-roles 'r))
        1))

</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-array-rest" class="outline-3">
<h3 id="sql-op-array-rest">sql-op :array[] (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-array-rest">
<p>
This is the general operator for arrays. It also handles statements that include
functions in the query such as (:+ 1 2), (:pi) in the array. See <a href="array-notes.html">array-notes.html</a>
for more detailed notes on the use of arrays.
</p>
<div class="org-src-container">
<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))))

((#(1 2 3)))

(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:array-prepend</span> 1 (<span style="color: #23d7d7;">:array[]</span> 2 3)))
       <span style="color: #23d7d7;">:single</span>)

#(1 2 3)
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-square-brackets" class="outline-3">
<h3 id="sql-op-square-brackets">sql-op :[] (form start &amp;optional end)</h3>
<div class="outline-text-3" id="text-sql-op-square-brackets">
<p>
Dereference an array value. If end is provided, extract a slice of the array.
Sample usage below, but also see <a href="array-notes.html">array-notes.html</a> for more detailed notes on
the use of arrays.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'receipe-id (<span style="color: #23d7d7;">:[]</span> 'tags 2 3)
                <span style="color: #23d7d7;">:from</span> 'receipe-tags-array
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'receipe-id 3)))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-extract" class="outline-3">
<h3 id="sql-op-extract">sql-op :extract (unit form)</h3>
<div class="outline-text-3" id="text-sql-op-extract">
<p>
Extract a field from a date/time value. For example, (:extract :month (:now)).
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
        (<span style="color: #23d7d7;">:select</span> 'facid
                 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:extract</span> 'month 'starttime) 'month)
                 (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'slots) 'total-slots)
                 <span style="color: #23d7d7;">:from</span> 'cd.bookings
                 <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>)
                              (<span style="color: #23d7d7;">:&lt;</span> 'starttime <span style="color: #e67128;">"2013-01-01"</span>))
                 <span style="color: #23d7d7;">:group-by</span> 'facid 'month)
        'facid 'month))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-case" class="outline-3">
<h3 id="sql-op-case">sql-op :case (&amp;rest clauses)</h3>
<div class="outline-text-3" id="text-sql-op-case">
<p>
A conditional expression. Clauses should take the form (test value). If
test is :else, an ELSE clause will be generated.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
                (<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>)
                            (<span style="color: #23d7d7;">:else</span> <span style="color: #e67128;">"cheap"</span>)) <span style="color: #ff4242; font-weight: bold;">'cost)</span>
                <span style="color: #23d7d7;">:from</span> 'cd.facilities))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-between" class="outline-3">
<h3 id="sql-op-between">sql-op :between (n start end)</h3>
<div class="outline-text-3" id="text-sql-op-between">
<p>
Test whether a value lies between two other values.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'name
                <span style="color: #23d7d7;">:from</span> 'countries
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:between</span> 'latitude -10 10))
       <span style="color: #23d7d7;">:column</span>)
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-between-symmetric" class="outline-3">
<h3 id="sql-op-between-symmetric">sql-op :between-symmetric (n start end)</h3>
<div class="outline-text-3" id="text-sql-op-between-symmetric">
<p>
Works like :between, except that the start value is not required to be
less than the end value.
</p>
</div>
</div>

<div id="outline-container-sql-op-dot" class="outline-3">
<h3 id="sql-op-dot">sql-op :dot (&amp;rest names)</h3>
<div class="outline-text-3" id="text-sql-op-dot">
<p>
Can be used to combine multiple names into a name of the form A.B to
refer to a column in a table, or a table in a schema. Note that you
can also just use a symbol with a dot in it.
</p>
</div>
</div>

<div id="outline-container-sql-op-raw-string" class="outline-3">
<h3 id="sql-op-raw-string">sql-op :raw (string)</h3>
<div class="outline-text-3" id="text-sql-op-raw-string">
<p>
Insert a string as-is into the query. This can be useful for doing things
that the syntax does not support, or to re-use parts of a query across
multiple queries:
</p>
<div class="org-src-container">
<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))))
       (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)))))
  (query (<span style="color: #23d7d7;">:delete-from</span> 'baz <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:raw</span> test)))
  (<span style="color: #ffad29; font-weight: bold;">do-stuff</span> rows))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-fetch" class="outline-3">
<h3 id="sql-op-fetch">sql-op :fetch (form amount &amp;optional offset)</h3>
<div class="outline-text-3" id="text-sql-op-fetch">
<p>
Fetch is a more efficient way to do pagination instead of using limit and
offset. Fetch allows you to retrieve a limited set of rows, optionally offset
by a specified number of rows. In order to ensure this works correctly, you
should use the order-by clause. If the amount is not provided, it assumes
you only want to return 1 row.
<a href="https://www.postgresql.org/docs/current/sql-select.html">https://www.postgresql.org/docs/current/sql-select.html</a>
</p>

<p>
Examples:
</p>
<div class="org-src-container">
<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))

((1) (2) (3) (4) (5))

(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))

((11) (12) (13) (14) (15))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-limit" class="outline-3">
<h3 id="sql-op-limit">sql-op :limit (query amount &amp;optional offset)</h3>
<div class="outline-text-3" id="text-sql-op-limit">
<p>
In S-SQL limit is not part of the select operator, but an extra
operator that is applied to a query (this works out better when limiting
the union or intersection of multiple queries, same for sorting).
It limits the number of results to the amount given as the second
argument, and optionally offsets the result by the amount given
as the third argument.
</p>
<div class="org-src-container">
<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))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-order-by" class="outline-3">
<h3 id="sql-op-order-by">sql-op :order-by (query &amp;rest exprs)</h3>
<div class="outline-text-3" id="text-sql-op-order-by">
<p>
Order the results of a query by the given expressions. See :desc for
when you want to invert an ordering. Note: This is not the same as
passing an :order-by parameter to an aggregation operator.
For that see Aggregation Operators.
</p>
<div class="org-src-container">
<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>))
                                          <span style="color: #23d7d7;">:from</span> 'employee
                                          <span style="color: #23d7d7;">:group-by</span> 'name 'id 'salary 'city)
                                 'name))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-values" class="outline-3">
<h3 id="sql-op-values">sql-op :values</h3>
<div class="outline-text-3" id="text-sql-op-values">
<p>
Values computes a row value or set of row values for use in a specific
query. See the postgresql docs at:
<a href="https://www.postgresql.org/docs/current/static/queries-values.html">https://www.postgresql.org/docs/current/static/queries-values.html</a>
and <a href="https://www.postgresql.org/docs/current/static/sql-values.html">https://www.postgresql.org/docs/current/static/sql-values.html</a>
Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '*
                <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>)
                                    (<span style="color: #23d7d7;">:set</span> 2 <span style="color: #e67128;">"two"</span>)
                                    (<span style="color: #23d7d7;">:set</span> 3 <span style="color: #e67128;">"three"</span>))
                           (<span style="color: #23d7d7;">:t1</span> 'num 'letter))))

(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))
                <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))
                (<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))
                (<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))))

(query
 (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
  (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:t1</span> 'n)
       (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> 1))
                   (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'n 1)
                            <span style="color: #23d7d7;">:from</span> 't1
                            <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'n 100))))
  (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:sum</span> 'n) <span style="color: #23d7d7;">:from</span> 't1))
 <span style="color: #23d7d7;">:single</span>)
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-empty-set" class="outline-3">
<h3 id="sql-op-empty-set">sql-op :empty-set</h3>
<div class="outline-text-3" id="text-sql-op-empty-set">
<p>
This is a fudge. It returns a string "()" where something like '()
would return "false" or :() would throw an error. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'appnumber 'day (<span style="color: #23d7d7;">:sum</span> 'inserts)
                (<span style="color: #23d7d7;">:sum</span> 'updates) (<span style="color: #23d7d7;">:sum</span> 'deletes) (<span style="color: #23d7d7;">:sum</span> 'transactions)
                <span style="color: #23d7d7;">:from</span> 'db-details
                <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>)))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-group-by" class="outline-3">
<h3 id="sql-op-group-by">sql-op :group-by</h3>
<div class="outline-text-3" id="text-sql-op-group-by">
<p>
<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>
The GROUP BY Clause is used to group together those rows in a table that
have the same values in all the columns listed. The order in which the
columns are listed does not matter. The effect is to combine each set of
rows having common values into one group row that represents all rows in
the group. This is done to eliminate redundancy in the output and/or compute
aggregates that apply to these groups. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:order-by</span>
        (<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)
                 <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'cd.bookings 'bks)
                 <span style="color: #23d7d7;">:inner-join</span> (<span style="color: #23d7d7;">:as</span> 'cd.members 'mems)
                 <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'mems.memid 'bks.memid)
                 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&gt;=</span> 'starttime <span style="color: #e67128;">"2012-09-01"</span>)
                 <span style="color: #23d7d7;">:group-by</span> 'mems.surname 'mems.firstname 'mems.memid)
        'mems.memid))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-grouping-sets" class="outline-3">
<h3 id="sql-op-grouping-sets">sql-op :grouping-sets</h3>
<div class="outline-text-3" id="text-sql-op-grouping-sets">
<p>
<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>
More complex grouping operations are possible using the concept of grouping
sets. The data selected by the FROM and WHERE clauses is grouped separately
by each specified grouping set, aggregates computed for each group just as
for simple GROUP BY clauses, and then the results returned.
This operator requires postgresql 9.5 or later. For example:
</p>
<div class="org-src-container">
<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)
                <span style="color: #23d7d7;">:from</span> 'employee
                <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)))))
</pre>
</div>
</div>
</div>
</div>

<div id="outline-container-sql-op-time-date-and-interval" class="outline-2">
<h2 id="sql-op-time-date-and-interval">Time, Date and Interval Operators</h2>
<div class="outline-text-2" id="text-sql-op-time-date-and-interval">
</div>
<div id="outline-container-sql-op-interval" class="outline-3">
<h3 id="sql-op-interval">sql-op :interval (arg)</h3>
<div class="outline-text-3" id="text-sql-op-interval">
<p>
Creates an interval data type, generally represented in postmodern as an alist
</p>
</div>
</div>
<div id="outline-container-sql-op-current-date" class="outline-3">
<h3 id="sql-op-current-date">sql-op :current-date ()</h3>
<div class="outline-text-3" id="text-sql-op-current-date">
<div class="org-src-container">
<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>)
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-current-time" class="outline-3">
<h3 id="sql-op-current-time">sql-op :current-time ()</h3>
<div class="outline-text-3" id="text-sql-op-current-time">
</div>
</div>
<div id="outline-container-sql-op-current-timestamp" class="outline-3">
<h3 id="sql-op-current-timestamp">sql-op :current-timestamp ()</h3>
<div class="outline-text-3" id="text-sql-op-current-timestamp">
</div>
</div>
<div id="outline-container-sql-op-timestamp" class="outline-3">
<h3 id="sql-op-timestamp">sql-op :timestamp (arg)</h3>
<div class="outline-text-3" id="text-sql-op-timestamp">
</div>
</div>
<div id="outline-container-sql-op-age" class="outline-3">
<h3 id="sql-op-age">sql-op :age (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-age">
</div>
</div>
<div id="outline-container-sql-op-date" class="outline-3">
<h3 id="sql-op-date">sql-op :date (arg)</h3>
<div class="outline-text-3" id="text-sql-op-date">
</div>
</div>
<div id="outline-container-sql-op-make-insterval" class="outline-3">
<h3 id="sql-op-make-insterval">sql-op :make-interval (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-make-insterval">
<p>
Takes lists of (time-unit value) and returns a timestamp type. Example:
</p>

<div class="org-src-container">
<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)))
       <span style="color: #23d7d7;">:single</span>)
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-make-timestamp" class="outline-3">
<h3 id="sql-op-make-timestamp">sql-op :make-timestamp (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-make-timestamp">
<p>
Takes lists of (time-unit value) and returns a timestamptz type. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span>
          (<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)
                             (<span style="color: #e67128;">"hour"</span> 21) (<span style="color: #e67128;">"min"</span> 50) (<span style="color: #e67128;">"sec"</span> 0)))
       <span style="color: #23d7d7;">:single</span>)
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-make-timestamptz" class="outline-3">
<h3 id="sql-op-make-timestamptz">sql-op :make-timestamptz (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-make-timestamptz">
<p>
Takes lists of (time-unit value) and returns a timestamptz type. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span>
          (<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)
                             (<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>)))
       <span style="color: #23d7d7;">:single</span>)
</pre>
</div>
</div>
</div>
</div>

<div id="outline-container-sql-op-aggregation-operators" class="outline-2">
<h2 id="sql-op-aggregation-operators">Aggregation Operators</h2>
<div class="outline-text-2" id="text-sql-op-aggregation-operators">
</div>
<div id="outline-container-sql-op-count" class="outline-3">
<h3 id="sql-op-count">sql-op :count (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-count">
<p>
Count returns the number of rows for which the expression is not null.
It can be the number of rows collected by the select statement as in:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> '*)
                <span style="color: #23d7d7;">:from</span> 'table1
                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'price 100)))
</pre>
</div>

<p>
or it can be a smaller number of rows based on the allowed keyword
parameters :distinct and :filter or some other type of condition as in:
</p>
<div class="org-src-container">
<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>)
                <span style="color: #23d7d7;">:from</span> 'cd.bookings))
</pre>
</div>
<p>
or
</p>

<div class="org-src-container">
<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)
                (<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))
                     'filtered)
                <span style="color: #23d7d7;">:from</span> 'testtable))
</pre>
</div>

<p>
Note that if used, the filter must be last in the count args. If distinct
is used, it must come before filter. Unlike standard sql, the word 'where'
is not used inside the filter clause. E.g.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:count</span> '*)
                (<span style="color: #23d7d7;">:count</span> '* <span style="color: #23d7d7;">:filter</span> (<span style="color: #23d7d7;">:=</span> 1 'bid))
                'id
                <span style="color: #23d7d7;">:from</span> 'pbbench-history))
</pre>
</div>

<p>
See tests.lisp for examples.
</p>
</div>
</div>

<div id="outline-container-sql-op-avg" class="outline-3">
<h3 id="sql-op-avg">sql-op :avg (&amp;rest rest args)</h3>
<div class="outline-text-3" id="text-sql-op-avg">
<p>
Avg calculates the average value of a list of values. Note that if the
filter keyword is used, the filter must be last in the avg args. If distinct
is used, it must come before filter. E.g. See tests.lisp for more examples.
</p>
<div class="org-src-container">
<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
                <span style="color: #23d7d7;">:from</span> 'pbbench-history))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-sum" class="outline-3">
<h3 id="sql-op-sum">sql-op :sum (&amp;rest rest args)</h3>
<div class="outline-text-3" id="text-sql-op-sum">
<p>
Sum calculates the total of a list of values. Note that if the keyword filter
is used, the filter must be last in the sum args. If distinct is used, it
must come before filter. Unlike standard sql, the word 'where' is not used
inside the filter clause (s-sql will properly expand it). See tests.lisp
for more examples.
</p>
<div class="org-src-container">
<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
                <span style="color: #23d7d7;">:from</span> 'pbbench-history))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-max" class="outline-3">
<h3 id="sql-op-max">sql-op ::max (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-max">
<p>
max returns the maximum value of a set of values. Note that if the filter
keyword is used, the filter must be last in the max args. If distinct is
used, it must come before filter. Unlike standard sql, the word 'where'
is not used inside the filter clause (s-sql will properly expand it).
See tests.lisp for more examples.
</p>

<div class="org-src-container">
<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
                <span style="color: #23d7d7;">:from</span> 'pbbench-history))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-min" class="outline-3">
<h3 id="sql-op-min">sql-op ::min (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-min">
<p>
min returns the minimum value of a set of values. Note that if the filter
keyword is used, the filter must be last in the min args. If distinct is
used, it must come before filter. Unlike standard sql, the word 'where'
is not used inside the filter clause (s-sql will properly expand it).
See tests.lisp for more examples.
</p>
<div class="org-src-container">
<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
                <span style="color: #23d7d7;">:from</span> 'pbbench-history))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-every" class="outline-3">
<h3 id="sql-op-every">sql-op ::every (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-every">
<p>
Every returns true if all input values are true, otherwise false. Note
that if the filter keyword is used, the filter must be last in the every
args. If distinct is used, it must come before filter. Unlike standard sql,
the word 'where' is not used inside the filter clause (s-sql will
properly expand it). See tests.lisp for more examples.
</p>
<div class="org-src-container">
<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>))
                <span style="color: #23d7d7;">:from</span> 'tbl-students
                <span style="color: #23d7d7;">:group-by</span> 'studname 'studid 'studgrades))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-percentile-cont" class="outline-3">
<h3 id="sql-op-percentile-cont">sql-op :percentile-cont (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-percentile-cont">
<p>
Requires Postgresql 9.4 or higher. Percentile-cont returns a value
corresponding to the specified fraction in the ordering, interpolating
between adjacent input items if needed. There are two required keyword
parameters :fraction and :order-by. If the fraction value is an array,
then it returns an array of results matching the shape of the fractions
parameter, with each non-null element replaced by the value corresponding
to that percentile. Examples:
</p>
<div class="org-src-container">
<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)
                <span style="color: #23d7d7;">:from</span> 'schools))

(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]
                                  <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
                <span style="color: #23d7d7;">:from</span>  'schools))
</pre>
</div>
</div>
</div>


<div id="outline-container-sql-op-percentile-dist" class="outline-3">
<h3 id="sql-op-percentile-dist">sql-op :percentile-dist (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-percentile-dist">
<p>
Requires Postgresql 9.4 or higher. There are two required keyword parameters
:fraction and :order-by. Percentile-dist returns the first input value whose
position in the ordering equals or exceeds the specified fraction. If the
fraction parameter is an array eturns an array of results matching the shape
of the fractions parameter, with each non-null element replaced by the input
value corresponding to that percentile. Examples:
</p>

<div class="org-src-container">
<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
                                  <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
                <span style="color: #23d7d7;">:from</span> 'schools))

(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]
                                  <span style="color: #23d7d7;">:order-by</span> 'number-of-staff)
                <span style="color: #23d7d7;">:from</span>  'schools))

</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-corr" class="outline-3">
<h3 id="sql-op-corr">sql-op :corr (y x)</h3>
<div class="outline-text-3" id="text-sql-op-corr">
<p>
The corr function returns the correlation coefficient between a set of
dependent and independent variables. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:corr</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-covar-pop" class="outline-3">
<h3 id="sql-op-covar-pop">sql-op :covar-pop (y x)</h3>
<div class="outline-text-3" id="text-sql-op-covar-pop">
<p>
The covar-pop function returns the population covariance between a set of
dependent and independent variables. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:covar-pop</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-covar-samp" class="outline-3">
<h3 id="sql-op-covar-samp">sql-op :covar-samp (y x)</h3>
<div class="outline-text-3" id="text-sql-op-covar-samp">
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:covar-samp</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
<p>
The covar-samp function returns the sample covariance between a set of
dependent and independent variables. Example:
</p>
</div>
</div>

<div id="outline-container-sql-op-string-agg" class="outline-3">
<h3 id="sql-op-string-agg">sql-op :string-agg (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-string-agg">
<p>
String-agg allows you to concatenate strings using different types of
delimiter symbols. Allowable optional keyword parameters are :distinct,
:order-by and :filter Note that order-by in string-agg requires
postgresql 9.0 or later. Filter requires postgresql 9.4 or later.
See tests.lisp for more examples.
</p>
<div class="org-src-container">
<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 \",\" )
                     'step-summary)
                <span style="color: #23d7d7;">:from</span> 'business-process))

(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))
                          'words)
                <span style="color: #23d7d7;">:from</span> 'moves))

(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))
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-array-agg" class="outline-3">
<h3 id="sql-op-array-agg">sql-op :array-agg (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-array-agg">
<p>
Array-agg returns a list of values concatenated into an arrays.
Allowable optional keyword parameters are :distinct, :order-by
and :filter.
</p>

<p>
Note that order-by in array-agg requires postgresql 9.0 or later.
Filter requires postgresql 9.4 or later. See <a href="array-notes.html">array-notes.html</a> for more
detailed notes on the use of arrays.
</p>

<p>
Example with Filter:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'g.id
                (<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\"))
                     'canonical-users)
                (<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\"))
                     'non-canonical-users)
                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'groups 'g)
                <span style="color: #23d7d7;">:group-by</span> 'g.id))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-mode" class="outline-3">
<h3 id="sql-op-mode">sql-op :mode (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-mode">
<p>
Mode is used to find the most frequent input value in a group.
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>
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>
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:mode</span> 'items)
                <span style="color: #23d7d7;">:from</span> 'item-table))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-avgx" class="outline-3">
<h3 id="sql-op-regr-avgx">sql-op :regr_avgx (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-avgx">
<p>
The regr_avgx function returns the average of the independent variable
(sum(X)/N) Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_avgx</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-avgy" class="outline-3">
<h3 id="sql-op-regr-avgy">sql-op :regr_avgy (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-avgy">
<p>
The regr_avgy function returns the average of the dependent variable
(sum(Y)/N). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">
</pre>
</div>
<p>
(query (:select (:regr_avgy 'height 'weight)
                :from 'people))
</p>
</div>
</div>
<div id="outline-container-sql-op-regr-count" class="outline-3">
<h3 id="sql-op-regr-count">sql-op :regr_count (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-count">
<p>
The regr_count function returns the number of input rows in which both
expressions are nonnull. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_count</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-intercept" class="outline-3">
<h3 id="sql-op-regr-intercept">sql-op :regr_intercept (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-intercept">
<p>
The regr_intercept function returns the y-intercept of the least-squares-fit
linear equation determined by the (X, Y) pairs. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_intercept</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-r2" class="outline-3">
<h3 id="sql-op-regr-r2">sql-op :regr_r2 (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-r2">
<p>
The regr_r2 function returns the square of the correlation coefficient. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_r2</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-slope" class="outline-3">
<h3 id="sql-op-regr-slope">sql-op :regr_slope (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-slope">
<p>
The regr_slope function returns the slope of the least-squares-fit linear
equation determined by the (X, Y) pairs. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_slope</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-sxx" class="outline-3">
<h3 id="sql-op-regr-sxx">sql-op :regr_sxx (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-sxx">
<p>
The regr_sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares” of
the independent variable). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_sxx</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-sxy" class="outline-3">
<h3 id="sql-op-regr-sxy">sql-op :regr_sxy (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-sxy">
<p>
The regr_sxy function returns the sum(X*Y) - sum(X) * sum(Y)/N (“sum of products”
of independent times dependent variable). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_sxy</span> 'height 'weight)
                <span style="color: #23d7d7;">:from</span> 'people))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-regr-syy" class="outline-3">
<h3 id="sql-op-regr-syy">sql-op :regr_syy (y x)</h3>
<div class="outline-text-3" id="text-sql-op-regr-syy">
<p>
The regr_syy function returns the sum(Y^2) - sum(Y)^2/N (“sum of squares”
of the dependent variable). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:regr_syy</span> 'salary 'age)
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-stddev" class="outline-3">
<h3 id="sql-op-stddev">sql-op :stddev (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-stddev">
<p>
The stddev function returns the the sample standard deviation of the input
values. It is a historical alias for stddev-samp. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-stddev-pop" class="outline-3">
<h3 id="sql-op-stddev-pop">sql-op :stddev-pop (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-stddev-pop">
<p>
The stddev-pop function returns the population standard deviation of the
input values. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev-pop</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-stddev-samp" class="outline-3">
<h3 id="sql-op-stddev-samp">sql-op :stddev-samp (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-stddev-samp">
<p>
The stddev-samp function returns the sample standard deviation of the
input values. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:stddev-samp</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-variance" class="outline-3">
<h3 id="sql-op-variance">sql-op :variance (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-variance">
<p>
Variance is a historical alias for var_samp. The variance function returns
the sample variance of the input values (square of the sample standard deviation).
Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:variance</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-var-pop" class="outline-3">
<h3 id="sql-op-var-pop">sql-op :var-pop (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-var-pop">
<p>
The var-pop function returns the population variance of the input values
(square of the population standard deviation). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:var-pop</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee)
       <span style="color: #23d7d7;">:single</span>)
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-var-samp" class="outline-3">
<h3 id="sql-op-var-samp">sql-op :var-samp (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-var-samp">
<p>
The var-samp function returns the sample variance of the input values
(square of the sample standard deviation). Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:var-samp</span> 'salary)
                <span style="color: #23d7d7;">:from</span> 'employee)
       <span style="color: #23d7d7;">:single</span>)
</pre>
</div>

<p>
Window Functions
</p>
</div>
</div>
<div id="outline-container-org2555f16" class="outline-3">
<h3 id="org2555f16">sql-op :range-between (&amp;rest args)</h3>
<div class="outline-text-3" id="text-org2555f16">
<p>
Range-between allows window functions to apply to different segments of a result set.
It accepts the following keywords: :order-by, :rows-between, :range-between,
:unbounded-preceding, :current-row and :unbounded-following. Use of :preceding or
:following will generate errors.
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.
</p>

<p>
An example which calculates a running total could look like this:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'country 'country-name)
          (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
          (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
                      (<span style="color: #23d7d7;">:range-between</span> <span style="color: #23d7d7;">:order-by</span> 'country
                                      <span style="color: #23d7d7;">:unbounded-preceding</span> <span style="color: #23d7d7;">:current-row</span>))
               'global-population)
  <span style="color: #23d7d7;">:from</span> 'population
  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'iso2)
               (<span style="color: #23d7d7;">:=</span> 'year 1976))))
</pre>
</div>
</div>
</div>

<div id="outline-container-orgfb601b4" class="outline-3">
<h3 id="orgfb601b4">sql-op :rows-between (&amp;rest args)</h3>
<div class="outline-text-3" id="text-orgfb601b4">
<p>
Rows-between allows window functions to apply to different segments of a result set.
It accepts the following keywords:
:order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
: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.
</p>

<p>
An example could look like this :
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'country 'country-name)
          (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
          (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
                      (<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))
               'global-population)
  <span style="color: #23d7d7;">:from</span> 'population
  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'iso2)
               (<span style="color: #23d7d7;">:=</span> 'year 1976))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-over" class="outline-3">
<h3 id="sql-op-over">sql-op :over (form &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-over">
<p>
Over, partition-by and window are so-called window functions. A window
function performs a calculation across a set of table rows that are
somehow related to the current row and adds that as an additional column to
the result. The following collects individual salaries and the total salaries.
</p>
<div class="org-src-container">
<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))
                <span style="color: #23d7d7;">:from</span> 'empsalary))
</pre>
</div>
<p>
A more complicated version that calculates a running total might look like:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
 (<span style="color: #23d7d7;">:select</span> 'name
          (<span style="color: #23d7d7;">:as</span> 'salary 'individual-salary)
          (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'salary)
                      (<span style="color: #23d7d7;">:range-between</span> <span style="color: #23d7d7;">:order-by</span> 'name <span style="color: #23d7d7;">:unbounded-preceding</span>
                       <span style="color: #23d7d7;">:current-row</span>))
               'running-total-salary)
  <span style="color: #23d7d7;">:from</span> 'empsalary))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-partition-by" class="outline-3">
<h3 id="sql-op-partition-by">sql-op :partition-by (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-partition-by">
<p>
Args is a list of one or more columns to partition by, optionally
followed by other keywords. Partition-by accepts the following keywords:
:order-by, :rows-between, :range-between, :preceding, :unbounded-preceding,
: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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'depname 'subdepname 'empno 'salary
                (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:avg</span> 'salary)
                       (<span style="color: #23d7d7;">:partition-by</span> 'depname 'subdepname))
                <span style="color: #23d7d7;">:from</span> 'empsalary))
</pre>
</div>

<p>
Note the use of :order-by without parens:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> 'depname 'empno 'salary
                (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:rank</span>)
                       (<span style="color: #23d7d7;">:partition-by</span> 'depname <span style="color: #23d7d7;">:order-by</span> (<span style="color: #23d7d7;">:desc</span> 'salary)))
                <span style="color: #23d7d7;">:from</span> 'empsalary))
</pre>
</div>

<p>
The following example shows a query for country population in 1976 with running total population by region.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
 (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'population.country 'country-name)
          (<span style="color: #23d7d7;">:as</span> 'population 'country-population)
          'region-name
          (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:sum</span> 'population)
                      (<span style="color: #23d7d7;">:partition-by</span> 'region-name <span style="color: #23d7d7;">:order-by</span> 'region-name
                       <span style="color: #23d7d7;">:rows-between</span> <span style="color: #23d7d7;">:unbounded-preceding</span> <span style="color: #23d7d7;">:current-row</span>))
               'regional-population)
          <span style="color: #23d7d7;">:from</span> 'population
          <span style="color: #23d7d7;">:inner-join</span> 'regions
          <span style="color: #23d7d7;">:on</span> (<span style="color: #23d7d7;">:=</span> 'population.iso3 'regions.iso3)
          <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:not-null</span> 'population.iso2)
                       (<span style="color: #23d7d7;">:=</span> 'year 1976))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-window" class="outline-3">
<h3 id="sql-op-window">sql-op :window (form)</h3>
<div class="outline-text-3" id="text-sql-op-window">
<div class="org-src-container">
<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)
                (<span style="color: #23d7d7;">:over</span> (<span style="color: #23d7d7;">:avg</span> 'salary) 'w)
                <span style="color: #23d7d7;">:from</span> 'empsalary <span style="color: #23d7d7;">:window</span>
                (<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)))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-with" class="outline-3">
<h3 id="sql-op-with">sql-op :with (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-with">
<p>
With provides a way to write auxillary statements for use in a larger query,
often referred to as Common Table Expressions or CTEs.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:with</span> (<span style="color: #23d7d7;">:as</span> 'upd
                   (<span style="color: #23d7d7;">:parens</span>
                    (<span style="color: #23d7d7;">:update</span> 'employees <span style="color: #23d7d7;">:set</span> 'sales-count (<span style="color: #23d7d7;">:+</span> 'sales-count 1)
                             <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'id
                                        (<span style="color: #23d7d7;">:select</span> 'sales-person
                                                 <span style="color: #23d7d7;">:from</span> 'accounts
                                                 <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'name <span style="color: #e67128;">"Acme Corporation"</span>)))
                             <span style="color: #23d7d7;">:returning</span> '*)))
              (<span style="color: #23d7d7;">:insert-into</span> 'employees-log
                            (<span style="color: #23d7d7;">:select</span> '* (<span style="color: #23d7d7;">:current-timestamp</span>) <span style="color: #23d7d7;">:from</span>
                                     'upd))))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-with-recursive" class="outline-3">
<h3 id="sql-op-with-recursive">sql-op :with-recursive (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-with-recursive">
<p>
Recursive modifier to a WITH statement, allowing the query to refer to its own output.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
      (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:t1</span> 'n)
           (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:values</span> (<span style="color: #23d7d7;">:set</span> 1))
                       (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:+</span> 'n 1)
                                <span style="color: #23d7d7;">:from</span> 't1
                                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:&lt;</span> 'n 100))))
      (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:sum</span> 'n) <span style="color: #23d7d7;">:from</span> 't1)))

(query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
      (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:included_parts</span> 'sub-part 'part 'quantity)
           (<span style="color: #23d7d7;">:union-all</span>
            (<span style="color: #23d7d7;">:select</span> 'sub-part 'part 'quantity
                     <span style="color: #23d7d7;">:from</span> 'parts
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'part <span style="color: #e67128;">"our-product"</span>))
            (<span style="color: #23d7d7;">:select</span> 'p.sub-part 'p.part 'p.quantity
                     <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'included-parts 'pr)
                     (<span style="color: #23d7d7;">:as</span> 'parts 'p)
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'p.part 'pr.sub-part) )))
      (<span style="color: #23d7d7;">:select</span> 'sub-part (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:sum</span> 'quantity) 'total-quantity)
               <span style="color: #23d7d7;">:from</span> 'included-parts
               <span style="color: #23d7d7;">:group-by</span> 'sub-part)))

(query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
      (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:search-graph</span> 'id 'link 'data 'depth)
           (<span style="color: #23d7d7;">:union-all</span> (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data 1
                                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g))
                       (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data (<span style="color: #23d7d7;">:+</span> 'sg.depth 1)
                                <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g) (<span style="color: #23d7d7;">:as</span> 'search-graph 'sg)
                                <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'g.id 'sg.link))))
      (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'search-graph)))

(query (<span style="color: #ffad29; font-weight: bold;">:with-recursive</span>
      (<span style="color: #23d7d7;">:as</span> (<span style="color: #23d7d7;">:search-graph</span> 'id 'link 'data'depth 'path 'cycle)
           (<span style="color: #23d7d7;">:union-all</span>
            (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data 1
                     (<span style="color: #23d7d7;">:[]</span> 'g.f1 'g.f2) nil
                     <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g))
            (<span style="color: #23d7d7;">:select</span> 'g.id 'g.link 'g.data (<span style="color: #23d7d7;">:+</span> 'sg.depth 1)
                     (:|| 'path (<span style="color: #23d7d7;">:row</span> 'g.f1 'g.f2))
                     (<span style="color: #23d7d7;">:=</span> (<span style="color: #23d7d7;">:row</span> 'g.f1 'g.f2)
                         (<span style="color: #23d7d7;">:any*</span> 'path))
                     <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'graph 'g)
                     (<span style="color: #23d7d7;">:as</span> 'search-graph 'sg)
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span> (<span style="color: #23d7d7;">:=</span> 'g.id 'sg.link)
                                  (<span style="color: #23d7d7;">:not</span> 'cycle)))))
      (<span style="color: #23d7d7;">:select</span> '* <span style="color: #23d7d7;">:from</span> 'search-graph)))
</pre>
</div>
</div>
</div>
<div id="outline-container-org6f9c3d3" class="outline-3">
<h3 id="org6f9c3d3">sql-op :with-ordinality, :with-ordinality-as</h3>
<div class="outline-text-3" id="text-org6f9c3d3">
<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:select</span> '*
        <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:generate-series</span> 4 1 -1)
        <span style="color: #23d7d7;">:with-ordinality</span>))

(query (<span style="color: #23d7d7;">:select</span> 't1.*
        <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>)
        <span style="color: #23d7d7;">:with-ordinality-as</span> (<span style="color: #23d7d7;">:t1</span> 'keys 'n)
</pre>
</div>
</div>
</div>
</div>

<div id="outline-container-table-functions" class="outline-2">
<h2 id="table-functions">Table Functions</h2>
<div class="outline-text-2" id="text-table-functions">
</div>
<div id="outline-container-sql-op-for-update" class="outline-3">
<h3 id="sql-op-for-update">sql-op :for-update (query &amp;key of nowait)</h3>
<div class="outline-text-3" id="text-sql-op-for-update">
<p>
Locks the selected rows against concurrent updates. This will prevent the
rows from being modified or deleted by other transactions until the current
transaction ends. The :of keyword should be followed by one or more table
names. If provided, PostgreSQL will lock these tables instead of the ones
detected in the select statement. The :nowait keyword should be provided
by itself (with no argument attached to it), after all the :of arguments.
If :nowait is provided, PostgreSQL will throw an error if a table cannot be
locked immediately, instead of pausing until it's possible.
</p>
<div class="org-src-container">
<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>))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-for-share" class="outline-3">
<h3 id="sql-op-for-share">sql-op :for-share (query &amp;key of nowait)</h3>
<div class="outline-text-3" id="text-sql-op-for-share">
<p>
Similar to :for-update, except it acquires a shared lock on the table,
allowing other transactions to perform :for-share selects on the locked
tables.
</p>
</div>
</div>

<div id="outline-container-sql-op-insert-into" class="outline-3">
<h3 id="sql-op-insert-into">sql-op :insert-into (table &amp;rest rest)</h3>
<div class="outline-text-3" id="text-sql-op-insert-into">
<p>
You can use insert-into when you are:
</p>

<ol class="org-ol">
<li>Inserting from a select clause and you do not need to specify specific columns:</li>
</ol>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
         (<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))
</pre>
</div>
<ol class="org-ol">
<li>Inserting from a select clause and you specifying the columns which will be filled with values from the select clause</li>
</ol>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 't11
        <span style="color: #23d7d7;">:columns</span> 'region 'subregion 'country
        (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'region-name 'region)
                 (<span style="color: #23d7d7;">:as</span> 'sub-region-name 'subregion)
                 'country
         <span style="color: #23d7d7;">:from</span> 'regions)))
</pre>
</div>
<p>
or
</p>
<ol class="org-ol">
<li>You are alternating specific columns and values for a single row:</li>
</ol>
<div class="org-src-container">
<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>))
</pre>
</div>
<p>
You can use parameterized variables in the insert statement.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #ffad29; font-weight: bold;">let</span> ((name <span style="color: #e67128;">"test-cat4"</span>))
  (query (<span style="color: #23d7d7;">:insert-into</span> 'categories <span style="color: #23d7d7;">:set</span> 'name '$1) name))
</pre>
</div>
<p>
It is possible to add :returning, followed by a list of field names or
expressions, at the end of the :insert-into form. This will cause the
query to return the values of these expressions as a single row.
</p>
<div class="org-src-container">
<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>
        <span style="color: #23d7d7;">:returning</span> '*))

(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>
        <span style="color: #23d7d7;">:returning</span> 'id))
</pre>
</div>
<p>
In Postgresql versions 9.5 and above, it is possible to add
: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.
</p>
<div class="org-src-container">
<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
                     <span style="color: #23d7d7;">:on-conflict</span> 'column-A <span style="color: #23d7d7;">:do-nothing</span>
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
                     <span style="color: #23d7d7;">:returning</span> '*)
        <span style="color: #e67128;">"c"</span> 37)

(query (<span style="color: #23d7d7;">:insert-into</span> 'test-table <span style="color: #23d7d7;">:set</span> 'column-A '$1 'column-B '$2
                     <span style="color: #23d7d7;">:on-conflict-do-nothing</span> 'column-A
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
                     <span style="color: #23d7d7;">:returning</span> '*)
        <span style="color: #e67128;">"c"</span> 37)
</pre>
</div>
<p>
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:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
        <span style="color: #23d7d7;">:set</span> 'c1 <span style="color: #e67128;">"A"</span> 'c2 <span style="color: #e67128;">"B"</span>
        <span style="color: #23d7d7;">:overriding-system-value</span>))
</pre>
</div>
<p>
To create what is commonly known as an upsert, use :on-conflict-update
(if the item already exists, update the values)
followed by a list of field names which are checked for the conflict
then using :update-set followed by a list of field names or expressions
following the syntax for updating a table. This is sometimes called
an "upsert". Note that as per the postgresql sql documentation you must
prepend the table name to the column in the where statement if you are updating.
</p>
<div class="org-src-container">
<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
                     <span style="color: #23d7d7;">:on-conflict-update</span> 'column-A
                     <span style="color: #23d7d7;">:update-set</span> 'column-B '$2
                     <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
                     <span style="color: #23d7d7;">:returning</span> '*)
        <span style="color: #e67128;">"c"</span> 37)
</pre>
</div>
<p>
If the destination table has identity columns and you want to override those identity columns with specific values, you should specify :overriding-system-value.
</p>
<div class="org-src-container">
<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
        <span style="color: #23d7d7;">:overriding-system-value</span>
        <span style="color: #23d7d7;">:on-conflict-update</span> 'column-A
        <span style="color: #23d7d7;">:update-set</span> 'column-B '$2
        <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'test-table.column-A '$1)
        <span style="color: #23d7d7;">:returning</span> '*)
        <span style="color: #e67128;">"c"</span> 37)
</pre>
</div>
<p>
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.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-into</span> 'table1
         <span style="color: #23d7d7;">:overriding-user-value</span>
         (<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-insert-rows-into" class="outline-3">
<h3 id="sql-op-insert-rows-into">sql-op :insert-rows-into (table &amp;rest rest)</h3>
<div class="outline-text-3" id="text-sql-op-insert-rows-into">
<p>
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.)
</p>

<p>
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:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'my-table
        <span style="color: #23d7d7;">:columns</span> 'field-1 'field-2
        <span style="color: #23d7d7;">:values</span> '((42 <span style="color: #e67128;">"foobar"</span>) (23 <span style="color: #e67128;">"foobaz"</span>))))
</pre>
</div>
<p>
An example using a select statement returning one row:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(squery (<span style="color: #23d7d7;">:insert-rows-into</span> 't6
         <span style="color: #23d7d7;">:columns</span> 'tags
         <span style="color: #23d7d7;">:values</span> '(((<span style="color: #23d7d7;">:select</span> 'id
                     <span style="color: #23d7d7;">:from</span> 't5)))))
</pre>
</div>
<p>
If you will use the default columns, this can be simplified and the :columns
parameters can be dropped. Example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'my-table
        <span style="color: #23d7d7;">:values</span> '((42 <span style="color: #e67128;">"foobar"</span>) (23 <span style="color: #e67128;">"foobaz"</span>))))
</pre>
</div>
<p>
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:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'table1
                   <span style="color: #23d7d7;">:columns</span> 'c1 'c2
                   <span style="color: #23d7d7;">:overriding-system-value</span>
                   <span style="color: #23d7d7;">:values</span> '((1 <span style="color: #e67128;">"a"</span>) (2 <span style="color: #e67128;">"b"</span>))))

(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'table1
        <span style="color: #23d7d7;">:overriding-system-value</span>
        <span style="color: #23d7d7;">:values</span> '(((<span style="color: #23d7d7;">:select</span> 'c1 'c2 <span style="color: #23d7d7;">:from</span> 'table2)))))
</pre>
</div>
<p>
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
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
        <span style="color: #23d7d7;">:columns</span> 'did 'dname
        <span style="color: #23d7d7;">:values</span> '((10 <span style="color: #e67128;">"Conrad International"</span>))
        <span style="color: #23d7d7;">:on-conflict-do-nothing</span>
        <span style="color: #23d7d7;">:where</span> 'is-active))

(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
        <span style="color: #23d7d7;">:columns</span> 'did 'dname
        <span style="color: #23d7d7;">:values</span> '((10 <span style="color: #e67128;">"Conrad International"</span>))
        <span style="color: #23d7d7;">:on-conflict</span> 'did
        <span style="color: #23d7d7;">:do-nothing</span>
        <span style="color: #23d7d7;">:where</span> 'is-active))
</pre>
</div>
<p>
or :on-conflict-update
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'distributors
        <span style="color: #23d7d7;">:columns</span> 'did 'dname
        <span style="color: #23d7d7;">:values</span> '((5 <span style="color: #e67128;">"Gizmo Transglobal"</span>) (6 <span style="color: #e67128;">"Associated Computing Inc."</span>))
        <span style="color: #23d7d7;">:on-conflict-update</span> 'did
        <span style="color: #23d7d7;">:update-set</span> 'dname 'excluded.dname))
</pre>
</div>
<p>
You can use :on-conflict-on-constraint to check for conflicts on constraints.
</p>
<div class="org-src-container">
<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
                                <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))
                                <span style="color: #23d7d7;">:on-conflict-on-constraint</span> 'somekey
                                <span style="color: #23d7d7;">:do-nothing</span>
                   <span style="color: #23d7d7;">:returning</span> '*))

(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'test <span style="color: #23d7d7;">:columns</span> 'some-key 'some-val
                                <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))
                                <span style="color: #23d7d7;">:on-conflict-on-constraint</span> 'somekey
                                <span style="color: #23d7d7;">:update-set</span> 'some-val 'excluded.some-val
                   <span style="color: #23d7d7;">:returning</span> '*))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-update" class="outline-3">
<h3 id="sql-op-update">sql-op :update (table &amp;rest rest)</h3>
<div class="outline-text-3" id="text-sql-op-update">
<p>
Update values in a table. There are two ways to update the values
</p>

<p>
The first method uses the keyword :set and any number of alternating field names and values, like
for :insert-into. Next comes the optional keyword :from, followed by at
least one table name and then any number of join statements, like for
:select. After the joins, an optional :where keyword followed by the condition,
and :returning keyword followed by a list of field names or expressions
indicating values to be returned as query result.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:update</span> 'weather
        <span style="color: #23d7d7;">:set</span> 'temp-lo (<span style="color: #23d7d7;">:+</span> 'temp-lo 1)
             'temp-hi (<span style="color: #23d7d7;">:+</span> 'temp-lo 15)
             'prcp <span style="color: #23d7d7;">:default</span>
        <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>)
                     (<span style="color: #23d7d7;">:=</span> 'date <span style="color: #e67128;">"2003-07-03"</span>))
        <span style="color: #23d7d7;">:returning</span> 'temp-lo 'temp-hi 'prcp))
</pre>
</div>
<p>
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:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:update</span> 'weather
        <span style="color: #23d7d7;">:columns</span> 'temp-lo 'temp-hi 'prcp
                 (<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>)
        <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>)
                     (<span style="color: #23d7d7;">:=</span> 'date <span style="color: #e67128;">"2003-07-03"</span>))))

(query (<span style="color: #23d7d7;">:update</span> 't1
        <span style="color: #23d7d7;">:columns</span> 'database-name 'encoding
                 (<span style="color: #23d7d7;">:select</span> 'x.datname 'x.encoding
                  <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg-database 'x)
                  <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> 'x.oid 't1.oid))))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-delete-from" class="outline-3">
<h3 id="sql-op-delete-from">sql-op :delete-from (table &amp;rest rest)</h3>
<div class="outline-text-3" id="text-sql-op-delete-from">
<p>
Delete rows from the named table. Can be given a :where argument followed
by a condition, and a :returning argument, followed by one or more
expressions that should be returned for every deleted row.
</p>
<div class="org-src-container">
<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)))
</pre>
</div>
</div>
</div>
<div id="outline-container-sql-op-create-table" class="outline-3">
<h3 id="sql-op-create-table">sql-op :create-table (name (&amp;rest columns) &amp;rest options)</h3>
<div class="outline-text-3" id="text-sql-op-create-table">
<p>
Create a new table. The simplest example would pass two parameters,
the table name and a list of lists providing information for each column.
For example:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-table</span> 'george ((id <span style="color: #23d7d7;">:type</span> integer))))
</pre>
</div>
<p>
where 'george is the name of the table, it has 1 column named id
which is limited to integers. There are no indexes or keys in this
example.
</p>

<p>
See <a href="create-tables.html">create-tables.html</a> for more detailed examples.
</p>
</div>

<div id="outline-container-sql-op-column-definition-parameters" class="outline-4">
<h4 id="sql-op-column-definition-parameters">Column Definition parameters</h4>
<div class="outline-text-4" id="text-sql-op-column-definition-parameters">
<p>
After the table name a list of column definitions
follows, which are lists that start with a name, followed by one or
more of the following keyword arguments:
</p>

<ul class="org-ul">
<li>:type</li>
</ul>

<p>
This one is required. It specifies the type of the column. Use a type like
(or db-null integer) to specify a column that may have NULL values.
</p>

<ul class="org-ul">
<li>:default</li>
</ul>

<p>
Provides a default value for the field.
</p>

<ul class="org-ul">
<li>:unique</li>
</ul>

<p>
If this argument is non-nil, the values of the column must be unique.
</p>

<ul class="org-ul">
<li>:primary-key</li>
</ul>

<p>
When non-nil, the column is a primary key of the table.
</p>

<ul class="org-ul">
<li>:check</li>
</ul>

<p>
Adds a constraint to this column. The value provided for this argument must
be an S-SQL expression that returns a boolean value. It can refer to other
columns in the table if needed.
</p>

<ul class="org-ul">
<li>:references</li>
</ul>

<p>
Adds a foreign key constraint to this table. The argument provided must be a
list of the form (target &amp;optional on-delete on-update). When target is a
symbol, it names the table to whose primary key this constraint refers. When
it is a list, its first element is the table, and its second element the
column within that table that the key refers to. on-delete and on-update
can be used to specify the actions that must be taken when the row that this
key refers to is deleted or changed. Allowed values are :restrict, :set-null,
</p>

<ul class="org-ul">
<li>:set-default, :cascade, and :no-action.</li>
</ul>
</div>
</div>

<div id="outline-container-sql-op-table-constraints" class="outline-4">
<h4 id="sql-op-table-constraints">Table Constraints</h4>
<div class="outline-text-4" id="text-sql-op-table-constraints">
<p>
After the list of columns, zero or more extra options (table constraints) can
be specified. These are lists starting with one of the following keywords:
</p>

<ul class="org-ul">
<li>:check</li>
</ul>

<p>
Adds a constraint to the table. Takes a single S-SQL expression that produces
a boolean as its argument.
</p>

<ul class="org-ul">
<li>:primary-key</li>
</ul>

<p>
Specifies a primary key for the table. The arguments to this option are the
names of the columns that this key consists of.
</p>

<ul class="org-ul">
<li>:unique</li>
</ul>

<p>
Adds a unique constraint to a group of columns. Again, the arguments are a
list of symbols that indicate the relevant columns.
</p>

<ul class="org-ul">
<li>:foreign-key</li>
</ul>

<p>
Create a foreign key. The arguments should have the form
(columns target &amp;optional on-delete on-update), where columns is a list of
columns that are used by this key, while the rest of the arguments have
the same meaning as they have in the :references option for columns.
Every list can start with :constraint name to create a specifically named
constraint.
</p>

<p>
Note that, unlike most other operators, :create-table expects most of its
arguments to be unquoted symbols. The exception to this is the value
of :check constraints: These must be normal S-SQL expressions, which means
that any column names they contain should be quoted. When programmatically
generating table definitions, sql-compile is usually more practical than
the sql macro.
</p>

<p>
Here is an example of a :create-table form:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(<span style="color: #23d7d7;">:create-table</span> enemy
  ((name <span style="color: #23d7d7;">:type</span> string <span style="color: #23d7d7;">:primary-key</span> t)
   (age <span style="color: #23d7d7;">:type</span> integer)
   (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>))
   (fatal-weakness <span style="color: #23d7d7;">:type</span> text <span style="color: #23d7d7;">:default</span> <span style="color: #e67128;">"None"</span>)
   (identifying-color <span style="color: #23d7d7;">:type</span> (string 20) <span style="color: #23d7d7;">:unique</span> t))
  (<span style="color: #23d7d7;">:foreign-key</span> (identifying-color) (colors name))
  (<span style="color: #23d7d7;">:constraint</span> enemy-age-check <span style="color: #23d7d7;">:check</span> (<span style="color: #23d7d7;">:&gt;</span> 'age 12)))
</pre>
</div>
<p>
For more detail and examples on building tables
using the s-sql approach, see <a href="create-tables.html">create-tables.html</a>
</p>
</div>
</div>
</div>

<div id="outline-container-sql-op-alter-table" class="outline-3">
<h3 id="sql-op-alter-table">sql-op :alter-table (name action &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-alter-table">
<p>
Alters named table. Currently changing a column's data type is not supported.
The meaning of args depends on action:
</p>

<ul class="org-ul">
<li>:add-column</li>
</ul>

<p>
Adds column to table. args should be a column in the same form as for :create-table.
</p>
<div class="org-src-container">
<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)))
</pre>
</div>

<ul class="org-ul">
<li>:set-default</li>
</ul>

<p>
Adds or changes a default value for a column
</p>
<div class="org-src-container">
<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>)))
</pre>
</div>

<ul class="org-ul">
<li>:drop-column</li>
</ul>

<p>
Drops a column from the table.
</p>
<div class="org-src-container">
<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))
</pre>
</div>

<ul class="org-ul">
<li>:add-constraint</li>
</ul>

<p>
Adds a named constraint to the table.
</p>
<div class="org-src-container">
<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))

(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)))
</pre>
</div>

<ul class="org-ul">
<li>:drop-constraint</li>
</ul>

<p>
Drops constraint. First of args should name a constraint to be dropped; second,
optional argument specifies behaviour regarding objects dependent on the
constraint and it may equal :cascade or :restrict.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (alter-table enemy <span style="color: #23d7d7;">:drop-constraint</span> enemy-age-check))
</pre>
</div>

<ul class="org-ul">
<li>:add</li>
</ul>

<p>
Adds an unnamed constraint to table. args should be a constraint in the same
form as for :create-table. (This is for backwards-compatibility, you should
use named constraints.)
</p>

<ul class="org-ul">
<li>:rename</li>
</ul>

<p>
Adds the ability to rename a table.
</p>

<ul class="org-ul">
<li>:rename-column</li>
</ul>

<p>
Adds the ability to rename a column of a table.
</p>
<div class="org-src-container">
<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))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-drop-table" class="outline-3">
<h3 id="sql-op-drop-table">sql-op :drop-table (name)</h3>
<div class="outline-text-3" id="text-sql-op-drop-table">
<p>
Drops the named table. You may optionally pass :if-exists before the name
to suppress the error message if the table does not exist. You can also
optionally pass :cascade after the name to indicate that it should also
drop any other tables, indices, etc which depend on that table.
Accepts strings, variable or symbol as the identifier.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-table</span> 'table1))
(query (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> 'table1))
(query (<span style="color: #23d7d7;">:drop-table</span> <span style="color: #23d7d7;">:if-exists</span> 'table1 <span style="color: #23d7d7;">:cascade</span>))
(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>))
(<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 <span style="color: #e67128;">"table1"</span>))
    (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>))
               <span style="color: #e67128;">"DROP TABLE IF EXISTS table1 CASCADE"</span>))
(<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 'table-1))
    (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>))
               <span style="color: #e67128;">"DROP TABLE IF EXISTS table_1 CASCADE"</span>))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-truncate" class="outline-3">
<h3 id="sql-op-truncate">sql-op :truncate (&amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-truncate">
<p>
Truncates one or more tables, deleting all the rows. Optional keyword arguments are
allowed in the following order. Note that :continue-identity and :restart-identity
make no sense if both are included.
</p>

<ul class="org-ul">
<li>:only (if not specified, the table and its descendants are truncated).</li>
<li>:continue-identity (the values of sequences will not be changed. This is the default)</li>
<li>:restart-identity (the values of sequences owned by the table(s) will be restarted)</li>
<li>:cascade (will cascade the truncation through tables using foreign keys.)</li>
</ul>

<p>
Example calls would be:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable))

(query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:only</span>))

(query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:only</span> <span style="color: #23d7d7;">:continue-identity</span>))

(query (<span style="color: #23d7d7;">:truncate</span> 'bigtable 'fattable <span style="color: #23d7d7;">:restart-identity</span>))

(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> ))

</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-create-index" class="outline-3">
<h3 id="sql-op-create-index">sql-op :create-index (name &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-create-index">
<p>
Create an index on a table. After the name of the index the keyword :on should
follow, with the table name after it. Then the keyword :fields, followed by
one or more column names. Optionally, a :where clause with a condition can
be added at the end to make a partial index.
</p>

<div class="org-src-container">
<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))

<span style="color: #e67128;">"CREATE INDEX gin_idx ON historical_events USING GIN (data)"</span>
</pre>
</div>
</div>
</div>


<div id="outline-container-sql-op-create-unique-index" class="outline-3">
<h3 id="sql-op-create-unique-index">sql-op :create-unique-index (name &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-create-unique-index">
<p>
Works like :create-index, except that the index created is unique.
</p>
</div>
</div>

<div id="outline-container-sql-op-drop-index" class="outline-3">
<h3 id="sql-op-drop-index">sql-op :drop-index (name)</h3>
<div class="outline-text-3" id="text-sql-op-drop-index">
<p>
Drop an index. Takes :if-exists and/or :cascade arguments like :drop-table.
Accepts strings, variable or symbol as the identifier.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-index</span> 'index1))
(query (<span style="color: #23d7d7;">:drop-index</span> <span style="color: #23d7d7;">:if-exists</span> 'index1))
(query (<span style="color: #23d7d7;">:drop-index</span> <span style="color: #23d7d7;">:if-exists</span> 'index1 <span style="color: #23d7d7;">:cascade</span>))
(<span style="color: #ffad29; font-weight: bold;">let</span> ((table-var1 <span style="color: #e67128;">"table1"</span>))
    (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>))
               <span style="color: #e67128;">"DROP INDEX IF EXISTS table1 CASCADE"</span>))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-create-sequence" class="outline-3">
<h3 id="sql-op-create-sequence">sql-op :create-sequence (name &amp;key increment min-value max-value start cache cycle)</h3>
<div class="outline-text-3" id="text-sql-op-create-sequence">
<p>
Create a sequence with the given name. The rest of the arguments control
the way the sequence selects values.
</p>
</div>
</div>

<div id="outline-container-sql-op-alter-sequence" class="outline-3">
<h3 id="sql-op-alter-sequence">sql-op :alter-sequence (name)</h3>
<div class="outline-text-3" id="text-sql-op-alter-sequence">
<p>
Alters a sequence. See <a href="https://www.postgresql.org/docs/10/static/sql-altersequence.html">Postgresql documentation</a> for parameters.
</p>

<ul class="org-ul">
<li>:increment</li>
</ul>

<p>
Sets the amount by which each subsequent increment will be increased.
</p>

<ul class="org-ul">
<li>:min-value</li>

<li>:max-value</li>

<li>:no-min</li>

<li>:no-max</li>

<li>:start</li>

<li>:restart</li>

<li>:cache</li>

<li>:cycle</li>

<li>:no-cycle</li>

<li>:owned-by</li>

<li>:if-exists before the name to suppress the error message.</li>
</ul>
</div>
</div>

<div id="outline-container-sql-op-drop-sequence" class="outline-3">
<h3 id="sql-op-drop-sequence">sql-op :drop-sequence (name)</h3>
<div class="outline-text-3" id="text-sql-op-drop-sequence">
<p>
Drop a sequence. Takes :if-exists and/or :cascade arguments like :drop-table.
Accepts strings, variable or symbol as the identifier.
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-sequence</span> 'sequence1))
(query (<span style="color: #23d7d7;">:drop-sequence</span> <span style="color: #23d7d7;">:if-exists</span> 'sequence1))
(query (<span style="color: #23d7d7;">:drop-sequence</span> <span style="color: #23d7d7;">:if-exists</span> 'sequence1 <span style="color: #23d7d7;">:cascade</span>))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-create-view" class="outline-3">
<h3 id="sql-op-create-view">sql-op :create-view (name query)</h3>
<div class="outline-text-3" id="text-sql-op-create-view">
<p>
Create a view from an S-SQL-style query.
</p>
</div>
</div>

<div id="outline-container-sql-op-drop-view" class="outline-3">
<h3 id="sql-op-drop-view">sql-op :drop-view (name)</h3>
<div class="outline-text-3" id="text-sql-op-drop-view">
<p>
Drop a view. Takes optional :if-exists argument.
Accepts strings, variable or symbol as the identifier.
</p>
</div>
</div>
<div id="outline-container-sql-op-set-constraints" class="outline-3">
<h3 id="sql-op-set-constraints">sql-op :set-constraints (state &amp;rest constraints)</h3>
<div class="outline-text-3" id="text-sql-op-set-constraints">
<p>
Configure whether deferrable constraints should be checked when a statement
is executed, or when the transaction containing that statement is completed.
The provided state must be either :immediate, indicating the former,
or :deferred, indicating the latter. The constraints must be either the
names of the constraints to be configured, or unspecified, indicating that
all deferrable constraints should be thus configured.
</p>
</div>
</div>

<div id="outline-container-sql-op-listen" class="outline-3">
<h3 id="sql-op-listen">sql-op :listen (channel)</h3>
<div class="outline-text-3" id="text-sql-op-listen">
<p>
Tell the server to listen for notification events on channel channel,
a string, on the current connection.
</p>
</div>
</div>

<div id="outline-container-sql-op-unlisten" class="outline-3">
<h3 id="sql-op-unlisten">sql-op :unlisten (channel)</h3>
<div class="outline-text-3" id="text-sql-op-unlisten">
<p>
Stop listening for events on channel.
</p>
</div>
</div>

<div id="outline-container-sql-op-notify" class="outline-3">
<h3 id="sql-op-notify">sql-op :notify (channel &amp;optional payload)</h3>
<div class="outline-text-3" id="text-sql-op-notify">
<p>
Signal a notification event on channel channel, a string. The optional
payload string can be used to send additional event information to the listeners.
</p>
</div>
</div>

<div id="outline-container-sql-op-create-role" class="outline-3">
<h3 id="sql-op-create-role">sql-op :create-role (role &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-create-role">
<p>
Create a new role (user). Following the role name are optional keywords
arguments:
</p>

<ul class="org-ul">
<li>:options</li>
</ul>

<p>
One or more of the no-parameter options to PostgreSQL's CREATE ROLE SQL command.
</p>

<ul class="org-ul">
<li>:password</li>
</ul>

<p>
Sets the role's password. (A password is only of use for roles having the LOGIN
attribute, but you can nonetheless define one for roles without it.) If you do
not plan to use password authentication you can omit this option. If no
password is specified, the password will be set to null and password
authentication will always fail for that user.
</p>

<ul class="org-ul">
<li>:connection-limit</li>
</ul>

<p>
If role can log in, this specifies how many concurrent connections the role can
make. -1 (the default) means no limit.
</p>

<ul class="org-ul">
<li>:valid-until</li>
</ul>

<p>
The :valid-until clause sets a date and time after which the role's password
is no longer valid. If this clause is omitted the password will be valid for
all time.
</p>

<ul class="org-ul">
<li>:role</li>
</ul>

<p>
Lists one or more existing roles which are automatically added as members of
the new role. (This in effect makes the new role a “group”.)
</p>

<ul class="org-ul">
<li>:in-role</li>
</ul>

<p>
Lists one or more existing roles to which the new role will be immediately
added as a new member.
</p>

<p>
Here is an example of a :create-role form:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-role</span> 'user23
                     <span style="color: #23d7d7;">:options</span> 'SUPERUSER 'NOINHERIT 'LOGIN
                     <span style="color: #23d7d7;">:password</span> <span style="color: #e67128;">"mypassword"</span>
                     <span style="color: #23d7d7;">:connection-limit</span> 100 <span style="color: #23d7d7;">:role</span> 'users))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-create-database" class="outline-3">
<h3 id="sql-op-create-database">sql-op :create-database (name)</h3>
<div class="outline-text-3" id="text-sql-op-create-database">
<p>
Create a new database with the given name.
</p>
</div>
</div>

<div id="outline-container-sql-op-drop-database" class="outline-3">
<h3 id="sql-op-drop-database">sql-op :drop-database (name)</h3>
<div class="outline-text-3" id="text-sql-op-drop-database">
<p>
Drops the named database. You may optionally pass :if-exists before the
name to suppress the error message. Examples:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:drop-database</span> 'database-name))
(query (<span style="color: #23d7d7;">:drop-database</span> <span style="color: #23d7d7;">:if-exists</span> 'database-name))
(<span style="color: #ffad29; font-weight: bold;">let</span> ((var 'my-database)) (query (<span style="color: #23d7d7;">:drop-database</span> var)))
</pre>
</div>
</div>
</div>

<div id="outline-container-sql-op-copy" class="outline-3">
<h3 id="sql-op-copy">sql-op :copy (table &amp;rest args)</h3>
<div class="outline-text-3" id="text-sql-op-copy">
<p>
Move data between Postgres tables and filesystem files. Table name is required
followed by one or more of the following keyword arguments. Documentation for
the copy command provides a full reference. An example from the Greenplum
tutorial:
</p>
<div class="org-src-container">
<pre class="src src-lisp">(query
   (<span style="color: #23d7d7;">:copy</span> 'faa.d_airlines
    <span style="color: #23d7d7;">:columns</span> 'airlineid 'airline_desc
    <span style="color: #23d7d7;">:from</span> <span style="color: #e67128;">"/home/gpadmin/gpdb-sandbox-tutorials/faa/L_AIRLINE_ID.csv"</span>
    <span style="color: #23d7d7;">:on-segment</span> t
    <span style="color: #23d7d7;">:binary</span> t
    <span style="color: #23d7d7;">:oids</span> t
    <span style="color: #23d7d7;">:header</span> t
    <span style="color: #23d7d7;">:delimiter</span> <span style="color: #e67128;">","</span>
    <span style="color: #23d7d7;">:null</span> <span style="color: #e67128;">"NULL"</span>
    <span style="color: #23d7d7;">:escape</span> <span style="color: #e67128;">"my-escape-string"</span>
    <span style="color: #23d7d7;">:newline</span> <span style="color: #e67128;">"CR"</span>
    <span style="color: #23d7d7;">:csv</span> t
    <span style="color: #23d7d7;">:log-errors</span> t
    <span style="color: #23d7d7;">:segment-reject-limit</span> 100 'ROWS))

</pre>
</div>
</div>
</div>
</div>
<div id="outline-container-dynamic-queries-composition-and-parameterized-queries" class="outline-2">
<h2 id="dynamic-queries-composition-and-parameterized-queries">Dynamic Queries, Composition and Parameterized Queries</h2>
<div class="outline-text-2" id="text-dynamic-queries-composition-and-parameterized-queries">
</div>

<div id="outline-container-dynamic-queries-overview" class="outline-3">
<h3 id="dynamic-queries-overview">Overview</h3>
<div class="outline-text-3" id="text-dynamic-queries-overview">
<p>
The question gets asked how to build dynamic or composable queries in
postmodern. First we need to understand the context - is the programmer
building the query or are you taking data from a user and using that to
build a query?
</p>
</div>

<div id="outline-container-programmer-built-queries" class="outline-4">
<h4 id="programmer-built-queries">Programmer Built Queries</h4>
<div class="outline-text-4" id="text-programmer-built-queries">
<p>
The question gets asked how to build dynamic or composable queries in
postmodern. First we need to understand the context - is the programmer
building the query or are you taking data from a user and using that to
build a query? We need to remember that the query macro assumes that everything
that is not a list starting with a keyword will evaluate to a string.
</p>

<p>
In any case you will need to ensure that either you have control over the inputs
or they still result in parameterized queries. If not you have opened yourself up
to an sql injection attack.
</p>

<p>
If you are not using s-sql, then it becomes easy. The query macro
assumes that everything that is not a list starting with a keyword will
evaluate to a string. That means you can build it with a simple format
string
</p>
<div class="org-src-container">
<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>))
</pre>
</div>

<p>
With s-sql, there are generally three approaches to building dynamic or
composible queries: pass symbols and values as variables,  use sql-compile
or use :raw.
</p>

<p>
For purposes of this example, we will use the following employee table:
</p>

<div class="org-src-container">
<pre class="src src-lisp">(query (<span style="color: #23d7d7;">:create-table</span> employee ((id <span style="color: #23d7d7;">:type</span> int)
                                (name <span style="color: #23d7d7;">:type</span> text)
                                (salary <span style="color: #23d7d7;">:type</span> numeric)
                                (start_date <span style="color: #23d7d7;">:type</span> date)
                                (city <span style="color: #23d7d7;">:type</span> text)
                                (region <span style="color: #23d7d7;">:type</span> char)
                                (age <span style="color: #23d7d7;">:type</span> int))))

(query (<span style="color: #23d7d7;">:insert-rows-into</span> 'employee
                          <span style="color: #23d7d7;">:columns</span> 'id 'name 'salary 'start-date 'city 'region 'age
                          <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)
                                    (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)
                                    (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)
                                    (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)
                                    (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)
                                    (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)
                                    (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)
                                    (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)
                                    (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))))
</pre>
</div>
</div>
<ul class="org-ul">
<li><a id="symbols-in-variables"></a>Approach #1 Using symbols in variables<br>
<div class="outline-text-5" id="text-symbols-in-variables">
</div>
<ul class="org-ul">
<li><a id="orgb3aab1e"></a>Select Statements<br>
<div class="outline-text-6" id="text-orgb3aab1e">
<p>
Consider the following two toy examples where we determine the table and columns
to be selected using symbols (either keyword or quoted) inside variables.
</p>
<div class="org-src-container">
<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))
    (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))
((3 <span style="color: #e67128;">"Celia"</span>))

(<span style="color: #ffad29; font-weight: bold;">let</span> ((table 'employee) (col1 'name) (col2 'salary) (id 3))
    (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))
((<span style="color: #e67128;">"Celia"</span> 24020))
</pre>
</div>
<p>
This will not work if you use strings instead of symbols because sql-expand
will wrap the strings in the variables in escape format as if they were string
constants and Postgresql will throw an error because it is not expecting
string constants in the middle of a select statement.
</p>
</div>
</li>

<li><a id="org0a3c30e"></a>Update Statements<br>
<div class="outline-text-6" id="text-org0a3c30e">
<p>
This works with update statements as well
</p>
<div class="org-src-container">
<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))
    (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)
    (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))
((3 <span style="color: #e67128;">"Celeste"</span>))
</pre>
</div>
</div>
</li>
<li><a id="orga8790bf"></a>Insert Statements<br>
<div class="outline-text-6" id="text-orga8790bf">
<p>
This works with insert-into statements as well
</p>
<div class="org-src-container">
<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>)
      (id 10) (col3 'salary) (col3-value 3452) (col4 'start-date)
      (col4-value <span style="color: #e67128;">"02/01/03"</span>) (col5 'city) (col5-value <span style="color: #e67128;">"Victoria"</span>)
      (col6 'region) (col6-value <span style="color: #e67128;">"N"</span>) (col7 'age) (col7-value 32))
  (query (<span style="color: #23d7d7;">:insert-into</span> table <span style="color: #23d7d7;">:set</span> col1 id col2 new-name col3 col3-value
                       col4 col4-value col5 col5-value col6 col6-value
                       col7 col7-value)))

(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 )))
((10 <span style="color: #e67128;">"Rochelle"</span> 3452))
</pre>
</div>
</div>
</li>
</ul>
</li>
<li><a id="org7846f41"></a>Delete Statements<br>
<div class="outline-text-5" id="text-org7846f41">
<p>
This works with delete statements as well
</p>
<div class="org-src-container">
<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))
   (query (<span style="color: #23d7d7;">:delete-from</span> table <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:=</span> col1 col1-value))))
</pre>
</div>
</div>
</li>
</ul>
</div>
<div id="outline-container-approach-use-sql-compile" class="outline-4">
<h4 id="approach-use-sql-compile">Approach #2 Use sql-compile</h4>
<div class="outline-text-4" id="text-approach-use-sql-compile">
<p>
Sql-compile does a run-time compilation of an s-sql expression. In the
following example, we create a function that accepts a where-clause, a
table-name, 3 columns to select and two parameters to go into the where
clause.
</p>

<div class="org-src-container">
<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)
       (<span style="color: #ffad29; font-weight: bold;">with-test-connection</span>
       (query (sql-compile
                (append `(<span style="color: #23d7d7;">:select</span> ,col1 ,col2 ,col3 <span style="color: #23d7d7;">:from</span> ,table-name <span style="color: #23d7d7;">:where</span>)
                       where-clause))
              arg1 arg2)))

(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)

((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>))
</pre>
</div>

<p>
If we just look at what this call to sql-compile in toy-example
generates, it would look like:
</p>

<div class="org-src-container">
<pre class="src src-lisp"><span style="color: #e67128;">"(SELECT id, name, city FROM employee WHERE ((city = $1) and (salary &gt; $2)))"</span>
</pre>
</div>

<p>
This example is still a parameterized query but for security reasons you
will need to be very careful how you generate the where clause.
</p>

<p>
Another example with sql-compile and append, in this case updating a
table and setting two columns to NULL.
</p>

<div class="org-src-container">
<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>)
                      (<span style="color: #ffad29; font-weight: bold;">loop</span> for a in '(<span style="color: #e67128;">"col1"</span> <span style="color: #e67128;">"col2"</span>)
                            collect a
                            collect <span style="color: #23d7d7;">:NULL</span>)))

 <span style="color: #e67128;">"UPDATE table1 SET E'col1' = NULL, E'col2' = NULL"</span>
</pre>
</div>

<p>
Lets think about it differently. What if we know the universe of columns
we want to select, but want to conditionally select some of them.
Suppose we know our targetted table has columns:
</p>

<div class="org-src-container">
<pre class="src src-lisp">'id 'name 'salary 'start-date 'city 'region 'age.
</pre>
</div>

<p>
We may decide we always want name, city and age, but salary and
start-date are conditional.
</p>

<div class="org-src-container">
<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)
  (sql-compile
   (remove nil `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age
                         ,(<span style="color: #ffad29; font-weight: bold;">if</span> salaryp 'salary nil)
                         ,(<span style="color: #ffad29; font-weight: bold;">if</span> start-date-p 'start-date nil)
                         <span style="color: #23d7d7;">:from</span> 'employee))))

(query (toy-example-2 t t))

((<span style="color: #e67128;">"Jason"</span> <span style="color: #e67128;">"New York"</span> 29 40420 #&lt;SIMPLE-DATE:DATE 01-02-1994&gt;)
 (<span style="color: #e67128;">"Robert"</span> <span style="color: #e67128;">"Vancouver"</span> 21 14420 #&lt;SIMPLE-DATE:DATE 02-01-1995&gt;)
 (<span style="color: #e67128;">"Celia"</span> <span style="color: #e67128;">"Toronto"</span> 24 24020 #&lt;SIMPLE-DATE:DATE 03-12-1996&gt;)
 (<span style="color: #e67128;">"Linda"</span> <span style="color: #e67128;">"New York"</span> 28 40620 #&lt;SIMPLE-DATE:DATE 04-11-1997&gt;)
 (<span style="color: #e67128;">"David"</span> <span style="color: #e67128;">"Vancouver"</span> 31 80026 #&lt;SIMPLE-DATE:DATE 05-10-1998&gt;)
 (<span style="color: #e67128;">"James"</span> <span style="color: #e67128;">"Toronto"</span> 26 70060 #&lt;SIMPLE-DATE:DATE 06-09-1999&gt;)
 (<span style="color: #e67128;">"Alison"</span> <span style="color: #e67128;">"New York"</span> 38 90620 #&lt;SIMPLE-DATE:DATE 07-08-2000&gt;)
 (<span style="color: #e67128;">"Chris"</span> <span style="color: #e67128;">"Vancouver"</span> 22 26020 #&lt;SIMPLE-DATE:DATE 08-07-2001&gt;)
 (<span style="color: #e67128;">"Mary"</span> <span style="color: #e67128;">"Toronto"</span> 34 60020 #&lt;SIMPLE-DATE:DATE 08-06-2002&gt;))

(query (toy-example-2 t nil))

((<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)
 (<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)
 (<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)
 (<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)
 (<span style="color: #e67128;">"Mary"</span> <span style="color: #e67128;">"Toronto"</span> 34 60020))
</pre>
</div>

<p>
You could skip the (remove nil&#x2026; portion and substitute t for nil. E.g.
</p>

<div class="org-src-container">
<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)
        (sql-compile
         `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age
                   ,(<span style="color: #ffad29; font-weight: bold;">if</span> salaryp 'salary t)
                   ,(<span style="color: #ffad29; font-weight: bold;">if</span> start-date-p 'start-date t)
                   <span style="color: #23d7d7;">:from</span> 'employee)))
</pre>
</div>

<p>
But I prefer to remove those segments completely from the query.
</p>

<p>
Following on this same thread of thought, you can define a portion of
the sql in a let clause:
</p>

<div class="org-src-container">
<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>)))
        (query (sql-compile
          `(<span style="color: #23d7d7;">:select</span> 'name 'city 'age <span style="color: #23d7d7;">:from</span> 'employee <span style="color: #23d7d7;">:where</span> ,sql1))))

      ((<span style="color: #e67128;">"Jason"</span> <span style="color: #e67128;">"New York"</span> 29))
</pre>
</div>

<p>
An example of this would be getting more columns depending on the
postgresql server versionr:
</p>

<div class="org-src-container">
<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)
        <span style="color: #e67128;">"Returns variable amounts of information depending on the postgresql server version"</span>
        (<span style="color: #ffad29; font-weight: bold;">let*</span> ((version&gt;11 (postgresql-version-at-least <span style="color: #e67128;">"12.0"</span> =*database*=))
               (version&gt;10 (postgresql-version-at-least <span style="color: #e67128;">"11.0"</span> =*database*=))
               (select-query (sql-compile
                              `(<span style="color: #23d7d7;">:order-by</span>
                                (<span style="color: #23d7d7;">:select</span> (<span style="color: #23d7d7;">:as</span> 'a.attnum 'ordinal-position)
                                         (<span style="color: #23d7d7;">:as</span> 'a.attname 'column-name)
                                         (<span style="color: #23d7d7;">:as</span> 'tn.typname 'data-type)
                                         ,(<span style="color: #ffad29; font-weight: bold;">if</span> version&gt;10 'a.attidentity t)
                                         ,(<span style="color: #ffad29; font-weight: bold;">if</span> version&gt;11 'a.attgenerated t)
                                         <span style="color: #23d7d7;">:from</span> (<span style="color: #23d7d7;">:as</span> 'pg_class 'c)
                                         (<span style="color: #23d7d7;">:as</span> 'pg_attribute 'a)
                                         (<span style="color: #23d7d7;">:as</span> 'pg_type 'tn)
                                         <span style="color: #23d7d7;">:where</span> (<span style="color: #23d7d7;">:and</span>
                                                 (<span style="color: #23d7d7;">:=</span> 'c.relname '$1)
                                                 (<span style="color: #23d7d7;">:&gt;</span> 'a.attnum 0)
                                                 (<span style="color: #23d7d7;">:=</span> 'a.attrelid 'c.oid)
                                                 (<span style="color: #23d7d7;">:=</span> 'a.atttypid 'tn.oid)))
                                'a.attnum))))
          (query select-query
                 (to-sql-name table-name))))
</pre>
</div>
</div>
</div>

<div id="outline-container-raw-approach" class="outline-4">
<h4 id="raw-approach">Approach #3 Use :raw</h4>
<div class="outline-text-4" id="text-raw-approach">
<p>
To quote Marijn, the :raw keyword takes a string and inserts it straight
into the query. I try to stay away from :raw if possible, but
sometimes&#x2026;
</p>

<div class="org-src-container">
<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>))))
</pre>
</div>
</div>
</div>

<div id="outline-container-queries-with-user-input" class="outline-4">
<h4 id="queries-with-user-input">Queries with User Input</h4>
<div class="outline-text-4" id="text-queries-with-user-input">
<p>
In any of the above approaches to building queries you will need to
ensure that either you have control over the inputs or they still result
in parameterized queries. If not, you have opened yourself up to an sql
injection attack.
</p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>