Hey Julian! To double check, you'd like this syntax converted into working PostgreSQL? What are you trying to achieve?
Would something like this help get you closer? I didn't incorporate your for loop, but I believe you might need to write that logic elsewhere (perhaps, in a JS query that runs the JS for loop and then triggers the necessary SQL query. You can pass in dynamic values/each loop item using additionalScope).
WITH inserted_prompt AS (
INSERT INTO prompt_table (prompt, nb_prompt_param, language)
VALUES (
{{txtPrompt.value}},
{{parseInt(numberParametres.value)}},
(SELECT id FROM language_table WHERE language = {{selectLanguage.value}})
)
RETURNING id
)
INSERT INTO prompt_category (prompt_id, category_id)
SELECT id, category_table.id
FROM inserted_prompt
CROSS JOIN UNNEST({{selectCategory.value}}) AS category
JOIN category_table ON category_table.category = category;