postmodern-s (Set)
[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]
Set
Often I need to get a list of results where a query is in a particular set. The following would be the syntax in postmodern sql where the set is a list. If you want to use a vector, then you need to use Any:
The following are equivalent
(query (:select 'name
:from 'countries
:where (:in 'id
(:set 20 21 23))))
(let ((x (list 20 21 23)))
(query (:select 'name
:from 'countries
:where (:in 'id
(:set x)))))
(query (:select 'name
:from 'countries
:where (:in 'id (:set (list 20 21 23)))))
(("Greece") ("US") ("UK"))
However, the following will generate an error about inability to convert to an sql literal
(query (:select 'name
:from 'countries
:where (:in 'id
(:set '(20 21 23)))))
Now with selecting a dao
(select-dao 'countries
(:in 'id
(:set (list 20 21 23))))
(#<COUNTRIES {1002B8C111}> #<COUNTRIES {1002B8C801}> #<COUNTRIES{1002B8D611}>)
Now with selecting from a vector. Note both the use of any* and := instead of :in.
(let ((x (vector 20 21 23)))
(query (:select 'name
:from 'countries
:where (:= 'id (:any* x)))))
(("Greece") ("US") ("UK"))
Note that the responses are still coming back in a list of lists
If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in." See example in Parametized Statements
String_agg
String_agg returns a string containging the values returned, separated by a delimiter. In the following example, we are searching for the name of all the regions in the regions table and we want it all returned as a single string with a delimiting comma.
(query (:select (:string_agg 'name ",") :from 'regions))
(("Central America,Middle East,North America,South America,Central Asia,Pacific,Caribbean,Eastern Europe,Western Europe,EMEA,APAC,LATAM,Emerging,US,Canada,Africa,All,Asia,Eastern Africa,Middle Africa,Northern Africa,Southern Africa,Western Africa,Oceania,Northern Europe,Southern Europe,Eastern Asia,South Central Asia,South East Asia"))
Sum
Simple example for a sum:
(query (:select (:sum 'population) :from 'countries) :single)
14427958899