postmodern-i (insert, insert-into, intersect)
ilike See like, ilike
In
(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.
(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")) (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. You could check and see if the dao that you are currently dealing with has its primary key set (in which case you are editing an existing object and need to use update-dao. If not, then you 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)))However, you could also just use upsert.
(postmodern:upsert-dao item)Multiple Row Inserts
You can use s-sql to generate INSERT INTO clauses with multiple rows, e.g. "INSERT INTO \"table\" (a, b) VALUES (10, 20), (30, 40)" ? (This ability does not yet exist with respect to inserting multiple daos.
(: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")Alist
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).
Plist
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) (query (sql-compile (append `(:insert-into ,table :set) (loop for x in plst counting x into y collect (if (oddp y) (cond ((symbolp x) x) ((stringp x) (intern (string-upcase x))) (t nil)) x)))))))Intersect
Intersect produces a result contain rows that appear on all the sub-selects.
(query (:intersect (:select 'countries.name :from 'countries :where (:< 'latitude 16.44)) (:select 'countries.name :from 'countries 'regions :where (:and (:= 'region-id 'regions.id) (:= 'regions.name "Caribbean")))))(("Aruba") ("Netherlands Antilles") ("Grenada") ("Barbados") ("Trinidad and Tobago"))