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