Combining a query and a manipulation in with?

[magthe]

I'm trying to translate the following SQL into squeal

with
  vals as (values (4, 5, 1.2))
insert into recipes (proc_id, prod_id, amount)
  select *
    from vals
           on conflict (proc_id, prod_id) do
           update set amount = excluded.amount
returning id

I thought this would be the equivalent using squeal

    with
    (values_ (4 `as` #proc_id :* 5 `as` #ingr_id :* 1.2 `as` #amount) `as` #vals)
    (insertInto #recipes
       (Select (Default `as` #id :*
                Set (#vals ! #proc_id) `as` #proc_id :*
                Set (#vals ! #ingr_id) `as` #prod_id :*
                Set (#vals ! #amount) `as` #amount)
          (from (common #vals)))
       (OnConflict (OnConstraint #uq_recs_ids)
          (DoUpdate (Set (#excluded ! #amount) `as` #amount) []))
       (Returning_ #id))

but I seem to get stuck on the two parts not being of the same type, vals is a query and the insert ... is a manipulation.

How do I combine a query and a manipulation in a with?

[magthe]

Nevermind, I managed to find queryStatement and turned my query into a manipulation that way.

[magthe]

However, withRecursive seems to only be defined for queries, does that mean I can't use it with manipulations at all?

[magthe]

It's not a big deal though, as the recursive query can be turned into a sub-query... but sometimes a with recursive <query> <manipulation> is a bit clearer.

[echatav]

You can still turn the recursive query into a manipulation with queryStatement

[magthe]

But withRecursive doesn't accept a manipulation, so how would that work?

[echatav]

That's fair. Maybe withRecursive should be extended to the whole With class.

[magthe]

It's something worth pondering, but as I wrote it's not a big deal. Since squeal allows me to define part of an SQL expression as separate values/functions (in a where clause) and then compose them it usually turns out readable enough.. much more so than the resulting SQL expression at least 😁