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