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]


(query (:select ' :from 'countries 'regions :where (:and (:= ' "North America") (:= ' 'countries.region-id)))) (("Bermuda") ("Canada") ("Greenland") ("Mexico") ("US")) (query (:select ' :from 'countries 'regions :where (:and (:= 'region-id ' (:= ' "Central America") (:< 'latitude 12)))) (("Costa Rica") ("Panama"))


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 from countries,regions where and'North America' and longitude > any(select longitude from countries, regions where region_id = and'South America')") (("Bermuda") ("Greenland"))

This can be re-phrased in s-sql as

(query (:select ' :from 'countries 'regions :where (:and (:= ' 'region-id) (:= ' "North America") (:> 'longitude (:any (:select 'longitude :from 'countries 'regions :where (:and (:= ' 'region-id) (:= ' "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 needs to be a vector and 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). (If you try to use a list, you will trigger an error message that you cannot convert that into an sql literal.)

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

(query "select name from countries where id=any($1)" (vector 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 (vector 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"))

Going back to our earlier example, remember that I said that unless you use a subselect, you need to provide a vector to :any or :any*. A standard query returns a list, not a vector. So you would need to coerce the variable into a vector before you pass it to :any*. See below as an example.

(let ((South-America (coerce (query (:select 'longitude :from 'countries 'regions :where (:and (:= ' 'region-id) (:= ' "South America"))) :column)) 'vector)) (query (:select ' :from 'countries 'regions :where (:and (:= ' 'region-id) (:= ' "North America") (:> 'longitude (:any* South-America)))))) (("Bermuda") ("Greenland"))

Arrays - see postmodern-arrays

As (sometimes referred to as Alias)

(query (:select (:as ' 'country) (:as ' 'region) :from 'countries 'regions :where (:and (:= ' 'region-id) (:= ' "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


Simple example easily applicable to max and min:
(query (:select (:avg 'longitude)
                :from 'countries 'regions
                :where (:and (:= ' 'region-id)
                             (:= ' "North America"))))