postmodern-c (case, cast, 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]

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

Cast

There are two ways to cast in postmodern. First is the explicit use of cast as in:

(query (:select (:cast (:as "2018-04-19" 'date)) :from 'roadmap))

Then there is the use of the type operator which effectively translates into postgresql's :: casting shortcut.

(query (:select (:type "2018-04-19" date) :from 'roadmap))

Note that the targetted data type is quoted in the explicit use of cast and not quoted when using type.

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

Or you could use s-sql:to-sql-name like this:

(query (:select '*

                :from 'information-schema.table-constraints

                :where (:= 'table-name '$1))

       (s-sql:to-sql-name 'tasks-lists))

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)

Create-Index

To create a B-tree index on the column title in the table films:

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

To create an index using e.g. GIN instead of a B-tree index:

(query (:create-index 'films_idx :on "films" :using gin :fields 'title))

Page updated

Google Sites

Report abuse