I have a query that does an insert into two tables at once with a 1-to-many relationship. I do this in a single transaction using a CTE
WITH new_question AS (
INSERT INTO exam_questions (profession, question_text)
VALUES ($1, $2)
RETURNING id
)
INSERT INTO exam_question_details (question_id, detail_text)
SELECT new_question.id, detail_text
FROM new_question, UNNEST($3::text[]) AS detail_text
RETURNING question_id, id
Using the UNNEST trick allows me to bulk enter the "many" part of the relationship. The code generated looks good as far as I can tell:
pub fn create_new_exam_question(db, arg_1, arg_2, arg_3) {
let decoder =
decode.into({
use question_id <- decode.parameter
use id <- decode.parameter
CreateNewExamQuestionRow(question_id: question_id, id: id)
})
|> decode.field(0, decode.optional(decode.int))
|> decode.field(1, decode.int)
"WITH new_question AS (
INSERT INTO exam_questions (profession, question_text)
VALUES ($1, $2)
RETURNING id
)
INSERT INTO exam_question_details (question_id, detail_text)
SELECT new_question.id, detail_text
FROM new_question, UNNEST($3::text[]) AS detail_text
RETURNING question_id, id
"
|> pgo.execute(
db,
[
pgo.text(arg_1),
pgo.text(arg_2),
pgo.array(list.map(arg_3, fn(a) {pgo.text(a)})), // <------ missing `gleam/list` import
],
decode.from(decoder, _),
)
}
but is missing the import of gleam/list import at the top of the generated file.
I have a query that does an insert into two tables at once with a 1-to-many relationship. I do this in a single transaction using a CTE
Using the
UNNEST
trick allows me to bulk enter the "many" part of the relationship. The code generated looks good as far as I can tell:but is missing the import of
gleam/list
import at the top of the generated file.