-
How do I get the columns' default values when I'm inserting based on a sub-query? In the schema I have I'd use this SQL to create a row each in with proc as
(insert into processes (type)
values ('farm')
returning id)
insert into products (proc_id, eid, name)
select id, ($1 :: uuid), ($2 :: text)
from proc trying to translate that into squeal I've got this so far sql = with
(insertInto #processes
(Values_ (Default `as` #id :*
Default `as` #eid :*
Default `as` #generation :*
Set "farm" `as` #type :*
Default `as` #extras))
OnConflictDoRaise
(Returning_ #id) `as` #proc)
(insertInto_ #products
(Subquery (select_ (undefined `as` #id :*
(param @1) `as` #eid :*
(param @2) `as` #name :*
#proc ! #id `as` #proc_id :*
undefined `as` #alloc)
(from (common #proc))))) AFAIU there's no way getting around giving values for all columns (right???), and that's easy for the |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 10 replies
-
Instead of Here's an example: >>> :{
let
insertUser :: Statement DB User ()
insertUser = manipulation $ with (u `as` #u) e
where
u = insertInto #users
(Values_ (Default `as` #id :* Set (param @1) `as` #name))
OnConflictDoRaise (Returning_ (#id :* param @2 `as` #email))
e = insertInto_ #emails $ Select
(Default `as` #id :* Set (#u ! #id) `as` #user_id :* Set (#u ! #email) `as` #email)
(from (common #u))
:} |
Beta Was this translation helpful? Give feedback.
-
Oh, that's very nice. The generated SQL is surprisingly short too as all the |
Beta Was this translation helpful? Give feedback.
Instead of
Subquery
useSelect
. That will let you useDefault
in your subquery.https://hackage.haskell.org/package/squeal-postgresql-0.9.1.3/docs/Squeal-PostgreSQL-Manipulation-Insert.html#t:QueryClause
Here's an example: