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)))



Comments