postmodern-o (on, on-conflict, 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 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)))

On Conflict

Some people use the term "upsert" for trying to insert a new row, but if that record already exists, then either update the row with new values or do nothing (as opposed to throwing an error).

Beginning in postgresql versions 9.5 and above, it is possible to use what postgresql calls on-conflict. There are two versions - "on conflict do nothing" or "on conflict update". See below for sample call in postmodern for on-conflict-update.

This capability was added to postmodern in a commit on 28 Jan 2018.

(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2

                     :on-conflict-update 'column-A

                     :update-set 'column-B '$2

                     :where (:= 'test-table.column-A '$1)) "c" 37)

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. It is important to note that there are two uses of order-by. First is a wrapper around a select statement, effectively sorting the data returned by the select statement.:

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

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

Order-by within an aggregation operator

The second use of order-by is as a keyword parameter to an aggregation operator.

Since it is part of the aggregation operator rather than sorting the result of the aggregation operator, it is not a wrap around form as seen above.

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