[
Special Characters][
A] [
B] [
C] [
D] [
E] [
F] [
G] [
H] [
I] [
J] [
K] [
L] [
M] [
N] [
O] [
P] [
Q] [
R] [
S] [
T] [
U] [
V] [
W] [
X] [
Y] [
Z]
sql-op :+, :*, :%, :&, :|, :||, :=, :/, :!=, :<, :<>, :>, :<=, :>=, :^,
*, /, , <, >, and, any, as, avg, between, case, coalesce, commit, constraints, count, current-date, current-timestamp, datatypes, date-part,date-trunc,desc,
distinct, distinct-on, except,
:except-all
exists, group-by, having, in, insert,
:intersect, :intersect-all,
is-null, join cross-join,
join inner-join, join left-join,
join outer-join,
like, limit, not,now, null, on, or, order-by, raw, rollback, select, set, some, sum, transactions,
truncate,
union,
union-all,
unique, update, using, when, while
More Complicated Examples
The following functions generate information about the database currently in use. They are here as examples of slightly more complicated queries using s-sql. The first query function generates information on the foreign key
constraints in a database. Ignoring all the postgresql internal table
names, while this looks complicated, the only additional items really
here are :[] as the op for getting an array index, :generate-series,
and :array-upper
(defun describe-foreign-key-constraints ()
"Generates a list of lists of information on the foreign key constraints"
(query (:order-by (:select 'conname
(:as 'conrelid 'table)
(:as 'pgc.relname 'tabname)
(:as 'a.attname 'columns)
(:as 'confrelid 'foreign-table)
(:as 'pgf.relname 'ftabname)
(:as 'af.attname 'fcolumn)
:from
(:as 'pg_attribute 'af)
(:as 'pg_attribute 'a)
(:as 'pg_class 'pgc)
(:as 'pg_class 'pgf)
(:as
(:select 'conname 'conrelid 'confrelid
(:as (:[] 'conkey 'i) 'conkey)
(:as (:[] 'confkey 'i) 'confkey)
:from (:as (:select 'conname
'conrelid 'confrelid
'conkey 'confkey
(:as
(:generate-series '1
(:array-upper 'conkey 1))
'i)
:from 'pg_constraint
:where (:= 'contype "f" ))
'ss)
) 'ss2)
:where (:and (:= 'af.attnum 'confkey)
(:= 'af.attrelid 'confrelid)
(:= 'a.attnum 'conkey)
(:= 'a.attrelid 'conrelid)
(:= 'pgf.relfilenode 'confrelid)
(:= 'pgc.relfilenode 'conrelid)))
'ftabname 'fcolumn 'tabname 'columns)))
(defun list-schema-table-type-owner (relkind-type)
"Returns a list of lists showing the schema, the name, the type and the owner
where relkind-type is a list of strings where the strings are: c,r,v,i,S,c,t or f"
(query (:order-by (:select (:as 'n.nspname 'Schema)
(:as 'c.relname 'Name)
(:as (:case ((:= 'c.relkind "r") "Table")
((:= 'c.relkind "v") "view")
((:= 'c.relkind "i") "index")
((:= 'c.relkind "S") "sequence")
((:= 'c.relkind "c") "composite")
((:= 'c.relkind "t") "TOAST")
((:= 'c.relkind "f") "foreign"))
'Type)
(:as 'u.usename 'Owner)
(:as (:/ (:pg_total_relation_size 'c.oid) 1000) 'Size)
(:as 'c.reltuples 'Records)
(:as 'c.relhasindex 'Indexed)
(:as 'c.relchecks 'Constraints)
(:as 'c.relhastriggers 'Triggers)
(:as (:pg_size_pretty (:pg_total_relation_size 'c.oid)) 'Size)
:from (:as 'pg-catalog.pg-class 'c)
:left-join (:as 'pg-catalog.pg-user 'u)
:on (:= 'u.usesysid 'c.relowner)
:left-join (:as 'pg-catalog.pg-namespace 'n)
:on (:= 'n.oid 'c.relnamespace)
:where (:and (:in 'c.relkind (:set relkind-type ""))
(:not-in 'n.nspname (:set "pg_catalog" "pg-toast"))
(:pg-catalog.pg-table-is-visible 'c.oid)))
1 2)))
(defun describe-views ()
"Describe the current views in schema public"
(query
(:order-by
(:select 'c.oid 'c.xmin 'c.relname
(:as (:pg_get_userbyid 'c.relowner) 'viewowner)
'c.relacl 'description
(:as (:pg_get-viewdef 'c.oid 't) 'code)
:from (:as 'pg_class 'c)
:left-join (:as 'pg_description 'des)
:on (:and (:= 'des.objoid 'c.oid)
(:= 0 'des.objsubid))
:left-join (:as 'pg_catalog.pg_namespace 'n)
:on (:= 'n.oid 'c.relnamespace)
:where (:and (:or (:and 'c.relhasrules
(:exists
(:select 'r.rulename
:from (:as 'pg_rewrite 'r)
:where (:and (:= 'r.ev_class 'c.oid)
(:= (:bpchar 'r.ev_type)
(:type "I" bpchar))))))
(:= 'c.relkind (:type "v" char)))
(:= 'n.nspname "public")))
'relname)))