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