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.html. They 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.

 (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. 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 (:set 1))

                   (:select (:+ 'n 1)

                            :from 't1

                            :where (:< 'n 100))))

  (:select (:sum 'n) :from 't1))

 :single)

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