I have a table, chat_rooms that has the following schema:
CREATE TABLE chat_rooms (
id integer GENERATED BY DEFAULT AS IDENTITY,
user_id uuid REFERENCES users(user_id) ON DELETE CASCADE,
CONSTRAINT chat_rooms_pkey PRIMARY KEY (id, user_id)
);
When a chat_room row is created, I want to insert rows for all the users inside chat_room, but I need the first generated id to use in the following inserts. Any ideas?
This can be a sql or plpgsql function as well as a query.
You design doesn't work. It makes no sense to have id as IDENTITY column, combined with a PK on (id, user_id).
CREATE TABLE chatroom (
chatroom_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
CREATE TABLE chatroom_user (
chatroom_id int REFERENCES chatroom ON DELETE CASCADE
, user_id int -- REFERENCES users ON DELETE CASCADE
, CONSTRAINT chatroom_user_pkey PRIMARY KEY (chatroom_id, user_id)
);
Then to insert an input array of existing users into the same, new chat room, use a data-modifying CTE:
WITH ins_chatroom AS (
INSERT INTO chatroom DEFAULT VALUES
RETURNING chatroom_id
)
INSERT INTO chatroom_user (chatroom_id, user_id)
SELECT chatroom_id, u
FROM ins_chatroom, unnest('{1,2,3}'::int[]) u -- input users as array
RETURNING *; -- just to show result in fiddle
See:
If you want to avoid the additional table for chatrooms, you can make do with a SEQUENCE, and good old sequence manipulation functions:
CREATE SEQUENCE IF NOT EXISTS public.chat_user_chat_id_seq;
CREATE TABLE chat_user (
chat_id int DEFAULT nextval('public.chat_user_chat_id_seq') -- optional default
, user_id int -- REFERENCES users ON DELETE CASCADE
, CONSTRAINT chat_rooms_pkey PRIMARY KEY (chat_id, user_id)
);
ALTER SEQUENCE public.chat_user_chat_id_seq OWNED BY chat_user.chat_id;
Then to insert an input array of existing users into the same, new chat room, use these two commands in the same session (better in the same transaction):
SELECT nextval('public.chat_user_chat_id_seq');
INSERT INTO chat_user (chat_id, user_id)
SELECT currval('public.chat_user_chat_id_seq'), unnest('{1,2,3}'::int[]) u
RETURNING *; -- just to show result in fiddle
See: