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:

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.

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.