postmodern-complex-examples
[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 ownerwhere 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)))