S-SQL Examples R

S-SQL Examples Home Page

A B C D E F G H I J K L M N O P R S T U V W Special Characters Calling Postgresql Stored Functions and Procedures

Random

(query (:limit
        (:order-by
         (:select 'id 'name
                  :from 'countries)
         (:random))
        5))

((54 "Israel") (62 "South Africa") (195 "Aruba") (79 "Costa Rica")(31 "OECD"))

Raw SQL Statements

We've already seen that the postmodern:query function can take a raw sql string and that the postmodern:sql function can output an sql string. You can combine them if you need to go beyond what postmodern can already provide. Consider the following toy example which uses a part of the eventual query in the let and inserts it into the body. 

(let ((param-constraints (sql (:= 'name "Austria"))))
  (query (:select 'id 'name
                  :from 'countries
                  :where (:raw param-constraints))))

Now consider this function where you have a conditional query based on the variables test1 and test2 passed into the function. In this toy example, if test1 is true then look for countries whose name is a fuzzy match for param1, if test2 is true, look for countries whose name is a fuzzy match for param2. If both are true, then the names need to be fuzzy matches for both param1 and param2. If neither test1 or test2 are true, then return all rows in the countries table. 

(defun test3 (test1 test2 param1 param2)
  (query (:select '* :from 'countries
          :where (:and
                  (:raw (if test1
                            (sql
                             (:like 'name
                                    (concatenate 'string "%" param1
                                                 "%")))
                            "'t'"))
                  (:raw (if test2
                            (sql (:like 'name
                                        (concatenate 'string "%"
                                                     param2
                                                     "%")))
                            "'t'"))))))

(test3 nil t "New" "gary")

((10 "Hungary" 11 47 20 "GU" 1 "2005-09-11 00:15:40-07" "Forint" "HUF" 348))

Returning the Primary Key

Suppose your table has a serial or identity key of id and you want the insert function to return the newly generated id for that new record. 

(query
 (:insert-into 'categories :set 'name "test-cat3"
               :returning 'id)
 :single)

The next example shows the same example using parameterized variables. 

(let ((name "test-cat4"))
  (query
   (:insert-into 'categories :set 'name '$1
                 :returning 'id)
   name :single))

Rollup

Rollup was added to postgresql in version 9.5. See https://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-GROUPING-SETS Sample usage: 

(query (:order-by
        (:select 'facid
                 (:as (:extract 'month 'starttime) 'month)
                 (:as (:sum 'slots) 'slots)
         :from 'cd.bookings
         :where (:and (:>= 'starttime "2012-01-01")
                      (:< 'starttime "2013-01-01"))
         :group-by (:rollup 'facid 'month))
        'facid 'month))