postmodern-w (when, window, with, with-recursive)

 [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]

When

(let ((param-latitude nil) (param-longitude t)) (query (:select 'id 'name (when param-latitude '0) (when param-longitude 'longitude) :from 'countries :where (:= 'id 20)))) ((20 "UK" NIL -2))

Window

As stated in the postgresql documentation: "When a query involves multiple window functions, it is possible to write out each one with a separate OVER clause, but this is duplicative and error-prone if the same windowing behavior is wanted for several functions. Instead, each windowing behavior can be named in a WINDOW clause and then referenced in OVER". http://www.postgresql.org/docs/9.1/static/tutorial-window.htmlThey are available in postmodern as of the 29 October 2013 additions to the git repository.

(query (:select (:over (:sum 'salary) 'w) (:over (:avg 'salary) 'w) :from 'empsalary :window (:as 'w (:partition-by 'depname :order-by (:desc 'salary)))))

With

With queries are often referred to as Common Table Expressions. They are used as ways to write auxiliary statements for use in a larger query. The Postgresql documentation covers them at http://www.postgresql.org/docs/9.1/static/queries-with.html. They are available in postmodern as of the 29 October 2013 additions to the git repository.

(query (:with (:as 'upd (:parens (:update 'employees :set 'sales-count (:= 'sales-count 1) :where (:= 'id (:select 'sales-person :from 'accounts :where (:= 'name "Acme Corporation"))) :returning '*))) (:insert-into 'employees-log (:select '* 'current-timestamp :from 'upd))))

With-recursive

With-recursive allows the with auxiliary statement to refer to itself. They are available in postmodern as of the 29 October 2013 additions to the git repository. These queries match the following template:

WITH RECURSIVE [temp table] [column list] AS ( [seed statement] UNION ALL [recursive statement - effectively looping through the table] ) [outer query which specifies the fields to be kept in the final result and throws away the intermediate results] 
A few postmodern samples follow which match up with the postgresql documentation examples:
(query (:with-recursive (:as (:t1 'n) (:union-all (:values 1) (:select (:n 1) :from 't1 :where (:< 'n 100)))) (:select (:sum 'n) :from 't1))) (query (:with-recursive (:as (:included-parts 'sub-part 'part 'quantity) (:union-all (:select 'sub-part 'part 'quantity :from 'parts :where (:= 'part "our-product")) (:select 'p.sub-part 'p.part 'p.quantity :from (:as 'included-parts 'pr) (:as 'parts 'p) :where (:= 'p.part 'pr.sub-part)))) (:select 'sub-part (:as (:sum 'quantity) 'total-quantity) :from 'included-parts :group-by 'sub-part))) (query (:with-recursive (:as (:search-graph 'id 'link 'data 'depth) (:union-all (:select 'g.id 'g.link 'g.data 1 :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:= 'sg.depth 1) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:= 'g.id 'sg.link)))) (:select '* :from 'search-graph))) (query (:with-recursive (:as (:search-graph 'id 'link 'data'depth 'path 'cycle) (:union-all (:select 'g.id 'g.link 'g.data 1 (:[] 'g.f1 'g.f2) nil :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:= 'sg.depth 1) (:|| 'path (:row 'g.f1 'g.f2)) (:= (:row 'g.f1 'g.f2) (:any* 'path)) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:and (:= 'g.id 'sg.link) (:not 'cycle))))) (:select '* :from 'search-graph)))

As a different example, consider a quicklisp dependency table where the fields are 'depends_on' and 'depended_on'. In other words library staple depends-on alexandria. So one record has "staple" in the depends_on column and "alexandria" in the depended_on column.

A function to return a list of all the dependencies of a quicklisp library (assuming the data is in a table called "dependencies") could look like this:

(defun list-dependencies (lib-name)   
  "Returns a list of the names of the direct and indirect libraries depended-on by lib-name."   
  (sort (alexandria:flatten
           (postmodern:query
            (:with-recursive
            (:as 'children
                 (:union
                  (:select 'depended-on :from 'dependencies :where (:= 'depends-on '$1))
                  (:select 'a.depended-on :from (:as 'dependencies 'a)
                           :inner-join (:as 'children 'b)
                           :on (:= 'a.depends-on 'b.depended-on))))
            (:select '* :from 'children))
            lib-name))
      #'string<))


Comments