S-SQL and Postgresql Functions

Postgresql Functions

Postgresql functions are typically inserted into s-sql in their own form prefaced with : and their parameters are generally single quoted. Here are a few simple examples.

:avg

(froundn (query (:select (:avg 'latitude) :from 'countries) :single) 4)

18.4209

:generate-series

Generate-series returns a list of of lists of numbers with a starting point of x, and ending point of y and an interval of z (defaulting to 1). Thus:

(query (:select '* :from (:generate-series 1 10)))

((1) (2) (3) (4) (5) (6) (7) (8) (9) (10))

(query (:select '* :from (:generate-series 1 30 5)))

((1) (6) (11) (16) (21) (26))

(query (:select '* :from (:generate-series 3 30 5)))

((3) (8) (13) (18) (23) (28))

:max

(query (:select (:max 'latitude) :from 'countries)
       :single)

72

:min

(query (:select (:min 'latitude) :from 'countries)
       :single)

-1029/20

:random

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

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

:string_agg

String_agg returns a string containging the values returned, separated by a delimiter. In the following example, we are searching for the name of all the regions in the regions table and we want it all returned as a single string with a delimiting comma.

(query (:select (:string_agg 'name ",")
        :from 'regions))

 (("Central America,Middle East,North America,South America,Central Asia,Pacific,Caribbean,Eastern Europe,Western Europe,EMEA,APAC,LATAM,Emerging,US,Canada,Africa,All,Asia,Eastern Africa,Middle Africa,Northern Africa,Southern Africa,Western Africa,Oceania,Northern Europe,Southern Europe,Eastern Asia,South Central Asia,South East Asia"))

:version

(query (:select (:version)))

(("PostgreSQL 9.2 on x86_64-pc-linux-gnu, compiled by GCC x86_64"))

(defun table-size (table-name)
  "Return the size of a postgresql table in k or m. Table-name can be either astring or quoted."
  (when (symbolp table-name)
    (setf table-name  (string-downcase (write-to-string table-name))))
  (query (:select (:pg_size_pretty (:pg_total_relation_size '$1)))
         :single
         table-name))

(table-size 'countries)

"88 kB"

Combining Postgresql Functions

(defun current-database-size-pretty ()
  "Returns the current database size as a string in MB"
  (query (:select (:pg_size_pretty
                   (:pg_database_size (:current_database))))
         :single))

S-SQL Examples Home Page