[SOLVED] SQL INSERT/SELECT using WITH clause

SQL INSERT/SELECT using WITH clause

I'm trying to make a copy from two tables based on a list of IDs, those IDs are retrieved from an initial table, then those IDs are used into an INSERT SELECT statement, also the created IDs from the previous INSERT must be inserted into a third table:

BEGIN;

WITH dog_tmp AS (SELECT id FROM dog WHERE toy_id = '12345'),
meal_tmp as (INSERT INTO meal (id, dog_id, date_created, type)
    SELECT public.uuid_generate_v4(), dt.id, m.date_created, m.type
    FROM meal AS m
    JOIN dog_tmp dt ON dt.id = m.dog_id RETURNING m.id AS meal_uuid)

INSERT INTO dog_diet (id, meal_id, date_created)
SELECT public.uuid_generate_v4(), mt.meal_uuid, dd.date_created
FROM meal_tmp mt
         JOIN dog_diet dd ON dd.thread_id = mt.meal_uuid;

COMMIT;

I'm getting this error: [42P01] ERROR: missing FROM-clause entry for table "m"

Thanks in advance for your hints about this, or if possible another approach.

Solution

Quick answer: change RETURNING m.id AS meal_uuid to RETURNING id AS meal_uuid (db fiddle).

So the full statement at issue is:

INSERT INTO meal (id, dog_id, date_created, type)
    SELECT public.uuid_generate_v4(), dt.id, m.date_created, m.type
    FROM meal AS m
    JOIN dog_tmp dt ON dt.id = m.dog_id 
RETURNING m.id AS meal_uuid

Within the select you set m as an alias for meal but the scope of this is the SELECT query (so m is not defined within the outer insert statement).