postmodern-i (insert, insert-into, intersect)

[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]

ilike See like, ilike


(query (:select 'name :from 'countries :where (:in 'id (:set 20 21 23))))

Insert, Insert-into

First, without s-sql

(query "insert into 'countries values (920 'Oz' 'Oz Dollars')")

With s-sql

Now using s-sql. Notice there are two ways. First directly setting the columns, using the :set keyword and alternating column names and values. This means that the :insert-into clause expects the arguments to :set to be in multiples of two. Odd arguments will trigger an error.

(query (:insert-into 'countries :set 'id 920 'name "Oz" 'currency "Oz Dollars"))

Notice that a list with two items is a single item, not a multiple of two. You can use sql-compile to deal with this. See this example from the mailing list.

(sql-compile `(:insert-into 'kupci :set ,@(when t (list 'ime "a"))))

The second method, without the :set keyword, uses :select, followed by values.

(query (:insert-into 'countries (:select "Oz" "Oz Dollars")))

Returning the Primary Key

Suppose your table has a serial key of id and you want the insert function to return the newly generated id for that new record.
(postmodern:query (:insert-into 'categories :set 'name "test-cat3" :returning 'id) :single)
The next example shows the same example using parameterized variables.
(let ((name "test-cat4")) (postmodern:query (:insert-into 'categories :set 'name '$1 :returning 'id) name :single))

Note: This is a postgresql extension to the standard From the Documentation: 

INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability to use WITH with INSERT, and the ability to specify an alternative action with ON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard.

The SQL standard specifies that OVERRIDING SYSTEM VALUE can only be specified if an identity column that is generated always exists. PostgreSQL allows the clause in any case and ignores it if it is not applicable.

With DAOs

Postmodern has an update-dao and an insert-dao function. My tendency is to check and see if the dao that I'm currently dealing with has its primary key set (in which case I am editing an existing object and need to use update-dao. If not, then I'm dealing with a brand new object and will need to set the primary key and use the insert-dao method, using something like this:

(if (id item) (postmodern:update-dao item) (progn (setf (id item) (get-next-id class-name)) (postmodern:insert-dao item)))

Multiple Row Inserts

The question was asked on the postmodern mailing list whether there is some way in S-SQL to generate INSERT INTO clauses with multiple rows, e.g. "INSERT INTO \"table\" (a, b) VALUES (10, 20), (30, 40)" ? Desired sexp format is `(:insert-into table :set a (10 20) b (30 40)),

The answer was that the functionality already exists, albeit undocumented.

(:insert-rows-into 'table :columns 'a 'b :values '((10 20) (30 40)))

Variations on the input syntax

Insert into does not cover all the use cases. You can either use it with a select form, as in:

(query (:insert-into 'test2 (:select '* :from 'test1)))
or with set and alternating field names and values, as in:
(:insert-into 'my-table :set 'field-1 42 'field-2 "foobar")


What happens if you want to insert from an alist? I usually find alists easier to use in this area than plists, but sql-compile has a better solution for plists so long as you can intern the keys (see below). 


What happens if you want to insert from a plist? 
Assume you have a plist where the keys are interned that you want to insert as a record. Consider the following:
(query (sql-compile (append `(:insert-into ,table :set) plst)))

That gives you the opportunity to generalize into something like this:

(defun insert-db-from-plist (table plst)
  "Takes a table and a plist and inserts the plist into the table as a new record."
  (when (stringp table)
    (setf table (intern (string-upcase table))))
     (with-connection (db)
                        (append `(:insert-into ,table :set)
                                  (loop for x in plst counting x into y collect
                                        (if (oddp y)
                                            (cond ((symbolp x)
                                                  ((stringp x)
                                                   (intern (string-upcase x)))
                                                  (t nil))


Intersect produces a result contain rows that appear on all the sub-selects.

(query (:intersect (:select ' :from 'countries :where (:< 'latitude 16.44)) (:select ' :from 'countries 'regions :where (:and (:= 'region-id ' (:= ' "Caribbean"))))) (("Aruba") ("Netherlands Antilles") ("Grenada") ("Barbados") ("Trinidad and Tobago"))