postmodern-c (case, coalesce, constraints, count, create-index)

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


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


The COALESCE function in SQL returns the first non-NULL expression among its arguments.

(query (:order-by (:select (:as ' 'country) (:as (:coalesce 'countries.latitude 999) 'latitude) (:as (:coalesce 'countries.longitude 999) 'longitude) :from 'countries :where (:and (:> ' '$1) (:< ' '$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 Examples

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

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

(("mydatabase" "public" "countries_pkey" "mydatabase" "public" "countries" "PRIMARY KEY" "NO" "NO") ("mydatabase" "public" "country_name_uk" "mydatabase" "public" "countries" "UNIQUE" "NO" "NO") ("mydatabase" "public" "country_name_unique" "mydatabase" "public" "countries" "UNIQUE" "NO" "NO") ("mydatabase" "public" "2200_456618_1_not_null" "mydatabase" "public" "countries" "CHECK" "NO" "NO"))

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


To create a B-tree index on the column title in the table films:
 (query (:create-index 'films_idx :on "films" :fields 'title))
To create an index on the postmodern expression lower so as to generate efficient case insensitive searches
(query (:create-index 'films_idx :on "films" :fields (:lower 'title)))