doc/ref-fdml.xml

   1 <?xml version='1.0' ?>
   2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
   3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
   4 <!ENTITY % myents SYSTEM "entities.inc">
   5 %myents;
   6 ]>
   7
   8 <!-- Functional Data Manipulation Language -->
   9 <reference id="ref-fdml">
  10   <title>Functional Data Manipulation Language (FDML)</title>
  11   <partintro>
  12     <para>
  13       The functional data manipulation interface provided by &clsql;
  14       includes functions for inserting, updating and deleting records
  15       in existing database tables and executing SQL queries and
  16       statements with the results of queries returned as Lisp types.
  17       SQL statements expressed as strings may be executed with the
  18       <link linkend="query"><function>query</function></link> and
  19       <link
  20       linkend="execute-command"><function>execute-command</function></link>
  21       functions. The <link
  22       linkend="select"><function>select</function></link> function, on
  23       the other hand, allows for the construction of queries in Lisp
  24       using the symbolic SQL syntax. Finally, iterative manipulation
  25       of query results is supported by <link
  26       linkend="do-query"><function>do-query</function></link>, <link
  27       linkend="map-query"><function>map-query</function></link> and an
  28       extended clause for the <link
  29       linkend="loop-tuples"><function>loop</function></link> macro.
  30     </para>
  31   </partintro>
  32
  33   <!-- Caching table queries -->
  34
  35   <refentry id="cache-table-queries-default">
  36     <refmeta>
  37       <refentrytitle>*CACHE-TABLE-QUERIES-DEFAULT*</refentrytitle>
  38     </refmeta>
  39     <refnamediv>
  40       <refname>*CACHE-TABLE-QUERIES-DEFAULT*</refname>
  41       <refpurpose>Specifies the default behaviour for caching of
  42       attribute types.</refpurpose>
  43       <refclass>Variable</refclass>
  44     </refnamediv>
  45     <refsect1>
  46       <title>Value Type</title>
  47       <para>
  48         A valid argument to the <parameter>action</parameter>
  49         parameter of <function>cache-table-queries</function>,
  50         i.e. one of
  51         <simplelist type="inline">
  52         <member>&t;</member>
  53         <member>&nil;</member>
  54         <member><symbol>:flush</symbol></member>
  55         </simplelist>.
  56       </para>
  57     </refsect1>
  58     <refsect1>
  59       <title>Initial Value</title>
  60       <para><symbol>nil</symbol></para>
  61     </refsect1>
  62     <refsect1>
  63       <title>Description</title>
  64       <para>
  65         Specifies the default behaivour for caching of attribute
  66         types. Meaningful values are &t;, &nil; and
  67         <symbol>:flush</symbol> as described for the
  68         <parameter>action</parameter> argument to
  69         <function>cache-table-queries</function>.
  70       </para>
  71     </refsect1>
  72     <refsect1>
  73       <title>Examples</title>
  74       <para>None.</para>
  75     </refsect1>
  76     <refsect1>
  77       <title>Affected By</title>
  78       <para>None.</para>
  79     </refsect1>
  80     <refsect1>
  81       <title>See Also</title>
  82       <simplelist>
  83         <member><link linkend="cache-table-queries"><function>cache-table-queries</function></link></member>
  84       </simplelist>
  85     </refsect1>
  86     <refsect1>
  87       <title>Notes</title>
  88       <para>None.</para>
  89     </refsect1>
  90   </refentry>
  91
  92   <refentry id="cache-table-queries">
  93     <refmeta>
  94       <refentrytitle>CACHE-TABLE-QUERIES</refentrytitle>
  95     </refmeta>
  96     <refnamediv>
  97       <refname>CACHE-TABLE-QUERIES</refname>
  98       <refpurpose>Control the caching of table attribute types.</refpurpose>
  99       <refclass>Function</refclass>
 100     </refnamediv>
 101     <refsect1>
 102       <title>Syntax</title>
 103       <synopsis>
 104       <function>cache-table-queries</function> <replaceable>table</replaceable> &amp;key <replaceable>action</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 105     </refsect1>
 106     <refsect1>
 107       <title>Arguments and Values</title>
 108       <variablelist>
 109         <varlistentry>
 110           <term><parameter>table</parameter></term>
 111           <listitem>
 112             <para>
 113               A string representing a database table, &t; or
 114               <symbol>:default</symbol>.
 115             </para>
 116           </listitem>
 117         </varlistentry>
 118         <varlistentry>
 119           <term><parameter>action</parameter></term>
 120           <listitem>
 121             <para>
 122               &t;, &nil; or <symbol>:flush</symbol>.
 123             </para>
 124           </listitem>
 125         </varlistentry>
 126         <varlistentry>
 127           <term><parameter>database</parameter></term>
 128           <listitem>
 129             <para>A
 130             <glossterm linkend="gloss-database-object">database
 131             object</glossterm>. This will default to the value
 132             of <symbol>*default-database*</symbol>.</para>
 133           </listitem>
 134       </varlistentry>
 135       </variablelist>
 136     </refsect1>
 137     <refsect1>
 138       <title>Description</title>
 139       <para>Controls the caching of attribute type information on the
 140       table specified by <parameter>table</parameter> in
 141       <parameter>database</parameter> which defaults to
 142       <symbol>*default-database*</symbol>. <parameter>action</parameter>
 143       specifies the caching behaviour to adopt. If its value is &t;
 144       then attribute type information is cached whereas if its value
 145       is &nil; then attribute type information is not cached. If
 146       <parameter>action</parameter> is <symbol>:flush</symbol> then
 147       all existing type information in the cache for
 148       <parameter>table</parameter> is removed, but caching is still
 149       enabled. <parameter>table</parameter> may be a string
 150       representing a table for which the caching action is to be taken
 151       while the caching action is applied to all tables if
 152       <parameter>table</parameter> is &t;. Alternatively, when
 153       <parameter>table</parameter> is <symbol>:default</symbol>, the
 154       default caching action specified by
 155       <symbol>*cache-table-queries-default*</symbol> is applied to all
 156       tables for which a caching action has not been explicitly set.
 157       </para>
 158     </refsect1>
 159     <refsect1>
 160       <title>Examples</title>
 161       <screen>
 162 (setf *cache-table-queries-default* t)
 163 => T
 164 (create-table [foo]
 165               '(([id] integer)
 166                 ([height] float)
 167                 ([name] (string 24))
 168                 ([comments] varchar)))
 169 =>
 170 (cache-table-queries "foo")
 171 =>
 172 (list-attribute-types "foo")
 173 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 24 NIL 1)
 174     ("comments" :VARCHAR 255 NIL 1))
 175 (drop-table "foo")
 176 =>
 177 (create-table [foo]
 178               '(([id] integer)
 179                 ([height] float)
 180                 ([name] (string 36))
 181                 ([comments] (string 100))))
 182 =>
 183 (cache-table-queries "foo" :action :flush)
 184 =>
 185 (list-attribute-types "foo")
 186 => (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 36 NIL 1)
 187     ("comments" :BPCHAR 100 NIL 1))
 188       </screen>
 189     </refsect1>
 190     <refsect1>
 191       <title>Side Effects</title>
 192        <para>
 193         The internal attribute cache for
 194         <parameter>database</parameter> is modified.
 195        </para>
 196      </refsect1>
 197     <refsect1>
 198       <title>Affected by</title>
 199       <simplelist>
 200         <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
 201       </simplelist>
 202     </refsect1>
 203     <refsect1>
 204       <title>Exceptional Situations</title>
 205       <para>
 206         None.
 207       </para>
 208     </refsect1>
 209     <refsect1>
 210       <title>See Also</title>
 211       <simplelist>
 212         <member><link linkend="cache-table-queries-default"><symbol>*cache-table-queries-default*</symbol></link></member>
 213       </simplelist>
 214     </refsect1>
 215     <refsect1>
 216       <title>Notes</title>
 217       <para>
 218         None.
 219       </para>
 220     </refsect1>
 221   </refentry>
 222
 223   <refentry id="insert-records">
 224     <refmeta>
 225       <refentrytitle>INSERT-RECORDS</refentrytitle>
 226     </refmeta>
 227     <refnamediv>
 228       <refname>INSERT-RECORDS</refname>
 229       <refpurpose>Insert tuples of data into a database table.</refpurpose>
 230       <refclass>Function</refclass>
 231     </refnamediv>
 232     <refsect1>
 233       <title>Syntax</title>
 234       <synopsis>
 235       <function>insert-records</function> &amp;key <replaceable>into</replaceable> <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>query</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 236     </refsect1>
 237     <refsect1>
 238       <title>Arguments and Values</title>
 239       <variablelist>
 240         <varlistentry>
 241           <term><parameter>into</parameter></term>
 242           <listitem>
 243             <para>
 244               A string, symbol or symbolic SQL expression representing
 245               the name of a table existing in
 246               <parameter>database</parameter>.
 247             </para>
 248           </listitem>
 249         </varlistentry>
 250         <varlistentry>
 251           <term><parameter>attributes</parameter></term>
 252           <listitem>
 253             <para>
 254               A list of attribute identifiers or &nil;.
 255             </para>
 256           </listitem>
 257         </varlistentry>
 258         <varlistentry>
 259           <term><parameter>values</parameter></term>
 260           <listitem>
 261             <para>
 262               A list of attribute values or &nil;.
 263             </para>
 264           </listitem>
 265         </varlistentry>
 266         <varlistentry>
 267           <term><parameter>av-pairs</parameter></term>
 268           <listitem>
 269             <para>
 270               A list of attribute identifier/value pairs or &nil;.
 271             </para>
 272           </listitem>
 273         </varlistentry>
 274         <varlistentry>
 275           <term><parameter>query</parameter></term>
 276           <listitem>
 277             <para>
 278               A query expression or &nil;.
 279             </para>
 280           </listitem>
 281         </varlistentry>
 282         <varlistentry>
 283           <term><parameter>database</parameter></term>
 284           <listitem>
 285             <para>A
 286             <glossterm linkend="gloss-database-object">database
 287             object</glossterm>. This will default to the value
 288             of <symbol>*default-database*</symbol>.</para>
 289           </listitem>
 290       </varlistentry>
 291       </variablelist>
 292     </refsect1>
 293     <refsect1>
 294       <title>Description</title>
 295       <para>
 296         Inserts records into the table specified by
 297         <parameter>into</parameter> in <parameter>database</parameter>
 298         which defaults to <symbol>*default-database*</symbol>.
 299       </para>
 300       <para>
 301         There are five ways of specifying the values inserted into
 302         each row. In the first <parameter>values</parameter> contains
 303         a list of values to insert and
 304         <parameter>attributes</parameter>,
 305         <parameter>av-pairs</parameter> and
 306         <parameter>query</parameter> are &nil;. This can be used when
 307         values are supplied for all attributes in
 308         <parameter>into</parameter>. In the second,
 309         <parameter>attributes</parameter> is a list of column names,
 310         <parameter>values</parameter> is a corresponding list of
 311         values and <parameter>av-pairs</parameter> and
 312         <parameter>query</parameter> are &nil;. In the third,
 313         <parameter>attributes</parameter>,
 314         <parameter>values</parameter> and <parameter>query</parameter>
 315         are &nil; and <parameter>av-pairs</parameter> is an alist of
 316         (attribute value) pairs. In the fourth,
 317         <parameter>values</parameter>, <parameter>av-pairs</parameter>
 318         and <parameter>attributes</parameter> are &nil; and
 319         <parameter>query</parameter> is a symbolic SQL query
 320         expression in which the selected columns also exist in
 321         <parameter>into</parameter>. In the fifth method,
 322         <parameter>values</parameter> and
 323         <parameter>av-pairs</parameter> are nil and
 324         <parameter>attributes</parameter> is a list of column names
 325         and <parameter>query</parameter> is a symbolic SQL query
 326         expression which returns values for the specified columns.
 327       </para>
 328     </refsect1>
 329     <refsect1>
 330       <title>Examples</title>
 331       <screen>
 332 (select [first-name] [last-name] [email]
 333         :from [employee]
 334         :where [= [emplid] 11]
 335         :field-names nil)
 336 => NIL
 337 (insert-records :into [employee]
 338                 :attributes '(emplid groupid first_name last_name email
 339                               ecompanyid managerid)
 340                 :values '(11 1 "Yuri" "Gagarin" "gagarin@soviet.org"
 341                           1 1))
 342 =>
 343 (select [first-name] [last-name] [email]
 344         :from [employee]
 345         :where [= [emplid] 11]
 346         :field-names nil)
 347 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 348       </screen>
 349     </refsect1>
 350     <refsect1>
 351       <title>Side Effects</title>
 352       <para>
 353         Modifications are made to the underlying database.
 354       </para>
 355     </refsect1>
 356     <refsect1>
 357       <title>Affected by</title>
 358       <para>
 359         None.
 360       </para>
 361     </refsect1>
 362     <refsect1>
 363       <title>Exceptional Situations</title>
 364       <para>
 365         An error of type <symbol>sql-database-data-error</symbol> is
 366         signalled if <parameter>table</parameter> is not an existing
 367         table in <parameter>database</parameter> or if the specified
 368         attributes are not found.
 369       </para>
 370     </refsect1>
 371     <refsect1>
 372       <title>See Also</title>
 373       <simplelist>
 374         <member><link linkend="update-records"><function>update-records</function></link></member>
 375         <member><link linkend="delete-records"><function>delete-records</function></link></member>
 376       </simplelist>
 377     </refsect1>
 378     <refsect1>
 379       <title>Notes</title>
 380       <para>
 381         None.
 382       </para>
 383     </refsect1>
 384   </refentry>
 385
 386   <refentry id="update-records">
 387     <refmeta>
 388       <refentrytitle>UPDATE-RECORDS</refentrytitle>
 389     </refmeta>
 390     <refnamediv>
 391       <refname>UPDATE-RECORDS</refname>
 392       <refpurpose>Updates the values of existing records.</refpurpose>
 393       <refclass>Function</refclass>
 394     </refnamediv>
 395     <refsect1>
 396       <title>Syntax</title>
 397       <synopsis>
 398       <function>update-records</function> <replaceable>table</replaceable> &amp;key  <replaceable>attributes</replaceable> <replaceable>values</replaceable> <replaceable>av-pairs</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 399     </refsect1>
 400     <refsect1>
 401       <title>Arguments and Values</title>
 402       <variablelist>
 403         <varlistentry>
 404           <term><parameter>table</parameter></term>
 405           <listitem>
 406             <para>
 407               A string, symbol or symbolic SQL expression representing
 408               the name of a table existing in
 409               <parameter>database</parameter>.
 410             </para>
 411           </listitem>
 412         </varlistentry>
 413         <varlistentry>
 414           <term><parameter>attributes</parameter></term>
 415           <listitem>
 416             <para>
 417               A list of attribute identifiers or &nil;.
 418             </para>
 419           </listitem>
 420         </varlistentry>
 421         <varlistentry>
 422           <term><parameter>values</parameter></term>
 423           <listitem>
 424             <para>
 425               A list of attribute values or &nil;.
 426             </para>
 427           </listitem>
 428         </varlistentry>
 429         <varlistentry>
 430           <term><parameter>av-pairs</parameter></term>
 431           <listitem>
 432             <para>
 433               A list of attribute identifier/value pairs or &nil;.
 434             </para>
 435           </listitem>
 436         </varlistentry>
 437         <varlistentry>
 438           <term><parameter>where</parameter></term>
 439           <listitem>
 440             <para>
 441               A symbolic SQL expression.
 442             </para>
 443           </listitem>
 444         </varlistentry>
 445         <varlistentry>
 446           <term><parameter>database</parameter></term>
 447           <listitem>
 448             <para>A
 449             <glossterm linkend="gloss-database-object">database
 450             object</glossterm>. This will default to the value
 451             of <symbol>*default-database*</symbol>.</para>
 452           </listitem>
 453       </varlistentry>
 454       </variablelist>
 455     </refsect1>
 456     <refsect1>
 457       <title>Description</title>
 458       <para>
 459         Updates the attribute values of existing records satsifying
 460         the SQL expression <parameter>where</parameter> in the table
 461         specified by <parameter>table</parameter> in
 462         <parameter>database</parameter> which defaults to
 463         <symbol>*default-database*</symbol>.
 464       </para>
 465       <para>
 466         There are three ways of specifying the values to update for
 467         each row. In the first, <parameter>values</parameter> contains
 468         a list of values to use in the update and
 469         <parameter>attributes</parameter> and
 470         <parameter>av-pairs</parameter> are &nil;. This can be used
 471         when values are supplied for all attributes in
 472         <parameter>table</parameter>. In the second,
 473         <parameter>attributes</parameter> is a list of column names,
 474         <parameter>values</parameter> is a corresponding list of
 475         values and <parameter>av-pairs</parameter> is &nil;. In the
 476         third, <parameter>attributes</parameter> and
 477         <parameter>values</parameter> are &nil; and
 478         <parameter>av-pairs</parameter> is an alist of (attribute
 479         value) pairs.
 480       </para>
 481     </refsect1>
 482     <refsect1>
 483       <title>Examples</title>
 484       <screen>
 485 (select [first-name] [last-name] [email]
 486         :from [employee]
 487         :where [= [emplid] 1]
 488         :field-names nil)
 489 => (("Vladimir" "Lenin" "lenin@soviet.org"))
 490 (update-records [employee]
 491                 :av-pairs'((first_name "Yuri")
 492                            (last_name "Gagarin")
 493                            (email "gagarin@soviet.org"))
 494                 :where [= [emplid] 1])
 495 =>
 496 (select [first-name] [last-name] [email]
 497         :from [employee]
 498         :where [= [emplid] 1]
 499         :field-names nil)
 500 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 501       </screen>
 502     </refsect1>
 503     <refsect1>
 504       <title>Side Effects</title>
 505       <para>
 506         Modifications are made to the underlying database.
 507       </para>
 508     </refsect1>
 509     <refsect1>
 510       <title>Affected by</title>
 511       <para>
 512         None.
 513       </para>
 514     </refsect1>
 515     <refsect1>
 516       <title>Exceptional Situations</title>
 517       <para>
 518         An error of type <symbol>sql-database-data-error</symbol> is
 519         signalled if <parameter>table</parameter> is not an existing
 520         table in <parameter>database</parameter>, if the specified
 521         attributes are not found or if the SQL statement resulting
 522         from the symbolic expression <parameter>where</parameter> does
 523         not return a Boolean value.
 524       </para>
 525       <para>If the execution of the SQL query leads to any errors, an
 526       error of type <errortype>sql-database-error</errortype> is
 527       signalled.</para>
 528     </refsect1>
 529     <refsect1>
 530       <title>See Also</title>
 531       <para>
 532         <simplelist>
 533           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 534           <member><link linkend="delete-records"><function>delete-records</function></link></member>
 535         </simplelist>
 536       </para>
 537     </refsect1>
 538     <refsect1>
 539       <title>Notes</title>
 540       <para>
 541         None.
 542       </para>
 543     </refsect1>
 544   </refentry>
 545
 546   <refentry id="delete-records">
 547     <refmeta>
 548       <refentrytitle>DELETE-RECORDS</refentrytitle>
 549     </refmeta>
 550     <refnamediv>
 551       <refname>DELETE-RECORDS</refname>
 552       <refpurpose>Delete records from a database table.</refpurpose>
 553       <refclass>Function</refclass>
 554     </refnamediv>
 555     <refsect1>
 556       <title>Syntax</title>
 557       <synopsis>
 558       <function>delete-records</function> &amp;key <replaceable>from</replaceable> <replaceable>where</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 559     </refsect1>
 560     <refsect1>
 561       <title>Arguments and Values</title>
 562       <variablelist>
 563         <varlistentry>
 564           <term><parameter>from</parameter></term>
 565           <listitem>
 566             <para>
 567               A string, symbol or symbolic SQL expression representing
 568               the name of a table existing in
 569               <parameter>database</parameter>.
 570             </para>
 571           </listitem>
 572         </varlistentry>
 573                 <varlistentry>
 574           <term><parameter>where</parameter></term>
 575           <listitem>
 576             <para>
 577               A symbolic SQL expression.
 578             </para>
 579           </listitem>
 580         </varlistentry>
 581         <varlistentry>
 582           <term><parameter>database</parameter></term>
 583           <listitem>
 584             <para>A
 585             <glossterm linkend="gloss-database-object">database
 586             object</glossterm>. This will default to the value
 587             of <symbol>*default-database*</symbol>.</para>
 588           </listitem>
 589         </varlistentry>
 590       </variablelist>
 591     </refsect1>
 592     <refsect1>
 593       <title>Description</title>
 594       <para>Deletes records satisfying the SQL expression
 595       <parameter>where</parameter> from the table specified by
 596       <parameter>from</parameter> in <parameter>database</parameter>
 597       specifies a database which defaults to
 598       <symbol>*default-database*</symbol>.
 599       </para>
 600     </refsect1>
 601     <refsect1>
 602       <title>Examples</title>
 603       <screen>
 604 (select [first-name] [last-name] [email]
 605         :from [employee]
 606         :where [= [emplid] 11]
 607         :field-names nil)
 608 => (("Yuri" "Gagarin" "gagarin@soviet.org"))
 609 (delete-records :from [employee] :where [= [emplid] 11])
 610 =>
 611 (select [first-name] [last-name] [email]
 612         :from [employee]
 613         :where [= [emplid] 11]
 614         :field-names nil)
 615 => NIL
 616       </screen>
 617     </refsect1>
 618     <refsect1>
 619       <title>Side Effects</title>
 620       <para>
 621         Modifications are made to the underlying database.
 622       </para>
 623     </refsect1>
 624     <refsect1>
 625       <title>Affected by</title>
 626       <para>
 627         None.
 628       </para>
 629     </refsect1>
 630     <refsect1>
 631       <title>Exceptional Situations</title>
 632       <para>
 633         An error of type <symbol>sql-database-data-error</symbol> is
 634         signalled if <parameter>from</parameter> is not an existing
 635         table in <parameter>database</parameter> or if the SQL
 636         statement resulting from the symbolic expression
 637         <parameter>where</parameter> does not return a Boolean value.
 638       </para>
 639     </refsect1>
 640     <refsect1>
 641       <title>See Also</title>
 642       <para>
 643         <simplelist>
 644           <member><link linkend="insert-records"><function>insert-records</function></link></member>
 645           <member><link linkend="update-records"><function>update-records</function></link></member>
 646         </simplelist>
 647       </para>
 648     </refsect1>
 649     <refsect1>
 650       <title>Notes</title>
 651       <para>
 652         None.
 653       </para>
 654     </refsect1>
 655   </refentry>
 656
 657
 658   <!-- executing SQL commands and queries -->
 659
 660   <refentry id="execute-command">
 661     <refmeta>
 662       <refentrytitle>EXECUTE-COMMAND</refentrytitle>
 663     </refmeta>
 664     <refnamediv>
 665       <refname>EXECUTE-COMMAND</refname>
 666       <refpurpose>Execute an SQL command which returns no values.</refpurpose>
 667       <refclass>Generic Function</refclass>
 668     </refnamediv>
 669     <refsect1>
 670       <title>Syntax</title>
 671       <synopsis>
 672       <function>execute-command</function> <replaceable>sql-expression</replaceable> &amp;key <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 673     </refsect1>
 674     <refsect1>
 675       <title>Arguments and Values</title>
 676       <variablelist>
 677         <varlistentry>
 678           <term><parameter>sql-expression</parameter></term>
 679           <listitem>
 680             <para>An <glossterm linkend="gloss-sql-expression">sql
 681             expression</glossterm> that represents an SQL
 682             statement which will return no values.</para>
 683           </listitem>
 684         </varlistentry>
 685         <varlistentry>
 686           <term><parameter>database</parameter></term>
 687           <listitem>
 688             <para>A
 689             <glossterm linkend="gloss-database-object">database
 690             object</glossterm>. This will default to the value
 691             of <symbol>*default-database*</symbol>.</para>
 692           </listitem>
 693       </varlistentry></variablelist>
 694     </refsect1>
 695     <refsect1>
 696       <title>Description</title>
 697       <para>Executes the SQL command
 698       <parameter>sql-expression</parameter>, which may be a symbolic
 699       SQL expression or a string representing any SQL statement apart
 700       from a query, on the supplied <parameter>database</parameter>
 701       which defaults to <symbol>*default-database*</symbol>.
 702       </para>
 703     </refsect1>
 704     <refsect1>
 705       <title>Examples</title>
 706       <screen>
 707         (execute-command "create table eventlog (time char(30),event char(70))")
 708         =>
 709
 710         (execute-command "create table eventlog (time char(30),event char(70))")
 711         >>
 712         >> While accessing database #&lt;CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}>
 713         >>   with expression "create table eventlog (time char(30),event char(70))":
 714         >>   Error NIL: ERROR:  amcreate: eventlog relation already exists
 715         >>   has occurred.
 716         >>
 717         >> Restarts:
 718         >>   0: [ABORT] Return to Top-Level.
 719         >>
 720         >> Debug  (type H for help)
 721         >>
 722         >> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))|
 723         >>  #&lt;unused-arg>
 724         >>  #&lt;unused-arg>
 725         >>  #&lt;unavailable-arg>
 726         >>  #&lt;unavailable-arg>)
 727         >> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...)
 728         >> 0] 0
 729
 730         (execute-command "drop table eventlog")
 731         =>
 732       </screen>
 733     </refsect1>
 734     <refsect1>
 735       <title>Side Effects</title>
 736       <para>Whatever effects the execution of the SQL statement has
 737       on the underlying database, if any.</para>
 738     </refsect1>
 739     <refsect1>
 740       <title>Affected by</title>
 741       <para>None.</para>
 742     </refsect1>
 743     <refsect1>
 744       <title>Exceptional Situations</title>
 745       <para>If the execution of the SQL statement leads to any errors,
 746       an error of type <errortype>sql-database-error</errortype> is
 747       signalled.</para>
 748     </refsect1>
 749     <refsect1>
 750       <title>See Also</title>
 751       <para>
 752         <simplelist>
 753           <member><link linkend="query"><function>query</function></link></member>
 754         </simplelist>
 755       </para>
 756     </refsect1>
 757     <refsect1>
 758       <title>Notes</title>
 759       <para>None.</para>
 760     </refsect1>
 761   </refentry>
 762
 763
 764   <refentry id="query">
 765     <refmeta>
 766       <refentrytitle>QUERY</refentrytitle>
 767     </refmeta>
 768     <refnamediv>
 769       <refname>QUERY</refname>
 770       <refpurpose>Execute an SQL query and return the tuples as a
 771       list.</refpurpose>
 772       <refclass>Generic Function</refclass>
 773     </refnamediv>
 774     <refsect1>
 775       <title>Syntax</title>
 776       <synopsis>
 777       <function>query</function> <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> <replaceable>flatp</replaceable> <replaceable>field-names</replaceable> => <returnvalue>result</returnvalue></synopsis>
 778     </refsect1>
 779     <refsect1>
 780       <title>Arguments and Values</title>
 781       <variablelist>
 782         <varlistentry>
 783           <term><parameter>query-expression</parameter></term>
 784           <listitem>
 785             <para>An <glossterm linkend="gloss-sql-expression">sql
 786             expression</glossterm> that represents an SQL
 787             query which is expected to return a (possibly empty)
 788             result set.</para>
 789           </listitem>
 790         </varlistentry>
 791         <varlistentry>
 792           <term><parameter>database</parameter></term>
 793           <listitem>
 794             <para>A
 795             <glossterm linkend="gloss-database-object">database
 796             object</glossterm>. This will default to the value
 797             of <symbol>*default-database*</symbol>.</para>
 798           </listitem>
 799         </varlistentry>
 800         <varlistentry>
 801           <term><parameter>flatp</parameter></term>
 802           <listitem>
 803             <para>A Boolean whose default value is &nil;.</para>
 804           </listitem>
 805         </varlistentry>
 806         <varlistentry>
 807           <term><parameter>result-types</parameter></term>
 808           <listitem>
 809             <para>A
 810             <glossterm linkend="gloss-field-types">field type
 811             specifier</glossterm>. The default is &nil;.
 812             </para>
 813             <para>
 814               The purpose of this argument is cause &clsql; to
 815               import SQL numeric fields into numeric Lisp objects
 816               rather than strings. This reduces the cost of
 817               allocating a temporary string and the &clsql; users'
 818               inconvenience of converting number strings into number
 819               objects.
 820             </para>
 821             <para>
 822               A value of <symbol>:auto</symbol> causes &clsql;
 823               to automatically convert SQL fields into a
 824               numeric format where applicable. The default value of
 825               &nil; causes all fields to be returned as strings
 826               regardless of the SQL type. Otherwise a list is expected
 827               which has a element for each field that specifies the
 828               conversion. Valid type identifiers are:
 829               <simplelist type="vert">
 830                 <member><symbol>:int</symbol> Field is imported as a
 831                 signed integer, from 8-bits to 64-bits depending
 832                 upon the field type.
 833                 </member>
 834                 <member><symbol>:double</symbol> Field is imported as a
 835                 double-float number.
 836                 </member>
 837                 <member><symbol>t</symbol> Field is imported as a
 838                 string.
 839                 </member>
 840               </simplelist>
 841               If the list is shorter than the number of fields, the a
 842               value of <symbol>t</symbol> is assumed for the field.
 843               If the list is longer than the number of fields, the
 844               extra elements are ignored.
 845             </para>
 846           </listitem>
 847         </varlistentry>
 848         <varlistentry>
 849           <term><parameter>field-names</parameter></term>
 850           <listitem>
 851           <para>
 852             A boolean with a default value of &t;. When &t;, this
 853             function returns a second value of a list of field
 854             names. When &nil;, this function only returns one value -
 855             the list of rows.
 856           </para>
 857           </listitem>
 858         </varlistentry>
 859         <varlistentry>
 860           <term><returnvalue>result</returnvalue></term>
 861           <listitem>
 862             <para>A list representing the result set obtained.  For
 863             each tuple in the result set, there is an element in
 864             this list, which is itself a list of all the attribute
 865             values in the tuple.</para>
 866           </listitem>
 867         </varlistentry>
 868       </variablelist>
 869     </refsect1>
 870     <refsect1>
 871       <title>Description</title>
 872       <para>
 873         Executes the SQL query expression
 874         <parameter>query-expression</parameter>, which may be an SQL
 875         expression or a string, on the supplied
 876         <parameter>database</parameter> which defaults to
 877         <symbol>*default-database*</symbol>. <parameter>result-types</parameter>
 878         is a list of symbols which specifies the lisp type for each
 879         field returned by <parameter>query-expression</parameter>.
 880       </para>
 881       <para>
 882         If <parameter>result-types</parameter> is &nil; all results
 883         are returned as strings whereas the default value of
 884         <symbol>:auto</symbol> means that the lisp types are
 885         automatically computed for each field.
 886       </para>
 887       <para>
 888         <parameter>field-names</parameter> is &t; by default which
 889         means that the second value returned is a list of strings
 890         representing the columns selected by
 891         <parameter>query-expression</parameter>. If
 892         <parameter>field-names</parameter> is &nil;, the list of column
 893         names is not returned as a second value.
 894       </para>
 895       <para>
 896         <parameter>flatp</parameter> has a default value of &nil;
 897         which means that the results are returned as a list of
 898         lists.If FLATP is &t; and only one result is returned for each
 899         record selected by <parameter>query-expression</parameter>,
 900         the results are returned as elements of a list.
 901       </para>
 902     </refsect1>
 903     <refsect1>
 904       <title>Examples</title>
 905       <screen>
 906 (query "select emplid,first_name,last_name,height from employee where emplid = 1")
 907 => ((1 "Vladimir" "Lenin" 1.5564661d0)),
 908    ("emplid" "first_name" "last_name" "height")
 909
 910 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 911        :field-names nil)
 912 => ((1 "Vladimir" "Lenin" 1.5564661d0))
 913
 914 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 915        :field-names nil
 916        :result-types nil)
 917 => (("1" "Vladimir" "Lenin" "1.5564661"))
 918
 919 (query "select emplid,first_name,last_name,height from employee where emplid = 1"
 920        :field-names nil
 921        :result-types '(:int t t :double))
 922 => ((1 "Vladimir" "Lenin" 1.5564661))
 923
 924 (query "select last_name from employee where emplid > 5" :flatp t)
 925 => ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
 926    ("last_name")
 927
 928 (query "select last_name from employee where emplid > 10"
 929        :flatp t
 930        :field-names nil)
 931 => NIL
 932       </screen>
 933     </refsect1>
 934     <refsect1>
 935       <title>Side Effects</title>
 936       <para>Whatever effects the execution of the SQL query has
 937       on the underlying database, if any.</para>
 938     </refsect1>
 939     <refsect1>
 940       <title>Affected by</title>
 941       <para>None.</para>
 942     </refsect1>
 943     <refsect1>
 944       <title>Exceptional Situations</title>
 945       <para>If the execution of the SQL query leads to any errors, an
 946       error of type <errortype>sql-database-error</errortype> is
 947       signalled.</para>
 948     </refsect1>
 949     <refsect1>
 950       <title>See Also</title>
 951       <simplelist>
 952         <member><link linkend="execute-command"><function>execute-command</function></link></member>
 953         <member><link linkend="print-query"><function>print-query</function></link></member>
 954         <member><link linkend="do-query"><function>do-query</function></link></member>
 955         <member><link linkend="map-query"><function>map-query</function></link></member>
 956         <member><link linkend="loop-tuples"><function>loop</function></link></member>
 957         <member><link linkend="select"><function>select</function></link></member>
 958       </simplelist>
 959     </refsect1>
 960     <refsect1>
 961       <title>Notes</title>
 962       <para>The <parameter>field-names</parameter> and
 963       <parameter>result-types</parameter> keyword arguments are a
 964       &clsql; extension.</para>
 965     </refsect1>
 966   </refentry>
 967
 968   <refentry id="print-query">
 969     <refmeta>
 970       <refentrytitle>PRINT-QUERY</refentrytitle>
 971     </refmeta>
 972     <refnamediv>
 973       <refname>PRINT-QUERY</refname>
 974       <refpurpose>Prints a tabular report of query results.</refpurpose>
 975       <refclass>Function</refclass>
 976     </refnamediv>
 977     <refsect1>
 978       <title>Syntax</title>
 979       <synopsis>
 980       <function>print-query</function> <replaceable>query-expression</replaceable> &amp;key <replaceable>titles</replaceable> <replaceable>formats</replaceable> <replaceable>sizes</replaceable> <replaceable>stream</replaceable> <replaceable>database</replaceable> => <returnvalue></returnvalue></synopsis>
 981     </refsect1>
 982     <refsect1>
 983       <title>Arguments and Values</title>
 984       <variablelist>
 985         <varlistentry>
 986           <term><parameter>query-expression</parameter></term>
 987           <listitem>
 988             <para>An <glossterm linkend="gloss-sql-expression">sql
 989             expression</glossterm> that represents an SQL
 990             query which is expected to return a (possibly empty)
 991             result set.</para>
 992           </listitem>
 993         </varlistentry>
 994         <varlistentry>
 995           <term><parameter>database</parameter></term>
 996           <listitem>
 997             <para>A
 998             <glossterm linkend="gloss-database-object">database
 999             object</glossterm>. This will default to the value
1000             of <symbol>*default-database*</symbol>.</para>
1001           </listitem>
1002         </varlistentry>
1003         <varlistentry>
1004           <term><parameter>titles</parameter></term>
1005           <listitem>
1006             <para>
1007               A list of strings or &nil; which is the default value.
1008             </para>
1009           </listitem>
1010         </varlistentry>
1011         <varlistentry>
1012           <term><parameter>formats</parameter></term>
1013           <listitem>
1014             <para>
1015               A list of strings, &nil; or &t; which is the default value.
1016             </para>
1017           </listitem>
1018         </varlistentry>
1019         <varlistentry>
1020           <term><parameter>sizes</parameter></term>
1021           <listitem>
1022             <para>
1023               A list of numbers, &nil; or &t; which is the default value.
1024             </para>
1025           </listitem>
1026         </varlistentry>
1027         <varlistentry>
1028           <term><parameter>stream</parameter></term>
1029           <listitem>
1030             <para>
1031               An output stream or &t; which is the default value.
1032             </para>
1033           </listitem>
1034         </varlistentry>
1035       </variablelist>
1036     </refsect1>
1037     <refsect1>
1038       <title>Description</title>
1039       <para>Prints a tabular report of the results returned by the SQL
1040       query <parameter>query-expression</parameter>, which may be a
1041       symbolic SQL expression or a string, in
1042       <parameter>database</parameter> which defaults to
1043       <symbol>*default-database*</symbol>. The report is printed onto
1044       <parameter>stream</parameter> which has a default value of &t;
1045       which means that <symbol>*standard-output*</symbol> is used. The
1046       <parameter>title</parameter> argument, which defaults to &nil;,
1047       allows the specification of a list of strings to use as column
1048       titles in the tabular output. <parameter>sizes</parameter>
1049       accepts a list of column sizes, one for each column selected by
1050       <parameter>query-expression</parameter>, to use in formatting
1051       the tabular report. The default value of &t; means that minimum
1052       sizes are computed. <parameter>formats</parameter> is a list of
1053       format strings to be used for printing each column selected by
1054       <parameter>query-expression</parameter>. The default value of
1055       <parameter>formats</parameter> is &t; meaning that
1056       <symbol>~A</symbol> is used to format all columns or
1057       <symbol>~VA</symbol> if column sizes are used.
1058       </para>
1059     </refsect1>
1060     <refsect1>
1061       <title>Examples</title>
1062       <screen>
1063 (print-query [select [emplid] [first-name] [last-name] [email]
1064                      :from [employee]
1065                      :where [&lt; [emplid] 5]]
1066               :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1067 ID FORENAME SURNAME  EMAIL
1068 1  Vladimir Lenin    lenin@soviet.org
1069 2  Josef    Stalin   stalin@soviet.org
1070 3  Leon     Trotsky  trotsky@soviet.org
1071 4  Nikita   Kruschev kruschev@soviet.org
1072 =>
1073
1074 (print-query "select emplid,first_name,last_name,email from employee where emplid >= 5"
1075              :titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
1076 ID FORENAME   SURNAME   EMAIL
1077 5  Leonid     Brezhnev  brezhnev@soviet.org
1078 6  Yuri       Andropov  andropov@soviet.org
1079 7  Konstantin Chernenko chernenko@soviet.org
1080 8  Mikhail    Gorbachev gorbachev@soviet.org
1081 9  Boris      Yeltsin   yeltsin@soviet.org
1082 10 Vladimir   Putin     putin@soviet.org
1083 =>
1084 </screen>
1085     </refsect1>
1086     <refsect1>
1087       <title>Side Effects</title>
1088       <para>
1089         None.
1090       </para>
1091     </refsect1>
1092     <refsect1>
1093       <title>Affected by</title>
1094       <para>
1095         None.
1096       </para>
1097     </refsect1>
1098     <refsect1>
1099       <title>Exceptional Situations</title>
1100       <para>
1101         If the execution of the SQL query leads to any errors, an
1102         error of type <errortype>sql-database-error</errortype> is
1103         signalled.
1104       </para>
1105     </refsect1>
1106     <refsect1>
1107       <title>See Also</title>
1108       <simplelist>
1109         <member><link linkend="query"><function>query</function></link></member>
1110         <member><link linkend="do-query"><function>do-query</function></link></member>
1111         <member><link linkend="map-query"><function>map-query</function></link></member>
1112         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1113         <member><link linkend="select"><function>select</function></link></member>
1114       </simplelist>
1115     </refsect1>
1116     <refsect1>
1117       <title>Notes</title>
1118       <para>
1119         None.
1120       </para>
1121     </refsect1>
1122   </refentry>
1123
1124   <refentry id="select">
1125     <refmeta>
1126       <refentrytitle>SELECT</refentrytitle>
1127     </refmeta>
1128     <refnamediv>
1129       <refname>SELECT</refname>
1130       <refpurpose>Executes a query given the supplied constraints.</refpurpose>
1131       <refclass>Function</refclass>
1132     </refnamediv>
1133     <refsect1>
1134       <title>Syntax</title>
1135       <synopsis>
1136       <function>select</function> &amp;rest <replaceable>identifiers</replaceable> &amp;key <replaceable>all</replaceable> <replaceable>distinct</replaceable> <replaceable>from</replaceable> <replaceable>group-by</replaceable> <replaceable>having</replaceable> <replaceable>order-by</replaceable> <replaceable>set-operation</replaceable> <replaceable>where</replaceable> <replaceable>result-types</replaceable> <replaceable>field-names</replaceable> <replaceable>flatp</replaceable> <replaceable>refresh</replaceable> <replaceable>caching</replaceable> <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
1137     </refsect1>
1138     <refsect1>
1139       <title>Arguments and Values</title>
1140       <variablelist>
1141         <varlistentry>
1142           <term><parameter>identifiers</parameter></term>
1143           <listitem>
1144             <para>
1145               A set of <glossterm linkend="gloss-sql-expression">sql
1146               expressions</glossterm> each of which indicates a column
1147               to query.
1148             </para>
1149           </listitem>
1150         </varlistentry>
1151         <varlistentry>
1152           <term><parameter>all</parameter></term>
1153           <listitem>
1154             <para>
1155               A Boolean.
1156             </para>
1157           </listitem>
1158         </varlistentry>
1159         <varlistentry>
1160           <term><parameter>distinct</parameter></term>
1161           <listitem>
1162             <para>
1163               A Boolean.
1164             </para>
1165           </listitem>
1166         </varlistentry>
1167         <varlistentry>
1168           <term><parameter>from</parameter></term>
1169           <listitem>
1170             <para>
1171               One or more SQL expression representing tables.
1172             </para>
1173           </listitem>
1174         </varlistentry>
1175         <varlistentry>
1176           <term><parameter>group-by</parameter></term>
1177           <listitem>
1178             <para>
1179               An SQL expression.
1180             </para>
1181           </listitem>
1182         </varlistentry>
1183         <varlistentry>
1184           <term><parameter>having</parameter></term>
1185           <listitem>
1186             <para>
1187               An SQL expression.
1188             </para>
1189           </listitem>
1190         </varlistentry>
1191         <varlistentry>
1192           <term><parameter>limit</parameter></term>
1193           <listitem>
1194             <para>
1195               A non-negative integer.
1196             </para>
1197           </listitem>
1198         </varlistentry>
1199         <varlistentry>
1200           <term><parameter>offset</parameter></term>
1201           <listitem>
1202             <para>
1203               A non-negative integer.
1204             </para>
1205           </listitem>
1206         </varlistentry>
1207         <varlistentry>
1208           <term><parameter>order-by</parameter></term>
1209           <listitem>
1210             <para>
1211               An SQL expression.
1212             </para>
1213           </listitem>
1214         </varlistentry>
1215         <varlistentry>
1216           <term><parameter>set-operation</parameter></term>
1217           <listitem>
1218             <para>
1219               An SQL expression.
1220             </para>
1221           </listitem>
1222         </varlistentry>
1223         <varlistentry>
1224           <term><parameter>where</parameter></term>
1225           <listitem>
1226             <para>
1227               An SQL expression.
1228             </para>
1229           </listitem>
1230         </varlistentry>
1231         <varlistentry>
1232           <term><parameter>database</parameter></term>
1233           <listitem>
1234             <para>A
1235             <glossterm linkend="gloss-database-object">database
1236             object</glossterm>. This will default to the value
1237             of <symbol>*default-database*</symbol>.</para>
1238           </listitem>
1239         </varlistentry>
1240         <varlistentry>
1241           <term><parameter>flatp</parameter></term>
1242           <listitem>
1243             <para>A Boolean whose default value is &nil;.</para>
1244           </listitem>
1245         </varlistentry>
1246         <varlistentry>
1247           <term><parameter>result-types</parameter></term>
1248           <listitem>
1249             <para>A
1250             <glossterm linkend="gloss-field-types">field type
1251             specifier</glossterm>. The default is &nil;.
1252             </para>
1253             <para>
1254               The purpose of this argument is cause &clsql; to
1255               import SQL numeric fields into numeric Lisp objects
1256               rather than strings. This reduces the cost of
1257               allocating a temporary string and the &clsql; users'
1258               inconvenience of converting number strings into number
1259               objects.
1260             </para>
1261             <para>
1262               A value of <symbol>:auto</symbol> causes &clsql;
1263               to automatically convert SQL fields into a
1264               numeric format where applicable. The default value of
1265               &nil; causes all fields to be returned as strings
1266               regardless of the SQL type. Otherwise a list is expected
1267               which has a element for each field that specifies the
1268               conversion. Valid type identifiers are:
1269               <simplelist type="vert">
1270                 <member><symbol>:int</symbol> Field is imported as a
1271                 signed integer, from 8-bits to 64-bits depending
1272                 upon the field type.
1273                 </member>
1274                 <member><symbol>:double</symbol> Field is imported as a
1275                 double-float number.
1276                 </member>
1277                 <member><symbol>t</symbol> Field is imported as a
1278                 string.
1279                 </member>
1280               </simplelist>
1281               If the list is shorter than the number of fields, the a
1282               value of <symbol>t</symbol> is assumed for the field.
1283               If the list is longer than the number of fields, the
1284               extra elements are ignored.
1285             </para>
1286           </listitem>
1287         </varlistentry>
1288         <varlistentry>
1289           <term><parameter>field-names</parameter></term>
1290           <listitem>
1291           <para>
1292             A boolean with a default value of &t;. When &t;, this
1293             function returns a second value of a list of field
1294             names. When &nil;, this function only returns one value -
1295             the list of rows.
1296           </para>
1297           </listitem>
1298         </varlistentry>
1299         <varlistentry>
1300           <term><parameter>refresh</parameter></term>
1301           <listitem>
1302           <para>
1303             This value is only considered when CLOS objects are being
1304             selected. A boolean with a default value of &nil;. When
1305             the value of the <varname>caching</varname> keyword is
1306             &t;, a second equivalent <function>select</function> call
1307             will return the same view class instance objects. When
1308             <varname>refresh</varname> is &t;, then slots of the
1309             existing instances are updated as necessary. In such
1310             cases, you may wish to override the hook
1311             <function>instance-refresh</function>.
1312           </para>
1313           </listitem>
1314         </varlistentry>
1315         <varlistentry>
1316           <term><parameter>caching</parameter></term>
1317           <listitem>
1318           <para>
1319             This value is only considered when CLOS objects are being
1320             selected.  A boolean with a default value of
1321             <varname>*default-caching*</varname>. &clsql; caches
1322             objects in accordance with the &commonsql; interface: a
1323             second equivalent <function>select</function> call will
1324             return the same view class instance objects.
1325           </para>
1326           </listitem>
1327         </varlistentry>
1328         <varlistentry>
1329           <term><parameter>result</parameter></term>
1330           <listitem>
1331             <para>
1332               A list representing the result set obtained.  For each
1333               tuple in the result set, there is an element in this
1334               list, which is itself a list of all the attribute values
1335               in the tuple.
1336             </para>
1337           </listitem>
1338         </varlistentry>
1339       </variablelist>
1340     </refsect1>
1341     <refsect1>
1342       <title>Description</title>
1343       <para>
1344         Executes a query on <parameter>database</parameter>, which has
1345         a default value of <symbol>*default-database*</symbol>,
1346         specified by the SQL expressions supplied using the remaining
1347         arguments in <parameter>args</parameter>. The
1348         <function>select</function> function can be used to generate
1349         queries in both functional and object oriented contexts.
1350       </para>
1351       <para>
1352         In the functional case, the required arguments specify the
1353         columns selected by the query and may be symbolic SQL
1354         expressions or strings representing attribute
1355         identifiers. Type modified identifiers indicate that the
1356         values selected from the specified column are converted to the
1357         specified lisp type. The keyword arguments
1358         <parameter>all</parameter>, <parameter>distinct</parameter>,
1359         <parameter>from</parameter>, <parameter>group-by</parameter>,
1360         <parameter>having</parameter>, <parameter>limit</parameter>,
1361         <parameter>offset</parameter>, <parameter>order-by</parameter>,
1362         <parameter>set-operation</parameter> and
1363         <parameter>where</parameter> are used to specify, using the
1364         symbolic SQL syntax, the corresponding components of the SQL
1365         query generated by the call to
1366         <function>select</function>.
1367       </para>
1368       <para>
1369         <parameter>result-types</parameter> is a list of symbols which
1370         specifies the lisp type for each field returned by the
1371         query. If <parameter>result-types</parameter> is &nil; all
1372         results are returned as strings whereas the default value of
1373         <symbol>:auto</symbol> means that the lisp types are
1374         automatically computed for each
1375         field. <parameter>field-names</parameter> is &t; by default
1376         which means that the second value returned is a list of
1377         strings representing the columns selected by the query. If
1378         <parameter>field-names</parameter> is &nil;, the list of
1379         column names is not returned as a second value.
1380       </para>
1381       <para>
1382         In the object oriented case, the required arguments to
1383         <function>select</function> are symbols denoting View Classes
1384         which specify the database tables to query. In this case,
1385         <function>select</function> returns a list of View Class
1386         instances whose slots are set from the attribute values of the
1387         records in the specified table. <symbol>Slot-value</symbol> is
1388         a legal operator which can be employed as part of the symbolic
1389         SQL syntax used in the <parameter>where</parameter> keyword
1390         argument to <function>select</function>.
1391         <parameter>refresh</parameter> is &nil; by default which means
1392         that the View Class instances returned are retrieved from a
1393         cache if an equivalent call to <function>select</function> has
1394         previously been issued. If <parameter>refresh</parameter> is
1395         true, the View Class instances returned are updated as
1396         necessary from the database and the generic function
1397         <function>instance-refreshed</function> is called to perform
1398         any necessary operations on the updated instances.
1399       </para>
1400       <para>
1401         In both object oriented and functional contexts,
1402         <parameter>flatp</parameter> has a default value of &nil;
1403         which means that the results are returned as a list of
1404         lists. If <parameter>flatp</parameter> is t and only one
1405         result is returned for each record selected in the query, the
1406         results are returned as elements of a list.
1407       </para>
1408     </refsect1>
1409     <refsect1>
1410       <title>Examples</title>
1411       <screen>
1412 (select [first-name] :from [employee] :flatp t :distinct t
1413                      :field-names nil
1414                      :result-types nil
1415                      :order-by [first-name])
1416 => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
1417     "Yuri")
1418
1419 (select [first-name] [count [*]] :from [employee]
1420                                  :result-types nil
1421                                  :group-by [first-name]
1422                                  :order-by [first-name]
1423                                  :field-names nil)
1424 => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
1425     ("Mikhail" "1") ("Nikita" "1") ("Vladimir" "2") ("Yuri" "1"))
1426
1427 (select [last-name] :from [employee]
1428                     :where [like [email] "%org"]
1429                     :order-by [last-name]
1430                     :field-names nil
1431                     :result-types nil
1432                     :flatp t)
1433 => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
1434     "Stalin" "Trotsky" "Yeltsin")
1435
1436 (select [max [emplid]] :from [employee]
1437                        :flatp t
1438                        :field-names nil
1439                        :result-types :auto)
1440 => (10)
1441
1442 (select [avg [height]] :from [employee] :flatp t :field-names nil)
1443 => (1.58999584d0)
1444
1445 (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
1446 => ((1 "Lenin")),
1447    ("emplid" "last_name")
1448
1449 (select [emplid :string] :from [employee]
1450                          :where [= 1 [emplid]]
1451                          :field-names nil
1452                          :flatp t)
1453 => ("1")
1454
1455 (select [emplid] :from [employee] :order-by [emplid]
1456                  :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
1457                  :field-names nil
1458                  :flatp t)
1459 => (1 2 3 4)
1460
1461 (select [emplid] :from [employee]
1462         :where [in [emplid] '(1 2 3 4)]
1463         :flatp t
1464         :order-by [emplid]
1465         :field-names nil)
1466 => (1 2 3 4)
1467
1468 (select [emplid] :from [employee]
1469         :order-by [emplid]
1470         :limit 5
1471         :offset 3
1472         :field-names nil
1473         :flatp t)
1474 => (4 5 6 7 8)
1475
1476 (select [first-name] [last-name] :from [employee]
1477         :field-names nil
1478         :order-by '(([first-name] :asc) ([last-name] :desc)))
1479 => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
1480     ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
1481     ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
1482     ("Yuri" "Andropov"))
1483
1484 (select [last-name] :from [employee]
1485                  :set-operation [union [select [first-name] :from [employee]
1486                                                :order-by [last-name]]]
1487                  :flatp t
1488                  :result-types nil
1489                  :field-names nil)
1490 => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
1491     "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
1492     "Trotsky" "Vladimir" "Yeltsin" "Yuri")
1493       </screen>
1494     </refsect1>
1495     <refsect1>
1496       <title>Side Effects</title>
1497         <para>Whatever effects the execution of the SQL query has on
1498         the underlying database, if any.</para>
1499     </refsect1>
1500     <refsect1>
1501       <title>Affected by</title>
1502       <para>
1503         None.
1504       </para>
1505     </refsect1>
1506     <refsect1>
1507       <title>Exceptional Situations</title>
1508       <para>
1509         If the execution of the SQL query leads to any errors, an
1510         error of type <errortype>sql-database-error</errortype> is
1511         signalled.
1512       </para>
1513     </refsect1>
1514     <refsect1>
1515       <title>See Also</title>
1516       <simplelist>
1517         <member><link linkend="query"><function>query</function></link></member>
1518         <member><link linkend="print-query"><function>print-query</function></link></member>
1519         <member><link linkend="do-query"><function>do-query</function></link></member>
1520         <member><link linkend="map-query"><function>map-query</function></link></member>
1521         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1522         <member><link linkend="instance-refreshed"><function>instance-refreshed</function></link></member>
1523       </simplelist>
1524     </refsect1>
1525     <refsect1>
1526       <title>Notes</title>
1527       <para>
1528         The <function>select</function> function is actually
1529         implemented in &clsql; with a single
1530         <symbol>&amp;rest</symbol> parameter (which is subsequently
1531         destructured) rather than the keyword parameters presented
1532         here for the purposes of exposition. This means that incorrect
1533         or missing keywords or values may not trigger errors in the
1534         way that they would if <function>select</function> had been
1535         defined using keyword arguments.
1536       </para>
1537       <para>
1538         The <parameter>field-names</parameter> and
1539         <parameter>result-types</parameter> keyword arguments are a
1540         &clsql; extension.
1541       </para>
1542       <para>
1543         <parameter>select</parameter> is common across the functional
1544         and object-oriented data manipulation languages.
1545       </para>
1546     </refsect1>
1547   </refentry>
1548
1549
1550   <!-- iteration and mapping -->
1551
1552   <refentry id="do-query">
1553     <refmeta>
1554       <refentrytitle>DO-QUERY</refentrytitle>
1555     </refmeta>
1556     <refnamediv>
1557       <refname>DO-QUERY</refname>
1558       <refpurpose>Iterate over all the tuples of a query.</refpurpose>
1559       <refclass>Macro</refclass>
1560     </refnamediv>
1561     <refsect1>
1562       <title>Syntax</title>
1563       <synopsis>
1564       <function>do-query</function> ((&amp;rest <replaceable>args</replaceable>) <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> &amp;body <replaceable>body</replaceable> => <returnvalue>result</returnvalue></synopsis>
1565     </refsect1>
1566     <refsect1>
1567       <title>Arguments and Values</title>
1568       <variablelist>
1569         <varlistentry>
1570           <term><parameter>args</parameter></term>
1571           <listitem>
1572             <para>A list of variable names.</para>
1573           </listitem>
1574         </varlistentry>
1575         <varlistentry>
1576           <term><parameter>query-expression</parameter></term>
1577           <listitem>
1578             <para>An <glossterm linkend="gloss-sql-expression">sql
1579             expression</glossterm> that represents an SQL
1580             query which is expected to return a (possibly empty)
1581             result set, where each tuple has as many attributes as
1582             <parameter>function</parameter> takes arguments.</para>
1583           </listitem>
1584         </varlistentry>
1585         <varlistentry>
1586           <term><parameter>database</parameter></term>
1587           <listitem>
1588             <para>A
1589             <glossterm linkend="gloss-database-object">database
1590             object</glossterm>. This will default to
1591             <symbol>*default-database*</symbol>.</para>
1592           </listitem>
1593         </varlistentry>
1594         <varlistentry>
1595           <term><parameter>result-types</parameter></term>
1596           <listitem>
1597             <para>
1598               A <glossterm linkend="gloss-field-types">field type
1599               specifier</glossterm>.  The default is &nil;. See <link
1600               linkend="query"><function>query</function></link> for
1601               the semantics of this argument.
1602             </para>
1603           </listitem>
1604         </varlistentry>
1605         <varlistentry>
1606           <term><parameter>body</parameter></term>
1607           <listitem>
1608             <para>A body of Lisp code, like in a
1609             <function>destructuring-bind</function> form.</para>
1610           </listitem>
1611         </varlistentry>
1612         <varlistentry>
1613           <term><parameter>result</parameter></term>
1614           <listitem>
1615             <para>The result of executing <parameter>body</parameter>.</para>
1616           </listitem>
1617         </varlistentry>
1618       </variablelist>
1619     </refsect1>
1620     <refsect1>
1621       <title>Description</title>
1622       <para>
1623         Repeatedly executes <parameter>body</parameter> within a
1624         binding of <parameter>args</parameter> on the fields of each
1625         row selected by the SQL query
1626         <parameter>query-expression</parameter>, which may be a string
1627         or a symbolic SQL expression, in
1628         <parameter>database</parameter> which defaults to
1629         <symbol>*default-database*</symbol>.
1630       </para>
1631       <para>
1632         The body of code is executed in a block named
1633         <symbol>nil</symbol> which may be returned from prematurely
1634         via <function>return</function> or
1635         <function>return-from</function>.  In this case the result of
1636         evaluating the <function>do-query</function> form will be the
1637         one supplied to <function>return</function> or
1638         <function>return-from</function>.  Otherwise the result will
1639         be <symbol>nil</symbol>.
1640       </para>
1641       <para>
1642         The body of code appears also is if wrapped in a
1643         <function>destructuring-bind</function> form, thus allowing
1644         declarations at the start of the body, especially those
1645         pertaining to the bindings of the variables named in
1646         <parameter>args</parameter>.
1647       </para>
1648       <para>
1649         <parameter>result-types</parameter> is a list of symbols which
1650         specifies the lisp type for each field returned by
1651         <parameter>query-expression</parameter>. If
1652         <parameter>result-types</parameter> is &nil; all results are
1653         returned as strings whereas the default value of
1654         <symbol>:auto</symbol> means that the lisp types are
1655         automatically computed for each field.
1656       </para>
1657       <para>
1658         <parameter>query-expression</parameter> may be an object query
1659         (i.e., the selection arguments refer to View Classes), in
1660         which case <parameter>args</parameter> are bound to the tuples
1661         of View Class instances returned by the object oriented query.
1662       </para>
1663     </refsect1>
1664     <refsect1>
1665       <title>Examples</title>
1666       <screen>
1667 (do-query ((salary name) "select salary,name from simple")
1668   (format t "~30A gets $~2,5$~%" name (read-from-string salary)))
1669 >> Mai, Pierre                    gets $10000.00
1670 >> Hacker, Random J.              gets $08000.50
1671 => NIL
1672
1673 (do-query ((salary name) "select salary,name from simple")
1674   (return (cons salary name)))
1675 => ("10000.00" . "Mai, Pierre")
1676
1677 (let ((result '()))
1678   (do-query ((name) [select [last-name] :from [employee]
1679                             :order-by [last-name]])
1680     (push name result))
1681   result)
1682 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1683     "Chernenko" "Brezhnev" "Andropov")
1684
1685 (let ((result '()))
1686   (do-query ((e) [select 'employee :order-by [last-name]])
1687     (push (slot-value e 'last-name) result))
1688   result)
1689 => ("Yeltsin" "Trotsky" "Stalin" "Putin" "Lenin" "Kruschev" "Gorbachev"
1690     "Chernenko" "Brezhnev" "Andropov")
1691       </screen>
1692     </refsect1>
1693     <refsect1>
1694       <title>Side Effects</title>
1695       <para>Whatever effects the execution of the SQL query has
1696       on the underlying database, if any.</para>
1697     </refsect1>
1698     <refsect1>
1699       <title>Affected by</title>
1700       <para>None.</para>
1701     </refsect1>
1702     <refsect1>
1703       <title>Exceptional Situations</title>
1704       <para>If the execution of  the SQL query leads to any
1705       errors, an error of type
1706       <errortype>sql-database-error</errortype> is signalled.</para>
1707       <para>If the number of variable names in
1708       <parameter>args</parameter> and the number of attributes in
1709       the tuples in the result set don't match up, an error is
1710       signalled.</para>
1711     </refsect1>
1712     <refsect1>
1713       <title>See Also</title>
1714       <simplelist>
1715         <member><link linkend="query"><function>query</function></link></member>
1716         <member><link linkend="map-query"><function>map-query</function></link></member>
1717         <member><link linkend="print-query"><function>print-query</function></link></member>
1718         <member><link linkend="loop-tuples"><function>loop</function></link></member>
1719         <member><link linkend="select"><function>select</function></link></member>
1720       </simplelist>
1721     </refsect1>
1722     <refsect1>
1723       <title>Notes</title>
1724       <para>The <parameter>result-types</parameter> keyword argument
1725       is a &clsql; extension.</para>
1726       <para>
1727         <parameter>do-query</parameter> is common across the functional
1728         and object-oriented data manipulation languages.
1729       </para>
1730     </refsect1>
1731   </refentry>
1732
1733   <refentry id="loop-tuples">
1734     <refmeta>
1735       <refentrytitle>LOOP</refentrytitle>
1736     </refmeta>
1737     <refnamediv>
1738       <refname>LOOP</refname>
1739       <refpurpose>Extension to Common Lisp
1740       <computeroutput>Loop</computeroutput> to iterate over all the
1741       tuples of a query via a loop clause.</refpurpose>
1742       <refclass>Loop Clause</refclass>
1743     </refnamediv>
1744     <!-- refsect1>
1745       <title>Compatibility</title>
1746       <caution><para><function>loop-for-as-tuples</function> only works with &cmucl;.</para></caution>
1747     </refsect1 -->
1748     <refsect1>
1749       <title>Syntax</title>
1750       <synopsis>{as | for} <replaceable>var</replaceable> [<replaceable>type-spec</replaceable>] being {each | the} {record | records | tuple | tuples} {in | of} <replaceable>query</replaceable> [from <replaceable>database</replaceable>]</synopsis>
1751     </refsect1>
1752     <refsect1>
1753       <title>Arguments and Values</title>
1754       <variablelist>
1755         <varlistentry>
1756           <term><parameter>var</parameter></term>
1757           <listitem>
1758             <para>A <literal>d-var-spec</literal>, as defined in the
1759             grammar for <function>loop</function>-clauses in the ANSI
1760             Standard for Common Lisp.  This allows for the usual
1761             loop-style destructuring.</para>
1762           </listitem>
1763         </varlistentry>
1764         <varlistentry>
1765           <term><parameter>type-spec</parameter></term>
1766           <listitem>
1767             <para>An optional <literal>type-spec</literal> either
1768             simple or destructured, as defined in the grammar for
1769             <function>loop</function>-clauses in the ANSI Standard for
1770             Common Lisp.</para>
1771           </listitem>
1772         </varlistentry>
1773         <varlistentry>
1774           <term><parameter>query</parameter></term>
1775           <listitem>
1776             <para>An <glossterm linkend="gloss-sql-expression">sql
1777             expression</glossterm> that represents an SQL
1778             query which is expected to return a (possibly empty)
1779             result set, where each tuple has as many attributes as
1780             <parameter>function</parameter> takes arguments.</para>
1781           </listitem>
1782         </varlistentry>
1783         <varlistentry>
1784           <term><parameter>database</parameter></term>
1785           <listitem>
1786             <para>An optional
1787             <glossterm linkend="gloss-database-object">database
1788             object</glossterm>. This will default to the value
1789             of <symbol>*default-database*</symbol>.</para>
1790           </listitem>
1791         </varlistentry>
1792       </variablelist>
1793     </refsect1>
1794     <refsect1>
1795       <title>Description</title>
1796       <para>This clause is an iteration driver for
1797       <function>loop</function>, that binds the given variable
1798       (possibly destructured) to the consecutive tuples (which are
1799       represented as lists of attribute values) in the result set
1800       returned by executing the SQL <parameter>query</parameter>
1801       expression on the <parameter>database</parameter>
1802       specified.</para>
1803       <para>
1804         <parameter>query</parameter> may be an object query (i.e., the
1805         selection arguments refer to View Classes), in which case the
1806         supplied variable is bound to the tuples of View Class
1807         instances returned by the object oriented query.
1808       </para>
1809     </refsect1>
1810     <refsect1>
1811       <title>Examples</title>
1812       <screen>
1813 (defvar *my-db* (connect '("dent" "newesim" "dent" "dent"))
1814 "My database"
1815 => *MY-DB*
1816 (loop with time-graph = (make-hash-table :test #'equal)
1817    with event-graph = (make-hash-table :test #'equal)
1818    for (time event) being the tuples of "select time,event from log"
1819    from *my-db*
1820    do
1821      (incf (gethash time time-graph 0))
1822      (incf (gethash event event-graph 0))
1823    finally
1824      (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v)))
1825        (format t "~&amp;Time-Graph:~%===========~%")
1826        (maphash #'show-graph time-graph)
1827        (format t "~&amp;~%Event-Graph:~%============~%")
1828        (maphash #'show-graph event-graph))
1829      (return (values time-graph event-graph)))
1830 >> Time-Graph:
1831 >> ===========
1832 >> D                                        => 53000
1833 >> X                                        =>     3
1834 >> test-me                                  =>  3000
1835 >>
1836 >> Event-Graph:
1837 >> ============
1838 >> CLOS Benchmark entry.                    =>  9000
1839 >> Demo Text...                             =>     3
1840 >> doit-text                                =>  3000
1841 >> C    Benchmark entry.                    => 12000
1842 >> CLOS Benchmark entry                     => 32000
1843 => #&lt;EQUAL hash table, 3 entries {48350A1D}>
1844 => #&lt;EQUAL hash table, 5 entries {48350FCD}>
1845
1846 (loop for (forename surname)
1847       being each tuple in
1848         [select [first-name] [last-name] :from [employee]
1849                 :order-by [last-name]]
1850       collect (concatenate 'string forename " " surname))
1851 => ("Yuri Andropov" "Leonid Brezhnev" "Konstantin Chernenko" "Mikhail Gorbachev"
1852     "Nikita Kruschev" "Vladimir Lenin" "Vladimir Putin" "Josef Stalin"
1853     "Leon Trotsky" "Boris Yeltsin")
1854
1855 (loop for (e) being the records in
1856      [select 'employee :where [&lt; [emplid] 4] :order-by [emplid]]
1857   collect (slot-value e 'last-name))
1858 => ("Lenin" "Stalin" "Trotsky")
1859       </screen>
1860     </refsect1>
1861     <refsect1>
1862       <title>Side Effects</title>
1863       <para>Whatever effects the execution of the SQL query has
1864       on the underlying database, if any.</para>
1865     </refsect1>
1866     <refsect1>
1867       <title>Affected by</title>
1868       <para>None.</para>
1869     </refsect1>
1870     <refsect1>
1871       <title>Exceptional Situations</title>
1872       <para>If the execution of  the SQL query leads to any
1873       errors, an error of type
1874       <errortype>sql-database-error</errortype> is signalled.</para>
1875       <para>Otherwise, any of the exceptional situations of
1876       <function>loop</function> applies.</para>
1877     </refsect1>
1878     <refsect1>
1879       <title>See Also</title>
1880       <para>
1881         <simplelist>
1882           <member><link linkend="query"><function>query</function></link></member>
1883           <member><link linkend="map-query"><function>map-query</function></link></member>
1884           <member><link linkend="do-query"><function>do-query</function></link></member>
1885         <member><link linkend="print-query"><function>print-query</function></link></member>
1886         <member><link linkend="select"><function>select</function></link></member>
1887         </simplelist>
1888       </para>
1889     </refsect1>
1890     <refsect1>
1891       <title>Notes</title>
1892       <para>The <parameter>database</parameter> loop keyword is a
1893       &clsql; extension.</para>
1894       <para>
1895         The extended <function>loop</function> syntax is common across
1896         the functional and object-oriented data manipulation
1897         languages.
1898       </para>
1899     </refsect1>
1900   </refentry>
1901
1902   <refentry id="map-query">
1903     <refmeta>
1904       <refentrytitle>MAP-QUERY</refentrytitle>
1905     </refmeta>
1906     <refnamediv>
1907       <refname>MAP-QUERY</refname>
1908       <refpurpose>Map a function over all the tuples from a
1909       query</refpurpose>
1910       <refclass>Function</refclass>
1911     </refnamediv>
1912     <refsect1>
1913       <title>Syntax</title>
1914       <synopsis><function>map-query</function> <replaceable>output-type-spec</replaceable> <replaceable>function</replaceable> <replaceable>query-expression</replaceable> &amp;key <replaceable>database</replaceable> <replaceable>result-types</replaceable> => <returnvalue>result</returnvalue></synopsis>
1915     </refsect1>
1916     <refsect1>
1917       <title>Arguments and Values</title>
1918       <variablelist>
1919         <varlistentry>
1920           <term><parameter>output-type-spec</parameter></term>
1921           <listitem>
1922             <para>A sequence type specifier or <symbol>nil</symbol>.</para>
1923           </listitem>
1924         </varlistentry>
1925         <varlistentry>
1926           <term><parameter>function</parameter></term>
1927           <listitem>
1928             <para>A function designator.
1929             <parameter>function</parameter> takes a single argument which
1930             is the atom value for a query single with a single column
1931             or is a list of values for a multi-column query.</para>
1932           </listitem>
1933         </varlistentry>
1934         <varlistentry>
1935           <term><parameter>query-expression</parameter></term>
1936           <listitem>
1937             <para>An <glossterm linkend="gloss-sql-expression">sql
1938             expression</glossterm> that represents an SQL
1939             query which is expected to return a (possibly empty)
1940             result set.</para>
1941           </listitem>
1942         </varlistentry>
1943         <varlistentry>
1944           <term><parameter>database</parameter></term>
1945           <listitem>
1946             <para>A
1947             <glossterm linkend="gloss-database-object">database
1948             object</glossterm>. This will default to the value
1949             of <symbol>*default-database*</symbol>.</para>
1950           </listitem>
1951         </varlistentry>
1952         <varlistentry>
1953           <term><parameter>result-types</parameter></term>
1954           <listitem>
1955             <para>
1956               A <glossterm linkend="gloss-field-types">field type
1957               specifier</glossterm>.  The default is &nil;. See <link
1958               linkend="query"><function>query</function></link> for
1959               the semantics of this argument.
1960             </para>
1961           </listitem>
1962         </varlistentry>
1963         <varlistentry>
1964           <term><returnvalue>result</returnvalue></term>
1965           <listitem>
1966             <para>If <parameter>output-type-spec</parameter> is a
1967             type specifier other than <symbol>nil</symbol>, then a
1968             sequence of the type it denotes.  Otherwise
1969             <symbol>nil</symbol> is returned.</para>
1970           </listitem>
1971         </varlistentry>
1972       </variablelist>
1973     </refsect1>
1974     <refsect1>
1975       <title>Description</title>
1976       <para>
1977         Applies <parameter>function</parameter> to the successive
1978         tuples in the result set returned by executing the SQL
1979         <parameter>query-expression</parameter>.  If the
1980         <parameter>output-type-spec</parameter> is
1981         <symbol>nil</symbol>, then the result of each application of
1982         <parameter>function</parameter> is discarded, and
1983         <function>map-query</function> returns <symbol>nil</symbol>.
1984         Otherwise the result of each successive application of
1985         <parameter>function</parameter> is collected in a sequence of
1986         type <parameter>output-type-spec</parameter>, where the jths
1987         element is the result of applying
1988         <parameter>function</parameter> to the attributes of the jths
1989         tuple in the result set.  The collected sequence is the result
1990         of the call to <function>map-query</function>.
1991       </para>
1992       <para>
1993         If the <parameter>output-type-spec</parameter> is a subtype of
1994         <type>list</type>, the result will be a <type>list</type>.
1995       </para>
1996       <para>
1997         If the <parameter>result-type</parameter> is a subtype of
1998         <type>vector</type>, then if the implementation can determine
1999         the element type specified for the
2000         <parameter>result-type</parameter>, the element type of the
2001         resulting array is the result of
2002         <emphasis>upgrading</emphasis> that element type; or, if the
2003         implementation can determine that the element type is
2004         unspecified (or <symbol>*</symbol>), the element type of the
2005         resulting array is <type>t</type>; otherwise, an error is
2006         signaled.
2007       </para>
2008       <para>
2009         If <parameter>result-types</parameter> is &nil; all results
2010         are returned as strings whereas the default value of
2011         <symbol>:auto</symbol> means that the lisp types are
2012         automatically computed for each field.</para>
2013       <para>
2014         <parameter>query-expression</parameter> may be an object query
2015         (i.e., the selection arguments refer to View Classes), in
2016         which case the supplied function is applied to the tuples of
2017         View Class instances returned by the object oriented query.
2018       </para>
2019     </refsect1>
2020     <refsect1>
2021       <title>Examples</title>
2022       <screen>
2023 (map-query 'list #'(lambda (tuple)
2024                      (multiple-value-bind (salary name) tuple
2025                         (declare (ignorable name))
2026                         (read-from-string salary)))
2027             "select salary,name from simple where salary > 8000")
2028 => (10000.0 8000.5)
2029
2030 (map-query '(vector double-float)
2031            #'(lambda (tuple)
2032                (multiple-value-bind (salary name) tuple
2033                   (declare (ignorable name))
2034                   (let ((*read-default-float-format* 'double-float))
2035                     (coerce (read-from-string salary) 'double-float))
2036            "select salary,name from simple where salary > 8000")))
2037 => #(10000.0d0 8000.5d0)
2038 (type-of *)
2039 => (SIMPLE-ARRAY DOUBLE-FLOAT (2))
2040
2041 (let (list)
2042   (values (map-query nil #'(lambda (tuple)
2043                              (multiple-value-bind (salary name) tuple
2044                                (push (cons name (read-from-string salary)) list))
2045                          "select salary,name from simple where salary > 8000"))
2046           list))
2047 => NIL
2048 => (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))
2049
2050 (map-query 'vector #'identity
2051            [select [last-name] :from [employee] :flatp t
2052                    :order-by [last-name]])
2053 => #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
2054      "Stalin" "Trotsky" "Yeltsin")
2055
2056 (map-query 'list #'identity
2057            [select [first-name] [last-name] :from [employee]
2058                    :order-by [last-name]])
2059 => (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
2060     ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladimir" "Lenin")
2061     ("Vladimir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky")
2062     ("Boris" "Yeltsin"))
2063
2064 (map-query 'list #'last-name [select 'employee :order-by [emplid]])
2065 => ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
2066     "Gorbachev" "Yeltsin" "Putin")
2067       </screen>
2068     </refsect1>
2069     <refsect1>
2070       <title>Side Effects</title>
2071       <para>Whatever effects the execution of the SQL query has
2072       on the underlying database, if any.</para>
2073     </refsect1>
2074     <refsect1>
2075       <title>Affected by</title>
2076       <para>None.</para>
2077     </refsect1>
2078     <refsect1>
2079       <title>Exceptional Situations</title>
2080       <para>If the execution of  the SQL query leads to any
2081       errors, an error of type
2082       <errortype>sql-database-error</errortype> is signalled.</para>
2083       <para>An error of type <errortype>type-error</errortype> must
2084       be signaled if the <parameter>output-type-spec</parameter> is
2085       not a recognizable subtype of <type>list</type>, not a
2086       recognizable subtype of <type>vector</type>, and not
2087       <symbol>nil</symbol>.</para>
2088       <para>An error of type <errortype>type-error</errortype>
2089       should be signaled if
2090       <parameter>output-type-spec</parameter> specifies the number
2091       of elements and the size of the result set is different from
2092       that number.</para>
2093     </refsect1>
2094     <refsect1>
2095       <title>See Also</title>
2096       <simplelist>
2097         <member><link linkend="query"><function>query</function></link></member>
2098         <member><link linkend="do-query"><function>do-query</function></link></member>
2099         <member><link linkend="print-query"><function>print-query</function></link></member>
2100         <member><link linkend="loop-tuples"><function>loop</function></link></member>
2101         <member><link linkend="select"><function>select</function></link></member>
2102       </simplelist>
2103     </refsect1>
2104     <refsect1>
2105       <title>Notes</title>
2106       <para>The <parameter>result-types</parameter> keyword argument
2107       is a &clsql; extension.</para>
2108       <para>
2109         <parameter>map-query</parameter> is common across the
2110         functional and object-oriented data manipulation languages.
2111       </para>
2112     </refsect1>
2113   </refentry>
2114
2115 </reference>
2116