Function

Summary

Selects data from a database given a number of specified constraints.

Package

sql

Signature

select &rest selections &key all set-operation distinct from flatp where group-by having database order-by refresh => result-list

Arguments

selections

A set of database identifiers or strings.

all

A boolean.

set-operation

An SQL operation.

distinct

A boolean.

from

An SQL table.

flatp

A boolean.

where

An SQL condition.

group-by

An SQL condition.

having

An SQL condition.

database

A database.

order-by

An SQL condition.

refresh

A boolean.

Values

result-list

A list of selections.

Description

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-modifed database identifier is an expression such as [foo :string] which means that the values in column foo are returned as Lisp strings.

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.

Examples

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])

See also

instance-refreshed
print-query