Postmodern Examples Table of Contents Table Management


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

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


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