Name

MAP-QUERY — Map a function over all the tuples from a queryFunction

Syntax

map-query output-type-spec function query-expression &key database result-types => result

Arguments and Values

output-type-spec

A sequence type specifier or nil.

function

A function designator. function takes a single argument which is the atom value for a query single with a single column or is a list of values for a multi-column query.

query-expression

An sql expression that represents an SQL query which is expected to return a (possibly empty) result set.

database

A database object. This will default to the value of *default-database*.

result-types

A field type specifier. The default is NIL. See query for the semantics of this argument.

result

If output-type-spec is a type specifier other than nil, then a sequence of the type it denotes. Otherwise nil is returned.

Description

Applies function to the successive tuples in the result set returned by executing the SQL query-expression. If the output-type-spec is nil, then the result of each application of function is discarded, and map-query returns nil. Otherwise the result of each successive application of function is collected in a sequence of type output-type-spec, where the jths element is the result of applying function to the attributes of the jths tuple in the result set. The collected sequence is the result of the call to map-query.

If the output-type-spec is a subtype of list, the result will be a list.

If the result-type is a subtype of vector, then if the implementation can determine the element type specified for the result-type, the element type of the resulting array is the result of upgrading that element type; or, if the implementation can determine that the element type is unspecified (or *), the element type of the resulting array is t; otherwise, an error is signaled.

If result-types is NIL all results are returned as strings whereas the default value of :auto means that the lisp types are automatically computed for each field.

query-expression may be an object query (i.e., the selection arguments refer to View Classes), in which case the supplied function is applied to the tuples of View Class instances returned by the object oriented query.

Examples

(map-query 'list #'(lambda (tuple) 
                     (multiple-value-bind (salary name) tuple
                        (declare (ignorable name))
                        (read-from-string salary)))
            "select salary,name from simple where salary > 8000")
=> (10000.0 8000.5)

(map-query '(vector double-float)
           #'(lambda (tuple)
               (multiple-value-bind (salary name) tuple
                  (declare (ignorable name))
                  (let ((*read-default-float-format* 'double-float))
                    (coerce (read-from-string salary) 'double-float))
           "select salary,name from simple where salary > 8000")))
=> #(10000.0d0 8000.5d0)
(type-of *)
=> (SIMPLE-ARRAY DOUBLE-FLOAT (2))

(let (list)
  (values (map-query nil #'(lambda (tuple) 
                             (multiple-value-bind (salary name) tuple
                               (push (cons name (read-from-string salary)) list))
                         "select salary,name from simple where salary > 8000"))
          list))
=> NIL
=> (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0))

(map-query 'vector #'identity
           [select [last-name] :from [employee] :flatp t
                   :order-by [last-name]])
=> #("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
     "Stalin" "Trotsky" "Yeltsin")

(map-query 'list #'identity
           [select [first-name] [last-name] :from [employee] 
                   :order-by [last-name]])
=> (("Yuri" "Andropov") ("Leonid" "Brezhnev") ("Konstantin" "Chernenko")
    ("Mikhail" "Gorbachev") ("Nikita" "Kruschev") ("Vladimir" "Lenin")
    ("Vladimir" "Putin") ("Josef" "Stalin") ("Leon" "Trotsky") 
    ("Boris" "Yeltsin"))

(map-query 'list #'last-name [select 'employee :order-by [emplid]])
=> ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
    "Gorbachev" "Yeltsin" "Putin")
      

Side Effects

Whatever effects the execution of the SQL query has on the underlying database, if any.

Affected by

None.

Exceptional Situations

If the execution of the SQL query leads to any errors, an error of type sql-database-error is signalled.

An error of type type-error must be signaled if the output-type-spec is not a recognizable subtype of list, not a recognizable subtype of vector, and not nil.

An error of type type-error should be signaled if output-type-spec specifies the number of elements and the size of the result set is different from that number.

See Also

query
do-query
print-query
loop
select

Notes

The result-types keyword argument is a CLSQL extension.

map-query is common across the functional and object-oriented data manipulation languages.