postmodern-arrays

Postmodern Examples Table of Contents Table Management

Arrays

Per the Postgresql documentation, an array data type is named by appending square brackets ([]) to the data type name of the array elements. An alternative syntax, which conforms to the SQL standard by using the keyword ARRAY, can be used for one-dimensional arrays. Note: Postmodern currently does not support the keyword ARRAY.

Standard sql select statements for arrays would look something like this:

(query "select stuff2[1][1] from test2 where id=5")
((83))

(query "select stuff2[1:2][1] from test2 where id=5") 

((#2A((83)(16))))

Running it from the psql command line returns:

 select stuff2[1:2][1] from test2 where id=5;    stuff2    -------------  {{83},{16}}

Creating a sample table with arrays in s-sql might look like this:

(query (:create-table test2 ((id :type serial)
                             (name :type text)
                             (stuff1 :type int4[])
                             (stuff2 :type int4[]))))

WARNING: Postgres warning: CREATE TABLE will create implicit sequence "test2_id_seq" for serial column "test2.id" NIL

Inserting into the table could look like this:

(query (:insert-into 'test2
                     :set 'name "Jeff"
                     'stuff1 #(22 24 21 20)
                     'stuff2 #(#(2 4) #(6 7))))

Inserting an additional value into the array could look like this:

(postmodern:query (:update 'packages
                           :set 'exported-functions (:array-append 'exported-functions "test-func")
                           :where (:= 'name "library-comparison")))

Remember in Postgresql that arrays start with an index of 1, not 0. Selecting could look like this:

(query (:select (:[] 'stuff1 1) :from 'test2 :where (:= 'name "Jeff")) :single) 22
(query (:select (:[] 'stuff2 1 1) :from 'test2
                :where (:= 'name "Jeff"))
       :single)
#2A((2 4))

You can use strings to get to the same place. Assume you have list that you want to insert into an database record array field, you can convert the list into a string that postgresql will read as inputs into an array with something like this:

(defun list-to-postgresql-array-string (lst)
  (if (> (length lst) 0)
      (format nil "{~{\"~a\"~^, ~}}" lst)
      :NULL))

Array_agg Function

Suppose you want 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)))

Text Searching and Indexing