postmodern-A (and, any, as)

[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 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 (:= 'regions.id 'region-id) (:= 'regions.name "South America"))) :column)) 'vector)) (query (:select 'countries.name :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "North America") (:> 'longitude (:any* South-America)))))) (("Bermuda") ("Greenland"))

Arrays - see postmodern-arrays

As

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