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[])
                             (stuff1 :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))))

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

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

Comments