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

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.

CodePudding user response：

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'))
       );