Adding a LEFT JOIN on a INSERT INTO....RETURNING

Question

My query Inserts a value and returns the new row inserted

INSERT INTO 
     event_comments(date_posted, e_id, created_by, parent_id, body, num_likes, thread_id)
     VALUES(1575770277, 1, '9e028aaa-d265-4e27-9528-30858ed8c13d', 9, 'December 7th', 0, 'zRfs2I')

 RETURNING comment_id, date_posted, e_id, created_by, parent_id, body, num_likes, thread_id

I want to join the created_by with the user_id from my user's table.

SELECT * from users WHERE user_id = created_by

Is it possible to join that new returning row with another table row?

WHy dont you just add the created_by to the comments table? – zip Dec 08 '19 at 04:16 — zip, Dec 08 '19 at 04:16
sorry what do you mean – WHOATEMYNOODLES Dec 08 '19 at 04:17 — WHOATEMYNOODLES, Dec 08 '19 at 04:17

msg45f · Accepted Answer · 2019-12-08T04:30:20.803

Consider using a WITH structure to pass the data from the insert to a query that can then be joined.

Example:

-- Setup some initial tables
create table colors (
    id SERIAL primary key,
    color VARCHAR UNIQUE
);

create table animals (
    id SERIAL primary key,
    a_id INTEGER references colors(id),
    animal VARCHAR UNIQUE
);

-- provide some initial data in colors
insert into colors (color) values ('red'), ('green'), ('blue');

-- Store returned data in inserted_animal for use in next query
with inserted_animal as (
    -- Insert a new record into animals
    insert into animals (a_id, animal) values (3, 'fish') returning *
) select * from inserted_animal 
     left join colors on inserted_animal.a_id = colors.id;

-- Output
-- id | a_id | animal | id | color
-- 1  | 3    | fish   | 3  | blue

Explanation: A WITH query allows a record returned from an initial query, including data returned from a RETURNING clause, which is stored in a temporary table that can be accessed in the expression that follows it to continue work on it, including using a JOIN expression.

Exactly what I need! Plus one! Thank you – Lyka Jan 15 '20 at 02:15 — Lyka, Jan 15 '20 at 02:15

score 0 · Answer 2 · answered Dec 08 '19 at 04:24

You were right, I misunderstood

This should do it:

DECLARE mycreated_by event_comments.created_by%TYPE;

INSERT INTO 
     event_comments(date_posted, e_id, created_by, parent_id, body, num_likes, thread_id)
     VALUES(1575770277, 1, '9e028aaa-d265-4e27-9528-30858ed8c13d', 9, 'December 7th', 0, 'zRfs2I')

 RETURNING created_by into mycreated_by 

SELECT * from users WHERE user_id = mycreated_by

Adding a LEFT JOIN on a INSERT INTO....RETURNING

2 Answers2

Linked