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