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

Text Searching and Indexing

Comments

SabraOnTheHill	Search this site