SELECT — Executes a query given the supplied constraints.Function
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 => resultidentifiersA set of sql expressions each of which indicates a column to query.
allA Boolean.
distinctA Boolean.
fromOne or more SQL expression representing tables.
group-byAn SQL expression.
havingAn SQL expression.
limitA non-negative integer.
offsetA non-negative integer.
order-byAn SQL expression.
set-operationAn SQL expression.
whereAn SQL expression.
databaseA database object. This will default to the value of *default-database*.
flatpA Boolean whose default value is NIL.
result-typesA 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.
resultA 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.
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.
(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")
If the execution of the SQL query leads to any errors, an error of type sql-database-error is signalled.
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.