select &rest selections &key all set-operation distinct from result-types flatp where group-by having database order-by refresh => result-list
A set of database identifiers or strings.
A boolean.
An SQL operation.
A boolean.
An SQL table.
A list of symbols.
A boolean.
An SQL condition.
An SQL condition.
An SQL condition.
A database.
An SQL condition.
A boolean.
The function
select
selects data from
database
, which has a default value of *default-database*, given the constraints specified by the rest of the arguments. It returns a list of objects as specified by
selections
. By default, the objects will each be represented as lists of attribute values.
The argument
selections
consists either of database identifiers, type-modified database identifiers or literal strings. A type-modified database identifier is an expression such as
[foo :string]
which means that the values in column
foo
are returned as Lisp strings.
result-types
is used when
selections
is
*
or
[*]
. It should be a list of symbols such as
:string
and
:integer
, one for each field in the table being selected in order to specify the types to return. Note that, for specific selections, the result type can be specified by using a type-modified identfier as described above.
The
flatp
argument, which has a default value of
nil
, specifies if full bracketed results should be returned for each matched entry. If
flatp
is
nil
, the results are returned as a list of lists. If
flatp
is
t
, the results are returned as elements of a list, only if there is only one result per row. See the examples section for an example of the use of
flatp
.
The arguments all , set-operation , distinct , from , where , group-by , having and order-by have the same function as the equivalent SQL expression.
The
select
function is common across both the functional and object-oriented SQL interfaces. If
selections
refers to View Classes then the select operation becomes object-oriented. This means that
select
returns a list of View Class instances, and
slot-value
becomes a valid SQL operator for use within the
where
clause.
In the View Class case, a second equivalent
select
call will return the same View Class instance objects. If
refresh
is true, then existing instances are updated if necessary, and in this case you might need to extend the hook instance-refreshed. The default value of
refresh
is
nil
.
SQL expressions used in the
select
function are specified using the square bracket syntax, once this syntax has been enabled using enable-sql-reader-syntax.
The following is a potential query and result:
(select [person_id] [surname] :from [person])
=> ((111 "Brown") (112 "Jones") (113 "Smith"))
In the next example, the
flatp
argument is set to
t
, and the result is a simple list of surname values:
(select [surname] :from [person] :flatp t)
=> ("Brown" "Jones" "Smith")
In this example data in the attribute
largenum
, which is of a vendor-specific large numeric type, is returned to Lisp as strings:
(sql:select [largenum :string] :from [my-table])
In this example the second column of
some_table
is a date that we want to return as a string:
(sql:select [*]
:from [some_table]
:result-types '(nil :string))