MAP-QUERY
Prev | Functional Data Manipulation Language (FDML) | Next

## 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.

Prev | Up | Next
LOOP | Home | Transaction Handling