sqlingvo
sqlingvo copied to clipboard
Enums and Multiple Insert
Firstly thanks for the lib =)...
Two quick questions, if I define an enum as:
(sql/create-type sql-db :gender
(sql/enum ["male" "female"]))
Is there no better api than:
(sql/insert sql-db :peep []
(sql/values [{:gender `(raw "'male'")}]))
#_=> ["INSERT INTO \"peep\" (\"gender\") VALUES ('male')"]
I don't see how else to insert enums, as they always appear to be inserted as strings, IE:
(sql/sql
(sql/insert sql-db :peep []
(sql/values [{:gender :male}])))
#_=> ["INSERT INTO \"peep\" (\"gender\") VALUES (\"male\")"]
Also if I wanted to use a sql statement inside an insert:
(sql/with sql-db
[:person (sql/insert sql-db :peep []
(sql/values [{:name "Peep 1"}])
(sql/returning :*))]
(sql/insert sql-db :peep []
(sql/values [{:name "Peep 2" :friend-id (sql/select sql-db [:id] (sql/from :person))}])))
#_=>
["WITH \"person\" AS (INSERT INTO \"peep\" (\"name\") VALUES (?) RETURNING *) INSERT INTO \"peep\" (\"friend-id\", \"name\") VALUES (SELECT \"id\" FROM \"person\", ?)"
"Peep 1"
"Peep 2"]
As you can see SELECT \"id\" FROM \"person\"
is not inside it's own expr ie: (SELECT \"id\" FROM \"person\")
, so it's not valid, the only way I can see to do this is:
(sql/with sql-db
[:person (sql/insert sql-db :peep []
(sql/values [{:name "Peep 1"}])
(sql/returning :*))]
(sql/insert sql-db :peep []
(sql/values [{:name "Peep 2" :friend-id `(raw ~(str "(" (first (sql/sql (sql/select sql-db [:id] (sql/from :person)))) ")"))}])))
#_=>
["WITH \"person\" AS (INSERT INTO \"peep\" (\"name\") VALUES (?) RETURNING *) INSERT INTO \"peep\" (\"friend-id\", \"name\") VALUES ((SELECT \"id\" FROM \"person\"), ?)"
"Peep 1"
"Peep 2"]
Which is pretty horrible, is there a better way?