postmodern-A (and, any, as, avg)

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

And

(query (:select 'countries.name

                :from 'countries 'regions

                :where (:and (:= 'regions.name "North America")

                             (:= 'regions.id 'countries.region-id))))

(("Bermuda") ("Canada") ("Greenland") ("Mexico") ("US"))

(query (:select 'countries.name

                :from 'countries 'regions

                :where (:and (:= 'region-id 'regions.id)

                             (:= 'regions.name "Central America")

                             (:< 'latitude 12))))(("Costa Rica") ("Panama"))

Any

Any needs to be considered as a special case. Quoting Marijn Haverbeke here,"Postgres has both a function-call-style any and an infix any, and S-SQL's syntax doesn't allow them to be distinguished." As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.

In general, the any qualifier in an sql statement looks at a subquery and does a comparison against that subquery. Sticking with our countries table, we have latitude, longitude data for every country (I'm not sure whether my table pulled the capital cities or the geographic center) and some designated a region for each country, so we have a region-id that matches the primary key 'id' in a regions table.

Out of curiosity, let's determine which countries in "North America" have a longitude less than any country in "South America". The standard sql could look like this:

(query "select countries.name         

               from countries,regions         

               where regions.id=region_id

               and regions.name='North America'

               and longitude > any(select longitude

                                          from countries, regions

                                          where region_id = regions.id

                                          and regions.name='South America')")

(("Bermuda") ("Greenland"))

This can be re-phrased in s-sql as

(query  (:select 'countries.name

                 :from 'countries 'regions

                 :where (:and (:= 'regions.id 'region-id)

                              (:= 'regions.name "North America")

                              (:> 'longitude

                                  (:any

                                   (:select 'longitude

                                            :from 'countries 'regions

                                            :where (:and (:= 'regions.id 'region-id)

                                                         (:= 'regions.name "South America"))))))))

(("Bermuda") ("Greenland"))

Subselects work fine in both regular sql and s-sql. If you have already calculated your subselect and put it in a variable, that variable can be either a list or a vector. Whether you should use the :any sql-op or the :any* sql-op depends on your phrasing. (Notice that the second variation has an asterisk). 

The SQL keyword ANY can be used in a parameterized sql statement if you provide it with a list or vector. The following two toy examples work in raw sql.

(query "select name from countries where id=any($1)"

       '(21 22))

(("Iceland") ("US"))

(let ((toy-query (vector 21 22)))

  (query "select name from countries where id=any($1)"

         toy-query))

(("Iceland") ("US"))

Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.

(let ((toy-query '(21 22)))

  (query (:select 'name

                  :from 'countries

                  :where (:= 'id (:any '$1)))

         toy-query))

;; Evaluation aborted on #<CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}>.

(let ((toy-query (vector 21 22)))

  (query (:select 'name

                  :from 'countries

                  :where (:= 'id (:any* '$1)))

         toy-query))

(("Iceland") ("US"))

Arrays - see postmodern-arrays

As (sometimes referred to as Alias)

(query (:select (:as 'countries.name 'country)

                (:as 'regions.name 'region)

                :from 'countries 'regions

                :where (:and (:= 'regions.id 'region-id)

                             (:= 'regions.name "Central America")))

       :alists)

(((:COUNTRY . "Belize") (:REGION . "Central America"))

 ((:COUNTRY . "Costa Rica")  (:REGION . "Central America"))

 ((:COUNTRY . "El Salvador")  (:REGION . "Central America"))

 ((:COUNTRY . "Guatemala")  (:REGION . "Central America"))

 ((:COUNTRY . "Panama") (:REGION . "Central America"))

 ((:COUNTRY . "Nicaragua") (:REGION . "Central America")))

The following uses aliases for both columns and tables in the from and inner-join clauses:

(query (:order-by

        (:select (:as 'recs.firstname 'firstname)

                 (:as 'recs.surname 'surname)

                 :distinct

                 :from (:as 'cd.members 'mems)

                 :inner-join (:as 'cd.members 'recs)

                 :on (:= 'recs.memid 'mems.recommendedby))

        'surname 'firstname))

Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:

"select sum(slots as "Total Slots" from cd.bookings"

without using :raw

Avg

Simple example easily applicable to max and min:

(query (:select (:avg 'longitude)

                :from 'countries 'regions

                :where (:and (:= 'regions.id 'region-id)

                             (:= 'regions.name "North America"))))

((-17939/200))

1