I have tried passing a clojure vector, also tried the following format:
-- name: insert-into-sometable<!
-- inserts in the sometable the lid and uids
INSERT INTO sometable
(lid, uids) values(:lid, ARRAY[:uids])
But both the methods throws data mismatch error.
I think if I can call the postgres array functions from the query file then update and insert can be done easily. Please help.
Thanks in advance
The error message of your 2nd try gives a subtile hint:
AssertionError Assert failed: Query argument mismatch.
Expected keys: (:uids])
Actual keys: (:uids)
Missing keys: (:uids])
Apparently things go south when yesql tries to parse the :uids key as it appends the closing bracket of the array definition. Lets try something else:
-- name: insert-into-sometable<!
-- inserts in the sometable the lid and uids
INSERT INTO sometable
(lid, uids) values(:lid, ARRAY[ :uids ])
Notice the extra spaces between :uids and the array brackets.
=> (insert-into-sometable<! {:lid 1, :uids [1 2 42])
;; => 1
Looks like a bug in yesql to me, :uid] should never get parsed as a valid key.
Edit: Was about to file a bug with yesql, but it's already fixed with the recently released 0.5.2 version.
SQL Arrays are not supported by yesql, but clojure.java.jdbc offers an extension point in the ISQLParameter protocol, which you can use as:
(deftype StringArray [items]
clojure.java.jdbc/ISQLParameter
(set-parameter [_ stmt ix]
(let [as-array (into-array Object items)
jdbc-array (.createArrayOf (.getConnection stmt) "text" as-array)]
(.setArray stmt ix jdbc-array))))
(insert-into-sometable<! db-spec "hi" (->StringArray ["one" "two"]))
Note that you cannot extend Clojure's vectors as vectors has a special meaning in yesql
