[ Special Characters][ A] [ B] [ C] [ D] [ E] [ F] [ G] [ H] [ I] [ J] [ K] [ L] [ M] [ N] [ O] [ P] [ Q] [ R] [ S] [ T] [ U] [ V] [ W] [ X] [ Y] [ Z]
Case
As you know, case is a conditional expression that you can use to
either search for rows in a table when a condition is true or compare
a table field to a statement in the case expression. So consider the
following where you only look at the first 10 rows in the countries
table. The Case expression indicates that if the id is less than five,
return the word "Low" and if the id is greater than five, return the
word "high". Notice what happens when Denmark, with an id of exactly
five, gets returned.
(query (:select 'id 'name
(:case
((:< 'id 5) "Low")
((:> 'id 5) "High"))
:from 'countries
:where (:< 'id 10)))
((1 "Austria" "Low") (2 "Belgium" "Low") (4 "Canada" "Low") (5 "Denmark" :NULL)
(8 "France" "High") (9 "Germany" "High") (7 "Finland" "High"))
Coalesce
The COALESCE function in SQL returns the first non-NULL expression among its arguments.
(query
(:order-by (:select (:as 'countries.name 'country)
(:as (:coalesce 'countries.latitude 999) 'latitude)
(:as (:coalesce 'countries.longitude 999) 'longitude)
:from 'countries
:where (:and (:> 'countries.name '$1)
(:< 'countries.name '$2)))
'country)
"D" "F")
(("Denmark" 56 10) ("Dominican Republic" 19 -352/5) ("EU" 999 999)
("East Timor" -17/2 2511/20) ("Ecuador" -2 -773/10) ("Egypt" 27 30)
("El Salvador" 27/2 -1771/20) ("Estonia" 59 26) ("Ethiopia" 8 38))
Constraints Example
The query in this function generates information on the foreign key
constraints in a database. Ignoring all the postgresql internal table
names, while this looks complicated, the only additional items really
here are :[] as the op for getting an array index, :generate-series,
and :array-upper
(defun describe-foreign-key-constraints ()
"Generates a list of lists of information on the foreign key constraints"
(query (:order-by (:select 'conname
(:as 'conrelid 'table)
(:as 'pgc.relname 'tabname)
(:as 'a.attname 'columns)
(:as 'confrelid 'foreign-table)
(:as 'pgf.relname 'ftabname)
(:as 'af.attname 'fcolumn)
:from
(:as 'pg_attribute 'af)
(:as 'pg_attribute 'a)
(:as 'pg_class 'pgc)
(:as 'pg_class 'pgf)
(:as
(:select 'conname 'conrelid 'confrelid
(:as (:[] 'conkey 'i) 'conkey)
(:as (:[] 'confkey 'i) 'confkey)
:from (:as (:select 'conname
'conrelid 'confrelid
'conkey 'confkey
(:as
(:generate-series '1
(:array-upper 'conkey 1))
'i)
:from 'pg_constraint
:where (:= 'contype "f" ))
'ss)
) 'ss2)
:where (:and (:= 'af.attnum 'confkey)
(:= 'af.attrelid 'confrelid)
(:= 'a.attnum 'conkey)
(:= 'a.attrelid 'conrelid)
(:= 'pgf.relfilenode 'confrelid)
(:= 'pgc.relfilenode 'conrelid)))
'ftabname 'fcolumn 'tabname 'columns)))
Count
The simplest use of count looks like this:
(query (:select (:count '*) :from 'countries) :single)
202
|
|