<div dir="ltr"><pre><div class="" id="LC607"><pre><div class="" id="LC589">I have been writing queries requiring window functions and recursive with functions. I finally decided to try to write some sql-ops so that I can use s-sql. They work for me but would appreciate comments. I did include a :parens op because I find that sometimes I just need an additional set of parens for an sql query. Below are the functions, the amendments to :select and :order-by and usage examples.</div>
<div class="" id="LC589"><br></div><div class="" id="LC589"><pre><div class="" id="LC716"><span class="">(</span><span class="">def-sql-op</span> <span class="">:over</span> <span class="">(</span><span class="">form</span> <span class="">&rest</span> <span class="">args</span><span class="">)</span></div>
<div class="" id="LC717"> <span class="">(</span><span class="">if</span> <span class="">args</span> <span class="">`</span><span class="">(</span><span class="">"("</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form</span><span class="">)</span> <span class="">" OVER "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand-list</span> <span class="">args</span><span class="">)</span> <span class="">")"</span><span class="">)</span></div>
<div class="" id="LC718"> <span class="">`</span><span class="">(</span><span class="">"("</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form</span><span class="">)</span> <span class="">" OVER ()) "</span><span class="">)))</span></div>
<div class="" id="LC719"><br></div><div class="" id="LC720"><span class="" style="font-family:arial">(</span><span class="" style="font-family:arial">def-sql-op</span><span style="font-family:arial"> </span><span class="" style="font-family:arial">:partition-by</span><span style="font-family:arial"> </span><span class="" style="font-family:arial">(</span><span class="" style="font-family:arial">&rest</span><span style="font-family:arial"> </span><span class="" style="font-family:arial">args</span><span class="" style="font-family:arial">)</span><br>
</div><div class="" id="LC723"> <span class="">`</span><span class="">(</span><span class="">"(PARTITION BY "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand-list</span> <span class="">args</span><span class="">)</span> <span class="">")"</span><span class="">))</span></div>
<div class="" id="LC724"><br></div><div class="" id="LC725"><span class="">(</span><span class="">def-sql-op</span> <span class="">:partition-by-order-by</span> <span class="">(</span><span class="">form</span> <span class="">&rest</span> <span class="">fields</span><span class="">)</span></div>
<div class="" id="LC726"> <span class="">`</span><span class="">(</span><span class="">"(PARTITION BY "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form</span><span class="">)</span> <span class="">" ORDER BY "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand-list</span> <span class="">fields</span><span class="">)</span> <span class="">") "</span><span class="">))</span></div>
<div class="" id="LC727"><br></div><div class="" id="LC728"><span class="">(</span><span class="">def-sql-op</span> <span class="">:parens</span> <span class="">(</span><span class="">op</span><span class="">)</span> <span class="">`</span><span class="">(</span><span class="">" ("</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">op</span><span class="">)</span> <span class="">") "</span><span class="">))</span></div>
<div class="" id="LC729"><br></div><div class="" id="LC730"><span class="">(</span><span class="">def-sql-op</span> <span class="">:with</span> <span class="">(</span><span class="">&rest</span> <span class="">args</span><span class="">)</span></div>
<div class="" id="LC731"> <span class="">(</span><span class="">let</span> <span class="">((</span><span class="">x</span> <span class="">(</span><span class="">butlast</span> <span class="">args</span><span class="">))</span> <span class="">(</span><span class="">y</span> <span class="">(</span><span class="">last</span> <span class="">args</span><span class="">)))</span> </div>
<div class="" id="LC732"> <span class="">`</span><span class="">(</span><span class="">"WITH "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand-list</span> <span class="">x</span><span class="">)</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">(</span><span class="">car</span> <span class="">y</span><span class="">)))))</span></div>
<div class="" id="LC733"><br></div><div class="" id="LC734"><span class="">(</span><span class="">def-sql-op</span> <span class="">:with-recursive1</span> <span class="">(</span><span class="">form1</span> <span class="">form2</span><span class="">)</span></div>
<div class="" id="LC735"> <span class="">`</span><span class="">(</span><span class="">"WITH RECURSIVE "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form1</span><span class="">)</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form2</span><span class="">)))</span></div>
<div class="" id="LC736"><br></div><div class="" id="LC737"><span class="">(</span><span class="">def-sql-op</span> <span class="">:window</span> <span class="">(</span><span class="">form</span><span class="">)</span></div>
<div class="" id="LC738"> <span class="">`</span><span class="">(</span><span class="">"WINDOW "</span> <span class="">,@</span><span class="">(</span><span class="">sql-expand</span> <span class="">form</span><span class="">)))</span></div>
</pre></div><div class="" id="LC589"><br></div><div class="" id="LC589">Amendment to :select (to pick up the new "window" arg</div><div class="" id="LC589"><br></div><div class="" id="LC590"><span class="">(def-sql-op :select (&rest args)</span></div>
<div class="" id="LC591"><span class=""> (split-on-keywords ((vars *) (distinct - ?) (distinct-on * ?) (from * ?) (window ?) (where ?) (group-by * ?)</span></div><div class="" id="LC592"><span class=""> (having ?)) (cons :vars args)</span></div>
<div class="" id="LC593"><span class=""> `("</span><span class="">(</span><span class="">SELECT</span> <span class="">"</span></div><div class="" id="LC594"><span class=""> ,@(if distinct '("</span><span class="">DISTINCT</span> <span class="">"))</span></div>
<div class="" id="LC595"><span class=""> ,@(if distinct-on `("</span><span class="">DISTINCT</span> <span class="">ON</span> <span class="">(</span><span class="">" ,@(sql-expand-list distinct-on) "</span><span class="">)</span> <span class="">"))</span></div>
<div class="" id="LC596"><span class=""> ,@(sql-expand-list vars)</span></div><div class="" id="LC597"><span class=""> ,@(if from (cons "</span> <span class="">FROM</span> <span class="">" (expand-joins from)))</span></div>
<div class="" id="LC598"><span class=""> ,@(if window (cons "</span> <span class="">WINDOW</span> <span class="">" (sql-expand-list window)))</span></div><div class="" id="LC599"><span class=""> ,@(if where (cons "</span> <span class="">WHERE</span> <span class="">" (sql-expand (car where))))</span></div>
<div class="" id="LC600"><span class=""> ,@(if group-by (cons "</span> <span class="">GROUP</span> <span class="">BY</span> <span class="">" (sql-expand-list group-by)))</span></div><div class="" id="LC601">
<span class=""> ,@(if having (cons "</span> <span class="">HAVING</span> <span class="">" (sql-expand (car having))))</span></div><div class="" id="LC602"><span class=""> "</span><span class="">)</span><span class="">")))</span></div>
</pre></div><div class="" id="LC607"><span class="">Amendment to :order-by to pick up the situation where the only arg is the form</span></div><div class="" id="LC607"><span class=""><br></span></div><div class="" id="LC607">
<span class="">(def-sql-op :order-by (form &rest fields)</span></div><div class="" id="LC608"><span class=""> (if fields</span></div><div class="" id="LC609"><span class=""> `("</span><span class="">(</span><span class="">" ,@(sql-expand form) "</span> <span class="">ORDER</span> <span class="">BY</span> <span class="">" ,@(sql-expand-list fields) "</span><span class="">)</span><span class="">")</span></div>
<div class="" id="LC610"><span class=""> `("</span><span class="">(</span> <span class="">ORDER</span> <span class="">BY</span> <span class="">" ,@(sql-expand form) "</span><span class="">)</span><span class="">")))</span></div>
<div class="" id="LC610"><span class=""><br></span></div><div class="" id="LC610"><span class=""><br></span></div><div class="" id="LC610"><span class="">Usage Examples:</span></div><div class="" id="LC610"><span class="">Over Examples generally following <a href="http://www.postgresql.org/docs/9.3/static/tutorial-window.html">http://www.postgresql.org/docs/9.3/static/tutorial-window.html</a></span></div>
<div class="" id="LC610"><span class=""><br></span></div><div class="" id="LC610"><span style="font-family:arial">(query (:select 'salary (:over (:sum 'salary))</span></div><div class="" id="LC610"><span style="font-family:arial"> :from 'empsalary))</span></div>
<div class="" id="LC610"><span style="font-family:arial"><br></span></div><div class="" id="LC610"><pre><div class="" id="LC459">(query (:select 'depname 'empno 'salary</div><div class="" id="LC460"> (:over (:avg 'salary)</div>
<div class="" id="LC461"> (:partition-by 'depname))</div><div class="" id="LC462"> :from 'empsalary))<span class=""></span></div><div class="" id="LC462"><br></div><div class="" id="LC462">
<pre><div class="" id="LC468">(query (:select 'depname 'empno 'salary</div><div class="" id="LC469"> (:over (:rank)</div><div class="" id="LC470"> (:partition-by-order-by 'depname (:desc 'salary)))</div>
<div class="" id="LC471"> :from 'empsalary))</div><div class="" id="LC471"><br></div><div class="" id="LC471"><pre><div class="" id="LC476">(query (:select (:over (:sum 'salary) 'w)</div><div class="" id="LC477">
(:over (:avg 'salary) 'w)</div><div class="" id="LC478"> :from 'empsalary :window</div><div class="" id="LC479"> (:as 'w (:partition-by-order-by 'depname</div><div class="" id="LC480">
(:desc 'salary)))))<span class=""></span></div></pre></div></pre></div><div class="" id="LC462"><br></div><div class="" id="LC462">With Examples </div><div class="" id="LC462">
<span style="font-family:arial"><br></span></div><div class="" id="LC462"><span style="font-family:arial">(query (:with (:as 'upd</span><br></div><div class="" id="LC462"><pre><div class="" id="LC491"> (:parens</div>
<div class="" id="LC492"> (:update 'employees :set 'sales-count (:+ 'sales-count 1)</div><div class="" id="LC493"> :where (:= 'id</div><div class="" id="LC494"> (:select 'sales-person</div>
<div class="" id="LC495"> :from 'accounts</div><div class="" id="LC496"> :where (:= 'name "Acme Corporation")))</div>
<div class="" id="LC497"> :returning '*)))</div><div class="" id="LC498"> (:insert-into 'employees-log</div><div class="" id="LC499"> (:select '* 'current-timestamp :from</div>
<div class="" id="LC500"> 'upd))))<span class=""></span></div><div class="" id="LC500"><br></div><div class="" id="LC500">With-Recursive Examples following <a href="http://www.postgresql.org/docs/current/static/queries-with.html">http://www.postgresql.org/docs/current/static/queries-with.html</a></div>
<div class="" id="LC500"><br></div><div class="" id="LC500"><pre><div class="" id="LC509">(query (:with-recursive</div><div class="" id="LC510"> (:as (:t1 'n)</div><div class="" id="LC511"> (:union-all (:values 1)</div>
<div class="" id="LC512"> (:select (:+ 'n 1)</div><div class="" id="LC513"> :from 't1</div><div class="" id="LC514"> :where (:<span class=""><</span> 'n 100))))</div>
<div class="" id="LC515"> (:select (:sum 'n) :from 't1)))</div><div class="" id="LC515"><br></div><div class="" id="LC515"><pre><div class="" id="LC517">(query (:with-recursive</div><div class="" id="LC518"> (:as (:included_parts 'sub-part 'part 'quantity)</div>
<div class="" id="LC519"> (:union-all</div><div class="" id="LC520"> (:select 'sub-part 'part 'quantity</div><div class="" id="LC521"> :from 'parts</div><div class="" id="LC522">
:where (:= 'part "our-product"))</div><div class="" id="LC523"> (:select 'p.sub-part 'p.part 'p.quantity</div><div class="" id="LC524"> :from (:as 'included-parts 'pr)</div>
<div class="" id="LC525"> (:as 'parts 'p)</div><div class="" id="LC526"> :where (:= 'p.part 'pr.sub-part) )))</div><div class="" id="LC527"> (:select 'sub-part (:as (:sum 'quantity) 'total-quantity)</div>
<div class="" id="LC528"> :from 'included-parts</div><div class="" id="LC529"> :group-by 'sub-part)))</div><div class="" id="LC530"><br></div><div class="" id="LC531">(query (:with-recursive</div>
<div class="" id="LC532"> (:as (:search-graph 'id 'link 'data 'depth)</div><div class="" id="LC533"> (:union-all (:select '<a href="http://g.id">g.id</a> 'g.link 'g.data 1</div><div class="" id="LC534">
:from (:as 'graph 'g))</div><div class="" id="LC535"> (:select '<a href="http://g.id">g.id</a> 'g.link 'g.data (:+ 'sg.depth 1)</div><div class="" id="LC536">
:from (:as 'graph 'g) (:as 'search-graph 'sg)</div><div class="" id="LC537"> :where (:= '<a href="http://g.id">g.id</a> 'sg.link))))</div>
<div class="" id="LC538"> (:select '* :from 'search-graph)))</div><div class="" id="LC539"><br></div><div class="" id="LC540">(query (:with-recursive</div><div class="" id="LC541"> (:as (:search-graph 'id 'link 'data'depth 'path 'cycle)</div>
<div class="" id="LC542"> (:union-all</div><div class="" id="LC543"> (:select '<a href="http://g.id">g.id</a> 'g.link 'g.data 1</div><div class="" id="LC544"> (:[] 'g.f1 'g.f2) nil</div>
<div class="" id="LC545"> :from (:as 'graph 'g))</div><div class="" id="LC546"> (:select '<a href="http://g.id">g.id</a> 'g.link 'g.data (:+ 'sg.depth 1)</div><div class="" id="LC547">
(:|| 'path (:row 'g.f1 'g.f2))</div><div class="" id="LC548"> (:= (:row 'g.f1 'g.f2)</div><div class="" id="LC549"> (:any* 'path))</div>
<div class="" id="LC550"> :from (:as 'graph 'g)</div><div class="" id="LC551"> (:as 'search-graph 'sg)</div><div class="" id="LC552"> :where (:and (:= '<a href="http://g.id">g.id</a> 'sg.link)</div>
<div class="" id="LC553"> (:not 'cycle)))))</div><div class="" id="LC554"> (:select '* :from 'search-graph)))<span class=""></span></div></pre></div></pre></div></pre></div>
<div class="" id="LC462"><br></div><div class="" id="LC462"><br></div></pre></div></pre></div>