Is there a better way to write "common table expressions " (with and with recursive) than:
(def-sql-op :with (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH " ,@(sql-expand-list x) ,@(sql-expand (car y)))))
(def-sql-op :with-recursive (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH RECURSIVE " ,@(sql-expand-list x) ,@(sql-expand (car y)))))
They work for me, but I have the feeling there is a better way to do it.
Actual use would be something like the following (using the example from the postgresql website):
(defun test2 () (query (:with-recursive (:as 'regional-sales (:select 'region (:as (:sum 'amount) 'total-sales) :from 'orders :group-by 'region)) (:as 'top-regions (:select 'region :from 'regional-sales :where (:> 'total-sales (:select (:/ (:sum 'total-sales) 10) :from 'regional-sales)))) (:select 'region 'product (:as (:sum 'quantity) 'product-units) (:as (:sum 'amount) 'product-sales) :from 'orders :where (:in 'region (:select 'region :from 'top-regions)) :group-by 'region 'product))))
Sabra