Name

SELECT — Executes a query given the supplied constraints.Function

Syntax

      select &rest identifiers &key all distinct from group-by having limit offset order-by set-operation where result-types field-names flatp refresh caching database => result

Arguments and Values

identifiers

A set of sql expressions each of which indicates a column to query.

all

A Boolean.

distinct

A Boolean.

from

One or more SQL expression representing tables.

group-by

An SQL expression.

having

An SQL expression.

limit

A non-negative integer.

offset

A non-negative integer.

order-by

An SQL expression.

set-operation

An SQL expression.

where

An SQL expression.

database

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

flatp

A Boolean whose default value is NIL.

result-types

A field type specifier. The default is :auto.

The purpose of this argument is cause CLSQL to import SQL numeric fields into numeric Lisp objects rather than strings. This reduces the cost of allocating a temporary string and the CLSQL users' inconvenience of converting number strings into number objects.

A value of :auto causes CLSQL to automatically convert SQL fields into a numeric format where applicable. The default value of NIL causes all fields to be returned as strings regardless of the SQL type. Otherwise a list is expected which has a element for each field that specifies the conversion. Valid type identifiers are:

:int Field is imported as a signed integer, from 8-bits to 64-bits depending upon the field type.
:double Field is imported as a double-float number.
t Field is imported as a string.

If the list is shorter than the number of fields, the a value of t is assumed for the field. If the list is longer than the number of fields, the extra elements are ignored.

field-names

A boolean with a default value of T. When T, this function returns a second value of a list of field names. When NIL, this function only returns one value - the list of rows.

refresh

This value is only considered when CLOS objects are being selected. A boolean with a default value of NIL. When the value of the caching keyword is T, a second equivalent select call will return the same view class instance objects. When refresh is T, then slots of the existing instances are updated as necessary. In such cases, you may wish to override the hook instance-refresh.

caching

This value is only considered when CLOS objects are being selected. A boolean with a default value of *default-caching*. CLSQL caches objects in accordance with the CommonSQL interface: a second equivalent select call will return the same view class instance objects.

result

A list representing the result set obtained. For each tuple in the result set, there is an element in this list, which is itself a list of all the attribute values in the tuple.

Description

Executes a query on database, which has a default value of *default-database*, specified by the SQL expressions supplied using the remaining arguments in args. The select function can be used to generate queries in both functional and object oriented contexts.

In the functional case, the required arguments specify the columns selected by the query and may be symbolic SQL expressions or strings representing attribute identifiers. Type modified identifiers indicate that the values selected from the specified column are converted to the specified lisp type. The keyword arguments all, distinct, from, group-by, having, limit, offset, order-by, set-operation and where are used to specify, using the symbolic SQL syntax, the corresponding components of the SQL query generated by the call to select.

result-types is a list of symbols which specifies the lisp type for each field returned by the query. 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. field-names is T by default which means that the second value returned is a list of strings representing the columns selected by the query. If field-names is NIL, the list of column names is not returned as a second value.

In the object oriented case, the required arguments to select are symbols denoting View Classes which specify the database tables to query. In this case, select returns a list of View Class instances whose slots are set from the attribute values of the records in the specified table. Slot-value is a legal operator which can be employed as part of the symbolic SQL syntax used in the where keyword argument to select. refresh is NIL by default which means that the View Class instances returned are retrieved from a cache if an equivalent call to select has previously been issued. If refresh is true, the View Class instances returned are updated as necessary from the database and the generic function instance-refreshed is called to perform any necessary operations on the updated instances.

In both object oriented and functional contexts, flatp has a default value of NIL which means that the results are returned as a list of lists. If flatp is t and only one result is returned for each record selected in the query, the results are returned as elements of a list.

Examples

(select [first-name] :from [employee] :flatp t :distinct t
                     :field-names nil 
                     :result-types nil 
                     :order-by [first-name])
=> ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
    "Yuri")

(select [first-name] [count [*]] :from [employee]
	                         :result-types nil 
			         :group-by [first-name]
			         :order-by [first-name]
			         :field-names nil)
=> (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
    ("Mikhail" "1") ("Nikita" "1") ("Vladimir" "2") ("Yuri" "1"))

(select [last-name] :from [employee] 
                    :where [like [email] "%org"]
		    :order-by [last-name]
		    :field-names nil 
		    :result-types nil 
		    :flatp t)
=> ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
    "Stalin" "Trotsky" "Yeltsin")

(select [max [emplid]] :from [employee] 
                       :flatp t 
	               :field-names nil 
                       :result-types :auto)
=> (10)

(select [avg [height]] :from [employee] :flatp t :field-names nil)
=> (1.58999584d0)

(select [emplid] [last-name] :from [employee] :where [= [emplid] 1]) 
=> ((1 "Lenin")), 
   ("emplid" "last_name")

(select [emplid :string] :from [employee] 
                         :where [= 1 [emplid]] 
                         :field-names nil 
                         :flatp t)
=> ("1")

(select [emplid] :from [employee] :order-by [emplid] 
                 :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
                 :field-names nil 
                 :flatp t)
=> (1 2 3 4)

(select [emplid] :from [employee] 
        :where [in [emplid] '(1 2 3 4)]
        :flatp t 
        :order-by [emplid] 
        :field-names nil)
=> (1 2 3 4)

(select [emplid] :from [employee] 
        :order-by [emplid]
        :limit 5 
        :offset 3
        :field-names nil
        :flatp t)
=> (4 5 6 7 8)

(select [first-name] [last-name] :from [employee] 
        :field-names nil 
        :order-by '(([first-name] :asc) ([last-name] :desc)))
=> (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
    ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
    ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
    ("Yuri" "Andropov"))

(select [last-name] :from [employee]                   
                 :set-operation [union [select [first-name] :from [employee]
                                               :order-by [last-name]]]
                 :flatp t
                 :result-types nil 
                 :field-names nil)
=> ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
    "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
    "Trotsky" "Vladimir" "Yeltsin" "Yuri")
      

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.

See Also

query
print-query
do-query
map-query
loop
instance-refreshed

Notes

The select function is actually implemented in CLSQL with a single &rest parameter (which is subsequently destructured) rather than the keyword parameters presented here for the purposes of exposition. This means that incorrect or missing keywords or values may not trigger errors in the way that they would if select had been defined using keyword arguments.

The field-names and result-types keyword arguments are a CLSQL extension.

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