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

Suppose you want to find the constraints on a particular table. You try something like this:

Okay. That looks like it works. At a minimum, it is telling me that the countries table has a primary key named "countries_pkey". Also, for some strange reason, it has two unique constraints named "country_name_uk" and "country_name_unique".  

However consider table names with multiple words separated by underscores. If you are used to postmodern turning everything nicely into hyphens, you might try something like:

(query (:select '* :from 'information-schema.table-constraints :where (:= 'table-name "countries-hyphenated-for-some-reason")))

That will not work. Remember that you have to provide the actual string name of the table. Because sql does not like hyphens and demands underscores, the correct query in postmodern would look like this:

(query (:select '* :from 'information-schema.table-constraints :where (:= 'table-name "countries_hyphenated_for_some_reason")))

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

The simplest use of count looks like this:

(query (:select (:count '*) :from 'countries) :single)

202