Can someone help me with this query please? I'm pretty new and I know that I can't do this because I'm mixing different languages but I hope someone can help me to correct this code:
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
)
IF ({{selectCategory.value.length}} > 0) THEN
FOR (i = 0; i < {{selectCategory.value.length}}; i++)
INSERT INTO prompt_category (prompt_id, category_id)
VALUES ((SELECT id FROM inserted_prompt), (SELECT id FROM category_table WHERE category_table.category = {{selectCategory.value[i]}}))
END FOR
END IF
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;