postmodern-o (or, order-by, order-by with limit and offset)

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

On

On is used in the join clauses. See the example below which returns a list of lists showing various types of items inside a database.

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

Or

(query (:select 'countries.name :from 'countries 'regions :where (:and (:or (:= 'regions.name "North America") (:= 'regions.name "Central America")) (:= 'regions.id 'countries.region-id)))) (("Belize") ("Bermuda") ("Canada") ("Costa Rica") ("El Salvador") ("Greenland") ("Guatemala") ("Mexico") ("Panama") ("US") ("Nicaragua"))

Order-by

Order-by does what it says on the label:

(query (:order-by (:select 'id 'name :from 'countries :where (:>= 'name "W")) 'name)) ((115 "Wallis") (141 "Zambia") (142 "Zimbabwe"))
If you want to change the default from ascending to descending, then the relevant column name is enclosed in a :desc term like so:
(query (:order-by (:select 'id 'name :from 'countries :where (:>= 'name "W")) (:desc 'name))) ((142 "Zimbabwe") (141 "Zambia") (115 "Wallis") )

Order By with Limit and offset

Note that :order-by has one or more parameters [Just one in this example] and :limit has 2 possible parameters, the limit and the offset. Note that the :order-by and :limit forms are wrapped around the :select form. The only difference between the two queries is the offset parameter.

(let ((list-limit 2) (offset 0)) (query (:limit (:order-by (:select 'countries.id 'countries.name :from 'countries) 'name) '$1 '$2) list-limit offset)) ((82 "Afghanistan") (130 "Albania")) 2 (let ((list-limit 2) (offset 2)) (query (:limit (:order-by (:select 'countries.id 'countries.name :from 'countries) 'name) '$1 '$2) list-limit offset)) ((140 "Algeria") (34 "All"))

Over

Over is available in postmodern as of the Oct 29 git updates. See also the examples for partition-by
(query (:select 'salary (:over (:sum 'salary)) :from 'empsalary))
Consider briefly at what "over" generates"
(sql (:over 'x 'y)) "(x OVER y)" (sql (:over 'x)) "(x OVER ()) " (sql (:over 'x 'y 'z)) "(x OVER y, z)"
So, if you wanted an sql statement such as:
(query "select x, array_agg(x) over (order by x) from generate_series(1, 3) as t(x);") ((1 #(1)) (2 #(1 2)) (3 #(1 2 3)))
The postmodern version would be:
(query (:select 'x (:over (:array-agg 'x) (:order-by 'x)) :from (:as (:generate-series 1 3) (:t 'x)))) ((1 #(1)) (2 #(1 2)) (3 #(1 2 3)))

Comments