Cannot manage to take ids returned from INSERT and insert into another table

Question

I have these 3 tables

recipe: recipe_id | name

ingredient: ingredient_id | name

recipes_ingredients: id | recipe_id | ingredient_id

The first id of every table is a SERIAL PRIMARY KEY and the two names are character varying(50). I'm trying to insert in the third table recipe_id and ingredient_id using RETURNING but it doesn't work. I already tried the three INSERT individually and they work perfectly, the problem seems to happen when i put altogether using the WITH or it cannot takes the returned ids.

Here's my SQL:

BEGIN;  -- start transaction
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('{}') RETURNING recipe_id
)
WITH new_ingredient AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
)
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)  VALUES (new_recipe.recipe_id, new_ingredient.ingredient_id)
COMMIT; -- end transaction

This is the error I get:

ERROR:  syntax error at or near "WITH"
LINE 5: WITH new_ingredient AS (
        ^
SQL state: 42601
Character: 117

I already checked other similar question on stackoverflow and it seems to me that I used the exact same question. So I can't understand where the error is.

Answer 1

If you want to write multiple common table expressions, the WITHkeyword is only needed once. The individual parts are separated by commas. But you can't reference the CTEs without using a SELECT, so the final INSERT needs to use a SELECT clause, not a VALUES clause:

WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('{}') RETURNING recipe_id
), new_ingredient AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
)
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)  
select new_recipe.recipe_id, new_ingredient.ingredient_id
from new_recipie
  cross join new_ingredient;
;

Another option is to use the currval() function together with pg_get_serial_sequence() then you don't need the CTE at all:

INSERT INTO recipe (name) VALUES ('{}');
INSERT INTO ingredient (name) VALUES ('{}');
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)  
values (
        currval(pg_get_serial_sequence('recipe', 'recipe_id')), 
        currval(pg_get_serial_sequence('ingredient', 'ingredient_id'))
       );