doc/s-sql.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
   2 <html xmlns="http://www.w3.org/1999/xhtml">
   3
   4   <head>
   5     <title>S-SQL reference manual</title>
   6     <link rel="stylesheet" type="text/css" href="style.css"/>
   7     <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
   8   </head>
   9
  10   <body>
  11
  12     <h1>S-SQL reference manual</h1>
  13
  14     <p>This is the reference manual for the S-SQL component of the <a
  15     href="index.html">postmodern</a> library.</p>
  16
  17     <p>S-SQL provides a lispy syntax for SQL queries, and knows how to
  18     convert various lisp types to their textual SQL representation. It
  19     takes care to do as much of the work as possible at compile-time,
  20     so that at runtime a string concatenation is all that is needed to
  21     produce the final SQL query.</p>
  22
  23     <h2>Contents</h2>
  24
  25     <ol>
  26       <li><a href="#interface">Interface</a></li>
  27       <li><a href="#types">SQL Types</a></li>
  28       <li><a href="#syntax">SQL Syntax</a></li>
  29       <li><a href="#index">Symbol-index</a></li>
  30     </ol>
  31
  32     <h2><a name="interface"></a>Interface</h2>
  33
  34     <p class="def">
  35       <span>macro</span>
  36       <a name="sql"></a>
  37       sql (form)
  38       <br/>&#8594; string
  39     </p>
  40
  41     <p class="desc">Convert the given form (a list starting with a
  42     keyword) to an SQL query string at compile time, according to the
  43     rules described <a href="#syntax">here</a>.</p>
  44
  45     <p class="def">
  46       <span>function</span>
  47       <a name="sql-compile"></a>
  48       sql-compile (form)
  49       <br/>&#8594; string
  50     </p>
  51
  52     <p class="desc">This is the run-time variant of the <a
  53     href="#sql"><code>sql</code></a> macro. It converts the given list
  54     to an SQL query, with the same rules except that symbols in this
  55     list do not have to be quoted to be interpreted as
  56     identifiers.</p>
  57
  58     <p class="def">
  59       <span>function</span>
  60       <a name="sql-template"></a>
  61       sql-template (form)
  62     </p>
  63
  64     <p class="desc">In cases where you do need to build the query at
  65     run time, yet you do not want to re-compile it all the time, this
  66     function can be used to compile it once and store the result. It
  67     takes an S-SQL form, which may contain <code>$$</code> placeholder
  68     symbols, and returns a function that takes one argument for every
  69     <code>$$</code>. When called, this returned function produces an
  70     SQL string in which the placeholders have been replaced by the
  71     values of the arguments.</p>
  72
  73     <p class="def">
  74       <span>function</span>
  75       <a name="enable-s-sql-syntax"></a>
  76       enable-s-sql-syntax (&amp;optional (char #\Q))
  77     </p>
  78
  79     <p class="desc">Modifies the current readtable to add a #Q syntax
  80     that is read as <code>(sql ...)</code>. The character to use can
  81     be overridden by passing an argument.</p>
  82
  83     <p class="def">
  84       <span>function</span>
  85       <a name="sql-escape-string"></a>
  86       sql-escape-string (string)
  87       <br/>&#8594; string
  88     </p>
  89
  90     <p class="desc"><a
  91     href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS">Escapes</a>
  92     a string for inclusion in a PostgreSQL query.</p>
  93
  94     <p class="def">
  95       <span>method</span>
  96       <a name="sql-escape"></a>
  97       sql-escape (value)
  98       <br/>&#8594; string
  99     </p>
 100
 101     <p class="desc">A generalisation of <a
 102     href="#sql-escape-string"><code>sql-escape-string</code></a>.
 103     Looks at the type of the value passed, and properly writes it out
 104     it for inclusion in an SQL query. Symbols will be converted to SQL
 105     names.</p>
 106
 107     <p class="def">
 108       <span>variable</span>
 109       <a name="*standard-sql-strings*"></a>
 110       *standard-sql-strings*
 111     </p>
 112
 113     <p class="desc">Used to configure whether S-SQL will use standard
 114     SQL strings (just replace #\' with ''), or backslash-style
 115     escaping. Setting this to <code>NIL</code> is always safe, but
 116     when the server is configured to allow standard strings
 117     (compile-time parameter '<code>standard_conforming_strings</code>'
 118     is '<code>on</code>', which will become the default in future
 119     versions of PostgreSQL), the noise in queries can be reduced by
 120     setting this to <code>T</code>.</p>
 121
 122     <p class="def">
 123       <span>variable</span>
 124       <a name="*escape-sql-names-p*"></a>
 125       *escape-sql-names-p*
 126     </p>
 127
 128     <p class="desc">Determines whether double quotes are added around
 129     column, table, and function names in queries. May be
 130     <code>T</code>, in which case every name is escaped,
 131     <code>NIL</code>, in which case none is, or <code>:auto</code>,
 132     which causes only <a
 133     href="http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html">reserved
 134     words</a> to be escaped.. The default value is <code>:auto</code>.
 135     Be careful when binding this with <code>let</code> and such
 136     &#x2015; since a lot of SQL compilation tends to happen at
 137     compile-time, the result might not be what you expect.</p>
 138
 139     <p class="def">
 140       <span>function</span>
 141       <a name="sql-type-name"></a>
 142       sql-type-name (type)
 143       <br/>&#8594; string
 144     </p>
 145
 146     <p class="desc">Create the SQL equivalent of the given Lisp type,
 147     if one is known. See <a href="#types">types</a>.</p>
 148
 149     <p class="def">
 150       <span>function</span>
 151       <a name="to-sql-name"></a>
 152       to-sql-name (name &amp;optional (escape-p *escape-sql-names-p*))
 153       <br/>&#8594; string
 154     </p>
 155
 156     <p class="desc">Convert a symbol or string to a name that can be
 157     used as an SQL identifier by converting all non-alphanumeric
 158     characters to underscores. Also lowercases the name to make
 159     queries look a bit less hideous. When a second argument is given,
 160     this overrides the current value of <a
 161     href="#*escape-sql-names-p*"><code>*escape-sql-names-p*</code></a>.</p>
 162
 163     <p class="def">
 164       <span>function</span>
 165       <a name="from-sql-name"></a>
 166       from-sql-name (string)
 167       <br/>&#8594; keyword
 168     </p>
 169
 170     <p class="desc">Convert a string that represents an SQL identifier
 171     to a keyword by uppercasing it and converting the underscores to
 172     dashes.</p>
 173
 174     <p class="def">
 175       <span>macro</span>
 176       <a name="register-sql-operators"></a>
 177       register-sql-operators (arity &amp;rest names)
 178     </p>
 179
 180     <p class="desc">Define simple SQL operators. Arity is one of
 181     <code>:unary</code> (like '<code>not</code>'),
 182     <code>:unary-postfix</code> (the operator comes after the
 183     operand), <code>:n-ary</code> (like '<code>+</code>': the operator
 184     falls away when there is only one operand), <code>:2+-ary</code>
 185     (like '<code>=</code>', which is meaningless for one operand), or
 186     <code>:n-or-unary</code> (like '<code>-</code>', where the
 187     operator is kept in the unary case). After the arity may follow
 188     any number of operators, either just a keyword, in which case the
 189     downcased symbol name is used as the SQL operator, or a
 190     two-element list containing a keyword and a name string.</p>
 191
 192     <h2><a name="types"></a>SQL Types</h2>
 193
 194     <p>S-SQL knows the SQL equivalents to a number of Lisp types, and
 195     defines some extra types that can be used to denote other SQL
 196     types. The following table shows the correspondence:</p>
 197
 198     <table>
 199       <thead>
 200         <tr><th>Lisp type</th><th>SQL type</th></tr>
 201       </thead>
 202       <tbody>
 203         <tr><td>smallint</td><td>smallint</td></tr>
 204         <tr><td>integer</td><td>integer</td></tr>
 205         <tr><td>bigint</td><td>bigint</td></tr>
 206         <tr><td>(numeric X Y)</td><td>numeric(X, Y)</td></tr>
 207         <tr><td>float, real</td><td>real</td></tr>
 208         <tr><td>double-float, double-precision</td><td>double-precision</td></tr>
 209         <tr><td>string, text</td><td>text</td></tr>
 210         <tr><td>(string X)</td><td>char(X)</td></tr>
 211         <tr><td>(varchar X)</td><td>varchar(X)</td></tr>
 212         <tr><td>boolean</td><td>boolean</td></tr>
 213         <tr><td>bytea</td><td>bytea</td></tr>
 214         <tr><td><a href="simple-date.html#date">date</a></td><td>date</td></tr>
 215         <tr><td><a href="simple-date.html#timestamp">timestamp</a></td><td>timestamp</td></tr>
 216         <tr><td><a href="simple-date.html#interval">interval</a></td><td>interval</td></tr>
 217       </tbody>
 218     </table>
 219
 220     <p class="def">
 221       <span>type</span>
 222       <a name="db-null"></a>
 223       db-null
 224     </p>
 225
 226     <p class="desc">This is a type of which only the keyword
 227     <code>:null</code> is a member. It is used to represent NULL
 228     values from the database.</p>
 229
 230     <h2><a name="syntax"></a>SQL Syntax</h2>
 231
 232     <p>An S-SQL form is converted to a query through the following rules:</p>
 233
 234     <ul>
 235       <li>Lists starting with a keyword are operators. They are
 236       expanded as described below if they are known, otherwise they
 237       are expanded in the standard way: <code>operator(arguments,
 238       ...)</code></li>
 239       <li>Quoted symbols or keywords are interpreted as names of
 240       columns or tables, and converted to strings with <a
 241       href="#to-sql-name"><code>to-sql-name</code></a>.</li>
 242       <li>Anything else is evaluated and the resulting Lisp value is
 243       converted to its textual SQL representation (or an error is
 244       raised when there is no rule for converting objects of this
 245       type). Self-quoting atoms may be converted to strings at
 246       compile-time.</li>
 247     </ul>
 248
 249     <p>The following operators are defined:</p>
 250
 251     <p class="def"><span>sql-op</span> <a name="infix"></a>:+, :*, :%, :&amp;, :|, :||,
 252     :and, :or, :=, :/, :!=, :&lt;, :&gt;, :&lt;=, :&gt;=, :^, :union, :union-all,
 253     :intersect, :intersect-all, :except, :except-all (&amp;rest args)</p>
 254
 255     <p class="desc">These are expanded as infix operators. When
 256     meaningful, they allow more than two arguments. <code>:-</code>
 257     can also be used as a unary operator to negate a value. Note that
 258     the arguments to <code>:union</code>, <code>:union-all</code>,
 259     <code>:intersect</code>, and <code>:except</code> should be
 260     queries (<code>:select</code> forms).</p>
 261
 262     <p class="desc">Note that you'll have to escape pipe characters to
 263     enter them as keywords. S-SQL handles the empty keyword symbol
 264     (written <code>:||</code>) specially, and treats it
 265     like <code>:\|\|</code>, so that it can be written without
 266     escapes. With <code>:\|</code>, this doesn't work.</p>
 267
 268     <p class="def"><span>sql-op</span> <a name="unary"></a>:~, :not (arg)</p>
 269
 270     <p class="desc">Unary operators for bitwise and logical
 271     negation.</p>
 272
 273     <p class="def"><span>sql-op</span> <a name="regexp"></a>:~, :~*, :!~, :!~* (string pattern)</p>
 274
 275     <p class="desc">Regular expression matching operators. The
 276     exclamation mark means 'does not match', the asterisk makes the
 277     match case-insensitive.</p>
 278
 279     <p class="def"><span>sql-op</span> <a name="like"></a>:like, :ilike (string pattern)</p>
 280
 281     <p class="desc">Simple SQL string matching operators
 282     (<code>:ilike</code> is case-insensitive).</p>
 283
 284     <p class="def"><span>sql-op</span> <a name="match"></a>:@@</p>
 285
 286     <p class="desc">Fast Text Search match operator.</p>
 287
 288     <p class="def"><span>sql-op</span> <a name="desc"></a>:desc (column)</p>
 289
 290     <p class="desc">Used to invert the meaning of an operator in an <a
 291     href="#order-by"><code>:order-by</code></a> clause.</p>
 292
 293     <p class="def"><span>sql-op</span> <a name="nulls-first"></a>:nulls-first, :nulls-last (column)</p>
 294
 295     <p class="desc">Used to determine where <code>:null</code> values
 296     appear in an <a href="#order-by"><code>:order-by</code></a>
 297     clause.</p>
 298
 299     <p class="def"><span>sql-op</span> <a name="as"></a>:as (form name &amp;rest fields)</p>
 300
 301     <p class="desc">Assigns a name to a column or table in a <a
 302     href="#select"><code>:select</code></a> form. When fields are
 303     given, they are added after the name, in parentheses. For example,
 304     <code>(:as 'table1 't1 'foo 'bar)</code> becomes <code>table1 AS
 305     t1(foo, bar)</code>. When you need to specify types for the
 306     fields, you can do something like <code>(:as 'table2 't2 ('foo
 307     integer))</code>. Note that names are quoted, types are not (when
 308     using <code><a href="#sql-compile">sql-compile</a></code> or
 309     <code><a href="#sql-template">sql-template</a></code>, you can
 310     leave out the quotes entirely).</p>
 311
 312     <p class="def"><span>sql-op</span> <a name="exists"></a>:exists (query)</p>
 313
 314     <p class="desc">The EXISTS operator. Takes a query as an argument,
 315     and returns true or false depending on whether that query returns
 316     any rows.</p>
 317
 318     <p class="def"><span>sql-op</span> <a name="is-null"></a>:is-null (arg)</p>
 319
 320     <p class="desc">Test whether a value is null.</p>
 321
 322     <p class="def"><span>sql-op</span> <a name="not-null"></a>:not-null (arg)</p>
 323
 324     <p class="desc">Test whether a value is not null.</p>
 325
 326     <p class="def"><span>sql-op</span> <a name="in"></a>:in (value set)</p>
 327
 328     <p class="desc">Test whether a value is in a set of values.</p>
 329
 330     <p class="def"><span>sql-op</span> <a name="not-in"></a>:not-in (value set)</p>
 331
 332     <p class="desc">Inverse of the above.</p>
 333
 334     <p class="def"><span>sql-op</span> <a name="set"></a>:set (&amp;rest elements)</p>
 335
 336     <p class="desc">Denote a set of values. This one has two
 337     interfaces. When the elements are known at compile-time, they can
 338     be given as multiple arguments to the operator. When they are not,
 339     a single argument that evaluates to a list should be used.</p>
 340
 341     <p class="def"><span>sql-op</span> <a name="deref"></a>:[] (form start &amp;optional end)</p>
 342
 343     <p class="desc">Dereference an array value. If <code>end</code> is
 344     provided, extract a slice of the array.</p>
 345
 346     <p class="def"><span>sql-op</span> <a name="extract"></a>:extract (unit form)</p>
 347
 348     <p class="desc"><a
 349     href="http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT">Extract</a>
 350     a field from a date/time value. For example, <code>(:extract
 351     :month (:now))</code>.</p>
 352
 353     <p class="def"><span>sql-op</span> <a name="case"></a>:case (&amp;rest clauses)</p>
 354
 355     <p class="desc">A conditional expression. Clauses should take the
 356     form <code>(test value)</code>. If test is <code>:else</code>,
 357     an <code>ELSE</code> clause will be generated.</p>
 358
 359     <p class="def"><span>sql-op</span> <a name="between"></a>:between (n start end)</p>
 360
 361     <p class="desc">Test whether a value lies between two other
 362     values.</p>
 363
 364     <p class="def"><span>sql-op</span> <a name="between"></a>:between-symmetric (n start end)</p>
 365
 366     <p class="desc">Works
 367     like <a href="#between"><code>:between</code></a>, except that the
 368     start value is not required to be less than the end value.</p>
 369
 370     <p class="def"><span>sql-op</span> <a name="dot"></a>:dot (&amp;rest names)</p>
 371
 372     <p class="desc">Can be used to combine multiple names into a name
 373     of the form A.B to refer to a column in a table, or a table in a
 374     schema. Note that you can also just use a symbol with a dot in
 375     it.</p>
 376
 377     <p class="def"><span>sql-op</span> <a name="type"></a>:type (form type)</p>
 378
 379     <p class="desc">Add a type declaration to a value, as in in
 380     "4.3::real". The second argument is not evaluated normally, but
 381     put through <a
 382     href="#sql-type-name"><code>sql-type-name</code></a> to get a type
 383     identifier.</p>
 384
 385     <p class="def"><span>sql-op</span> <a name="raw"></a>:raw (string)</p>
 386
 387     <p class="desc">Insert a string as-is into the query. This can be
 388     useful for doing things that the syntax does not support, or to
 389     re-use parts of a query across multiple queries:</p>
 390
 391     <pre class="code desc">
 392 (let* ((test (sql (:and (:= 'foo 22) (:not-null 'bar))))
 393        (rows (query (:select '* :from 'baz :where (:raw test)))))
 394   (query (:delete-from 'baz :where (:raw test)))
 395   (do-stuff rows))</pre>
 396
 397     <p class="def"><span>sql-op</span> <a name="select"></a>:select (&amp;rest args)</p>
 398
 399     <p class="desc">Creates a select query. The arguments are split on
 400     the keywords found among them. The group of arguments immediately
 401     after <code>:select</code> is interpreted as the expressions that
 402     should be selected. After this, an optional <code>:distinct</code>
 403     may follow, which will cause the query to only select distinct
 404     rows, or alternatively <code>:distinct-on</code> followed by a
 405     group of row names. Next comes the optional keyword
 406     <code>:from</code>, followed by at least one table name and then
 407     any number of join statements. Join statements start with one of
 408     <code>:left-join</code>, <code>:right-join</code>,
 409     <code>:inner-join</code>, <code>:outer-join</code> or
 410     <code>:cross-join</code>, then a table name or subquery, then the
 411     keyword <code>:on</code> or <code>:using</code>, if applicable,
 412     and then a form. A join can be preceded by <code>:natural</code>
 413     (leaving off the
 414     <code>:on</code> clause) to use a natural join. After the joins an
 415     optional <code>:where</code> followed by a single form may occur.
 416     And finally <code>:group-by</code> and <code>:having</code> can
 417     optionally be specified. The first takes any number of arguments,
 418     and the second only one. An example:</p>
 419
 420     <pre class="code desc">
 421 (:select (:+ 'field-1 100) 'field-5
 422    :from (:as 'my-table 'x)
 423    :left-join 'your-table :on (:= 'x.field-2 'your-table.field-1)
 424    :where (:not-null 'a.field-3))</pre>
 425
 426     <p class="def"><span>sql-op</span> <a name="limit"></a>:limit (query amount &amp;optional offset)</p>
 427
 428     <p class="desc">In S-SQL limit is not part of the select operator,
 429     but an extra operator that is applied to a query (this works out
 430     better when limiting the union or intersection of multiple
 431     queries, same for sorting). It limits the number of results to the
 432     amount given as the second argument, and optionally offsets the
 433     result by the amount given as the third argument.</p>
 434
 435     <p class="def"><span>sql-op</span> <a name="order-by"></a>:order-by (query &amp;rest exprs)</p>
 436
 437     <p class="desc">Order the results of a query by the given
 438     expressions. See <a href="#desc"><code>:desc</code></a> for when
 439     you want to invert an ordering.</p>
 440
 441     <p class="def"><span>sql-op</span> <a name="over"></a>:over (form
 442     &amp;rest args)</p>
 443
 444     <p class="desc"><code>Over</code>, <code>partition-by</code> and <code>window</code> are so-called window
 445     functions. A window function performs a calculation across a set
 446     of table rows that are somehow related to the current row.</p>
 447
 448     <pre class="code desc">
 449 (query (:select 'salary (:over (:sum 'salary))
 450                 :from 'empsalary))</pre>
 451
 452     <p class="def"><span>sql-op</span> <a name="partition-by"></a>:partition-by
 453     (&amp;rest args)</p>
 454
 455     <p class="desc"><code>Args</code> is a list of one or more columns
 456     to partition by, optionally followed by an <code>:order-by</code>
 457     clause.</p>
 458
 459     <pre class="code desc">
 460 (query (:select 'depname 'subdepname 'empno 'salary
 461                 (:over (:avg 'salary)
 462                        (:partition-by 'depname 'subdepname))
 463                 :from 'empsalary))</pre>
 464
 465     <p class="desc">Note the use of <code>:order-by</code> without parens:</p>
 466
 467     <pre class="code desc">
 468 (query (:select 'depname 'empno 'salary
 469                 (:over (:rank)
 470                        (:partition-by 'depname :order-by (:desc 'salary)))
 471                 :from 'empsalary))
 472     </pre>
 473
 474     <p class="def"><span>sql-op</span> <a name="window"></a>:window (form)</p>
 475
 476     <pre class="code desc">
 477 (query (:select (:over (:sum 'salary) 'w)
 478               (:over (:avg 'salary) 'w)
 479               :from 'empsalary :window
 480               (:as 'w (:partition-by 'depname :order-by (:desc 'salary)))))</pre>
 481
 482     <p class="def"><span>sql-op</span> <a name="with"></a>:with
 483     (&amp;rest args)</p>
 484
 485     <p class="desc">With provides a way to write auxillary statements
 486     for use in a larger query, often referred to as Common Table
 487     Expressions or CTEs.</p>
 488
 489     <pre class="code desc">
 490 (query (:with (:as 'upd
 491                  (:parens
 492                   (:update 'employees :set 'sales-count (:+ 'sales-count 1)
 493                            :where (:= 'id
 494                                       (:select 'sales-person
 495                                                :from 'accounts
 496                                                :where (:= 'name "Acme Corporation")))
 497                            :returning '*)))
 498             (:insert-into 'employees-log
 499                           (:select '* 'current-timestamp :from
 500                  'upd))))</pre>
 501
 502     <p class="def"><span>sql-op</span> <a name="with-recursive"></a>:with-recursive
 503     (&amp;rest args)</p>
 504
 505     <p class="desc">Recursive modifier to a WITH statement, allowing
 506     the query to refer to its own output.</p>
 507
 508     <pre class="code desc">
 509 (query (:with-recursive
 510       (:as (:t1 'n)
 511            (:union-all (:values 1)
 512                        (:select (:+ 'n 1)
 513                                 :from 't1
 514                                 :where (:< 'n 100))))
 515       (:select (:sum 'n) :from 't1)))
 516
 517 (query (:with-recursive
 518       (:as (:included_parts 'sub-part 'part 'quantity)
 519            (:union-all
 520             (:select 'sub-part 'part 'quantity
 521                      :from 'parts
 522                      :where (:= 'part "our-product"))
 523             (:select 'p.sub-part 'p.part 'p.quantity
 524                      :from (:as 'included-parts 'pr)
 525                      (:as 'parts 'p)
 526                      :where (:= 'p.part 'pr.sub-part) )))
 527       (:select 'sub-part (:as (:sum 'quantity) 'total-quantity)
 528                :from 'included-parts
 529                :group-by 'sub-part)))
 530
 531 (query (:with-recursive
 532       (:as (:search-graph 'id 'link 'data 'depth)
 533            (:union-all (:select 'g.id 'g.link 'g.data 1
 534                                 :from (:as 'graph 'g))
 535                        (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
 536                                 :from (:as 'graph 'g) (:as 'search-graph 'sg)
 537                                 :where (:= 'g.id 'sg.link))))
 538       (:select '* :from 'search-graph)))
 539
 540 (query (:with-recursive
 541       (:as (:search-graph 'id 'link 'data'depth 'path 'cycle)
 542            (:union-all
 543             (:select 'g.id 'g.link 'g.data 1
 544                      (:[] 'g.f1 'g.f2) nil
 545                      :from (:as 'graph 'g))
 546             (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
 547                      (:|| 'path (:row 'g.f1 'g.f2))
 548                      (:= (:row 'g.f1 'g.f2)
 549                          (:any* 'path))
 550                      :from (:as 'graph 'g)
 551                      (:as 'search-graph 'sg)
 552                      :where (:and (:= 'g.id 'sg.link)
 553                                   (:not 'cycle)))))
 554       (:select '* :from 'search-graph)))</pre>
 555
 556     <p class="def"><span>sql-op</span> <a name="for-update"></a>:for-update (query &amp;key of nowait)</p>
 557
 558     <p class="desc">Locks the selected rows against concurrent updates. This will prevent the rows
 559     from being modified or deleted by other transactions until the current transaction ends. The :of
 560     keyword should be followed by one or more table names. If provided, PostgreSQL will lock
 561     these tables instead of the ones detected in the select statement. The :nowait keyword should be
 562     provided by itself (with no argument attached to it), after all the :of arguments . If :nowait
 563     is provided, PostgreSQL will throw an error if a table cannot be locked immediately, instead of
 564     pausing until it's possible.</p>
 565
 566     <pre class="desc code">
 567 (:for-update (:select :* :from 'foo 'bar 'baz) :of 'bar 'baz :nowait)</pre>
 568
 569     <p class="def"><span>sql-op</span> <a name="for-share"></a>:for-share (query &amp;key of nowait)</p>
 570
 571     <p class="desc">Similar to <a href="#for-update">:for-update</a>, except it acquires a shared
 572     lock on the table, allowing other transactions to perform :for-share selects on the locked
 573     tables.</p>
 574
 575     <p class="def"><span>sql-op</span> <a name="function"></a>:function (name (&amp;rest
 576     arg-types) return-type stability body)</p>
 577
 578     <p class="desc">Create a stored procedure. The argument and return
 579     types are interpreted as type names and not evaluated. Stability
 580     should be one of <code>:immutable</code>, <code>:stable</code>, or
 581     <code>:volatile</code> (see <a
 582     href="http://www.postgresql.org/docs/current/static/sql-createfunction.html">the
 583     PostgreSQL documentation</a>). For example, a function that gets foobars by
 584     id:</p>
 585
 586     <pre class="desc code">
 587 (:function 'get-foobar (integer) foobar :stable (:select '* :from 'foobar :where (:= 'id '$1)))</pre>
 588
 589     <p class="def"><span>sql-op</span> <a name="insert-into"></a>:insert-into (table &amp;rest rest)</p>
 590
 591     <p class="desc">Insert a row into a table. When the second
 592     argument is <code>:set</code>, the other arguments should be
 593     alternating field names and values, otherwise it should be a <a
 594     href="#select"><code>:select</code></a> form that will produce the
 595     values to be inserted. Example:</p>
 596
 597     <pre class="code desc">
 598 (:insert-into 'my-table :set 'field-1 42 'field-2 "foobar")</pre>
 599
 600     <p class="desc">It is possible to add <code>:returning</code>,
 601     followed by a list of field names or expressions, at the end of
 602     the <code>:insert-into</code> form. This will cause the query to
 603     return the values of these expressions as a single row.</p>
 604
 605     <p class="def"><span>sql-op</span> <a name="update"></a>:update (table &amp;rest rest)</p>
 606
 607     <p class="desc">Update values in a table. After the table name
 608     there should follow the keyword <code>:set</code> and any number
 609     of alternating field names and values, like
 610     for <a href="#insert-into"><code>:insert-into</code></a>. Next comes
 611     the optional keyword <code>:from</code>, followed by at least one table name
 612     and then any number of join statements, like for
 613     <a href="#select"><code>:select</code></a>. After the joins,
 614     an optional <code>:where</code> keyword followed by the condition,
 615     and <code>:returning</code> keyword followed by a list of field
 616     names or expressions indicating values to be returned as query
 617     result.</p>
 618
 619     <p class="def"><span>sql-op</span> <a name="delete-from"></a>:delete-from (table &amp;rest rest)</p>
 620
 621     <p class="desc">Delete rows from the named table. Can be given a
 622     <code>:where</code> argument followed by a condition, and a
 623     <code>:returning</code> argument, followed by one or more
 624     expressions that should be returned for every deleted row.</p>
 625
 626     <p class="def"><span>sql-op</span> <a name="create-table"></a>:create-table (name (&amp;rest columns) &amp;rest options)</p>
 627
 628     <p class="desc">Create a new table. After the table name a list of
 629     column definitions follows, which are lists that start with a
 630     name, followed by one or more of the following keyword
 631     arguments:</p>
 632
 633     <div class="desc"><dl>
 634       <dt><code>:type</code></dt>
 635       <dd>This one is required. It specifies the type of the column.
 636       Use a type like <code>(or db-null integer)</code> to specify a
 637       column that may have NULL values.</dd>
 638       <dt><code>:default</code></dt>
 639       <dd>Provides a default value for the field.</dd>
 640       <dt><code>:unique</code></dt>
 641       <dd>If this argument is non-nil, the values of the column must
 642       be unique.</dd>
 643       <dt><code>:primary-key</code></dt>
 644       <dd>When non-nil, the column is a primary key of the table.</dd>
 645       <dt><code>:check</code></dt>
 646       <dd>Adds a constraint to this column. The value provided for
 647       this argument must be an S-SQL expression that returns a boolean
 648       value. It can refer to other columns in the table if
 649       needed.</dd>
 650       <dt><code>:references</code></dt>
 651       <dd>Adds a foreign key constraint to this table. The argument
 652       provided must be a list of the form <code>(target &amp;optional
 653       on-delete on-update)</code>. When target is a symbol, it names
 654       the table to whose primary key this constraint refers. When it
 655       is a list, its first element is the table, and its second
 656       element the column within that table that the key refers to.
 657       <code>on-delete</code> and <code>on-update</code> can be used to
 658       specify the actions that must be taken when the row that this
 659       key refers to is deleted or changed. Allowed values are
 660       <code>:restrict</code>, <code>:set-null</code>,
 661       <code>:set-default</code>, <code>:cascade</code>, and
 662       <code>:no-action</code>.</dd>
 663     </dl></div>
 664
 665     <p class="desc"><a name="table-constraints"></a>After the list of
 666     columns, zero or more extra options (table constraints) can be
 667     specified. These are lists starting with one of the following
 668     keywords:</p>
 669
 670     <div class="desc"><dl>
 671       <dt><code>:check</code></dt>
 672       <dd>Adds a constraint to the table. Takes a single S-SQL
 673       expression that produces a boolean as its argument.</dd>
 674       <dt><code>:primary-key</code></dt>
 675       <dd>Specifies a primary key for the table. The arguments to this
 676       option are the names of the columns that this key consists
 677       of.</dd>
 678       <dt><code>:unique</code></dt>
 679       <dd>Adds a unique constraint to a group of columns. Again, the
 680       arguments are a list of symbols that indicate the relevant
 681       columns.</dd>
 682       <dt><code>:foreign-key</code></dt>
 683       <dd>Create a foreign key. The arguments should have the form
 684       <code>(columns target &amp;optional on-delete on-update)</code>,
 685       where <code>columns</code> is a list of columns that are used by
 686       this key, while the rest of the arguments have the same meaning
 687       as they have in the <code>:references</code> option for
 688       columns.</dd>
 689     </dl></div>
 690
 691     <p class="desc">Every list can start with <code>:constraint
 692     name</code> to create a specifically named constraint.</p>
 693
 694     <p class="desc">Note that, unlike most other operators,
 695     <code>:create-table</code> expects most of its arguments to be
 696     <em>unquoted</em> symbols. The exception to this is the value of
 697     <code>:check</code> constraints: These must be normal S-SQL
 698     expressions, which means that any column names they contain should
 699     be quoted. When programmatically generating table definitions,
 700     <code><a href="#sql-compile">sql-compile</a></code> is usually
 701     more practical than the <code><a href="#sql">sql</a></code>
 702     macro.</p>
 703
 704     <p class="desc">Here is an example of a <code>:create-table</code>
 705     form:</p>
 706
 707     <pre class="code desc">
 708 (:create-table enemy
 709   ((name :type string :primary-key t)
 710    (age :type integer)
 711    (address :type (or db-null string) :references (important-addresses :cascade :cascade))
 712    (fatal-weakness :type text :default "None")
 713    (identifying-color :type (string 20) :unique t))
 714   (:foreign-key (identifying-color) (colors name))
 715   (:constraint enemy-age-check :check (:> 'age 12))</pre>
 716
 717     <p class="def"><span>sql-op</apen><a name="alter-table"></a>:alter-table (name action &amp;rest args)</p>
 718
 719     <p class="desc">Alters named table. Currently changing a column's data
 720     type is not supported. The meaning of <code>args</code> depends on
 721     <code>action</code>:</p>
 722
 723     <div class="desc"><dl>
 724         <dt><code>:add-column</code></dt><dd>Adds column to table.
 725         <code>args</code> should be a column in the same form as for
 726         <a href="#create-table"><code>:create-table</code></a>.</dd>
 727         <dt><code>:drop-column</code></dt><dd>Drops a column from the
 728         table.</dd>
 729         <dt><code>:add-constraint</code></dt><dd>Adds a named constraint
 730         to the table.</dd>
 731         <dt><code>:drop-constraint</code><dd>Drops constraint.  First
 732         of <code>args</code> should name a constraint to be dropped;
 733         second, optional argument specifies behaviour regarding
 734         objects dependent on the constraint and it may
 735         equal <code>:cascade</code> or <code>:restrict</code>.</dd>
 736         <dt><code>:add</code></dt><dd>Adds an unnamed constraint to
 737         table.  <code>args</code> should be a constraint in the same
 738         form as for <a href="#table-constraints"><code>:create-table</code></a>.
 739         (This is for backwards-compatibility, you should use named constraints.)</dd>
 740     </dl></div>
 741
 742     <p class="desc">Here is an example using the table defined above:</p>
 743
 744     <pre class="code desc">
 745 (:alter-table enemy :drop-constraint enemy-age-check)
 746 (:alter-table enemy :add-constraint enemy-age-check :check (:> 'age 21))</pre>
 747
 748     <p class="def"><span>sql-op</span> <a name="drop-table"></a>:drop-table (name)</p>
 749
 750     <p class="desc">Drops the named table. You may optionally pass
 751     <code>:if-exists</code> before the name to suppress the error
 752     message.</p>
 753
 754     <p class="def"><span>sql-op</span> <a
 755     name="create-index"></a>:create-index (name &amp;rest args)</p>
 756
 757     <p class="desc">Create an index on a table. After the name of the
 758     index the keyword <code>:on</code> should follow, with the table
 759     name after it. Then the keyword <code>:fields</code>, followed by
 760     one or more column names. Optionally, a <code>:where</code> clause
 761     with a condition can be added at the end to make a partial
 762     index.</p>
 763
 764     <p class="def"><span>sql-op</span> <a
 765     name="create-unique-index"></a>:create-unique-index (name
 766     &amp;rest args)</p>
 767
 768     <p class="desc">Works like <a
 769     href="#create-index"><code>:create-index</code></a>, except that
 770     the index created is unique.</p>
 771
 772     <p class="def"><span>sql-op</span> <a
 773     name="drop-index"></a>:drop-index (name)</p>
 774
 775     <p class="desc">Drop an index. Takes an <code>:if-exists</code>
 776     argument like <a
 777     href="#drop-table"><code>:drop-table</code></a>.</p>
 778
 779     <p class="def"><span>sql-op</span> <a
 780     name="create-sequence"></a>:create-sequence (name &amp;key
 781     increment min-value max-value start cache cycle)</p>
 782
 783     <p class="desc">Create a sequence with the given name. The rest of
 784     the arguments control the way the sequence selects values.</p>
 785
 786     <p class="def"><span>sql-op</span> <a
 787     name="drop-sequence"></a>:drop-sequence (name)</p>
 788
 789     <p class="desc">Drop a sequence. You may pass
 790     <code>:if-exists</code> as an extra first argument.</p>
 791
 792     <p class="def"><span>sql-op</span> <a
 793     name="create-view"></a>:create-view (name query)</p>
 794
 795     <p class="desc">Create a view from an S-SQL-style query.</p>
 796
 797     <p class="def"><span>sql-op</span> <a
 798     name="drop-view"></a>:drop-view (name)</p>
 799
 800     <p class="desc">Drop a view. Takes optional
 801     <code>:if-exists</code> argument.</p>
 802
 803     <p class="def"><span>sql-op</span> <a
 804     name="set-constraints"></a>:set-constraints (state &amp;rest
 805     constraints)</p>
 806
 807     <p class="desc">Configure whether deferrable constraints should be
 808     checked when a statement is executed, or when the transaction
 809     containing that statement is completed. The provided state must be
 810     either <code>:immediate</code>, indicating the former, or
 811     <code>:deferred</code>, indicating the latter.  The constraints
 812     must be either the names of the constraints to be configured, or
 813     unspecified, indicating that all deferrable constraints should be
 814     thus configured.</p>
 815
 816     <p class="def"><span>sql-op</span> <a
 817     name="listen"></a>:listen (channel)</p>
 818
 819     <p class="desc">Tell the server to listen for notification events
 820       on channel <code>channel</code>, a string, on the current
 821       connection.</p>
 822
 823     <p class="def"><span>sql-op</span> <a
 824     name="unlisten"></a>:unlisten (channel)</p>
 825
 826     <p class="desc">Stop listening for events on <code>channel</code>.</p>
 827
 828     <p class="def"><span>sql-op</span> <a
 829     name="notify"></a>:notify (channel &optional payload)</p>
 830
 831     <p class="desc">Signal a notification event on
 832         channel <code>channel</code>, a string.  The
 833         optional <code>payload</code> string can be used to send
 834         additional event information to the listeners.</p>
 835
 836     <h2><a name="index"></a>Symbol-index</h2>
 837
 838     <ul class="symbol-index">
 839       <li><a href="#infix">:+</a></li>
 840       <li><a href="#unary">:-</a></li>
 841       <li><a href="#infix">:*</a></li>
 842       <li><a href="#infix">:&amp;</a></li>
 843       <li><a href="#infix">:|</a></li>
 844       <li><a href="#infix">:||</a></li>
 845       <li><a href="#infix">:=</a></li>
 846       <li><a href="#infix">:/</a></li>
 847       <li><a href="#infix">:!=</a></li>
 848       <li><a href="#infix">:&lt;</a></li>
 849       <li><a href="#infix">:&gt;</a></li>
 850       <li><a href="#infix">:&lt;=</a></li>
 851       <li><a href="#infix">:&gt;=</a></li>
 852       <li><a href="#infix">:^</a></li>
 853       <li><a href="#unary">:~</a></li>
 854       <li><a href="#regexp">:!~</a></li>
 855       <li><a href="#regexp">:!~*</a></li>
 856       <li><a href="#regexp">:~*</a></li>
 857       <li><a href="#match">:@@</a></li>
 858       <li><a href="#deref">:[]</a></li>
 859       <li><a href="#sql-template">$$</a></li>
 860       <li><a href="#infix">:and</a></li>
 861       <li><a href="#as">:as</a></li>
 862       <li><a href="#between">:between</a></li>
 863       <li><a href="#types">bytea</a></li>
 864       <li><a href="#types">bigint</a></li>
 865       <li><a href="#case">:case</a></li>
 866       <li><a href="#create-index">:create-index</a></li>
 867       <li><a href="#create-sequence">:create-sequence</a></li>
 868       <li><a href="#create-table">:create-table</a></li>
 869       <li><a href="#create-unique-index">:create-unique-index</a></li>
 870       <li><a href="#db-null">db-null</a></li>
 871       <li><a href="#delete-from">:delete-from</a></li>
 872       <li><a href="#desc">:desc</a></li>
 873       <li><a href="#dot">:dot</a></li>
 874       <li><a href="#types">double-precision</a></li>
 875       <li><a href="#drop-index">:drop-index</a></li>
 876       <li><a href="#drop-sequence">:drop-sequence</a></li>
 877       <li><a href="#drop-table">:drop-table</a></li>
 878       <li><a href="#drop-view">:drop-view</a></li>
 879       <li><a href="#set-constraints">:set-constraints</a></li>
 880       <li><a href="#enable-s-sql-syntax">enable-s-sql-syntax</a></li>
 881       <li><a href="#*escape-sql-names-p*">*escape-sql-names-p*</a></li>
 882       <li><a href="#infix">:except</a></li>
 883       <li><a href="#exists">:exists</a></li>
 884       <li><a href="#extract">:extract</a></li>
 885       <li><a href="#from-sql-name">from-sql-name</a></li>
 886       <li><a href="#function">:function</a></li>
 887       <li><a href="#like">:ilike</a></li>
 888       <li><a href="#in">:in</a></li>
 889       <li><a href="#insert-into">:insert-into</a></li>
 890       <li><a href="#infix">:intersect</a></li>
 891       <li><a href="#is-null">:is-null</a></li>
 892       <li><a href="#like">:like</a></li>
 893       <li><a href="#limit">:limit</a></li>
 894       <li><a href="#listen">:listen</a></li>
 895       <li><a href="#not">:not</a></li>
 896       <li><a href="#not-in">:not-in</a></li>
 897       <li><a href="#not-null">:not-null</a></li>
 898       <li><a href="#notify">:notify</a></li>
 899       <li><a href="#types">numeric</a></li>
 900       <li><a href="#nulls-first">:nulls-first</a></li>
 901       <li><a href="#nulls-first">:nulls-last</a></li>
 902       <li><a href="#infix">:or</a></li>
 903       <li><a href="#order-by">:order-by</a></li>
 904       <li><a href="#raw">:raw</a></li>
 905       <li><a href="#types">real</a></li>
 906       <li><a href="#register-sql-operators">:register-sql-operators</a></li>
 907       <li><a href="#*standard-sql-strings*">*standard-sql-strings*</a></li>
 908       <li><a href="#select">:select</a></li>
 909       <li><a href="#set">:set</a></li>
 910       <li><a href="#types">smallint</a></li>
 911       <li><a href="#sql">sql</a></li>
 912       <li><a href="#sql-compile">sql-compile</a></li>
 913       <li><a href="#sql-escape">sql-escape</a></li>
 914       <li><a href="#sql-escape-string">sql-escape-string</a></li>
 915       <li><a href="#sql-template">sql-template</a></li>
 916       <li><a href="#sql-type-name">sql-type-name</a></li>
 917       <li><a href="#types">text</a></li>
 918       <li><a href="#to-sql-name">to-sql-name</a></li>
 919       <li><a href="#type">:type</a></li>
 920       <li><a href="#infix">:union</a></li>
 921       <li><a href="#infix">:union-all</a></li>
 922       <li><a href="#unlisten">:unlisten</a></li>
 923       <li><a href="#update">:update</a></li>
 924       <li><a href="#types">varchar</a></li>
 925     </ul>
 926
 927   </body>
 928 </html>