PostgreSQL中如何实现嵌套INSERT语句？

我有两张桌子，group and groupmembers。在插入行时group表，我还想插入两个值，groupid（来自组表的 ID）和userid（创建该组的用户的 ID）到groupmembers桌子。这些是表格：

CREATE TABLE groups (
  id SERIAL PRIMARY KEY NOT NULL,
  name CHARACTER VARYING(255) NOT NULL,
  creator CHARACTER VARYING(255) NOT NULL,
  role CHARACTER VARYING(100) NOT NULL DEFAULT ('admin'),
  createdon TIMESTAMP WITH TIME ZONE DEFAULT now(),
  FOREIGN KEY (creator) references users (email) on delete CASCADE
);

CREATE TABLE groupmembers (
  id SERIAL PRIMARY KEY NOT NULL,
  groupid INTEGER NOT NULL,
  userid INTEGER NOT NULL,
  createdon TIMESTAMP WITH TIME ZONE DEFAULT now(),
  FOREIGN KEY (groupid) references groups (id) on delete CASCADE,
  FOREIGN KEY (userid) references users (id) on delete CASCADE
);

 CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    firstname CHARACTER VARYING(255) NOT NULL,
    lastname CHARACTER VARYING(255) NOT NULL,
    email CHARACTER VARYING(50) UNIQUE NOT NULL,
    password CHARACTER VARYING(255) NOT NULL,
    registeredon TIMESTAMP WITH TIME ZONE DEFAULT now()
  );

插入组表的语句为：

INSERT INTO groups (name, creator) VALUES ($1, $2) RETURNING *;

如何添加另一个将值插入到的插入语句groupid and userid的列groupmembers table?

我看过这个，但它似乎没有回答我的问题：
PostgreSQL 嵌套 INSERT/WITH 用于外键插入 https://stackoverflow.com/questions/21386772/postgresql-nested-inserts-withs-for-foreign-key-insertions

我建议将两个插入包装在一个查询中数据修改CTE https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING, like:

WITH grp_ins AS (
   INSERT INTO groups (name, creator)
   VALUES ($1, $2)
   RETURNING id, creator
   )
INSERT INTO groupmembers (groupid, userid)
SELECT g.id, u.id 
FROM   grp_ins    g
JOIN   users u ON u.email = g.creator;

Since groups.creator被定义为NOT NULL具有强制引用完整性的 FK 约束，[INNER] JOIN很好。否则我会考虑LEFT JOIN.

很像你提到的答案 https://stackoverflow.com/a/21387005/939860到。或者这些：

• 使用 Postgres 一次向 3 个表插入数据 https://stackoverflow.com/questions/20561254/insert-data-in-3-tables-at-a-time-using-postgres/20561627#20561627
• 将插入的id插入到另一个表中 https://stackoverflow.com/questions/27588544/insert-inserted-id-to-another-table/27589070#27589070

如果由于某种原因，您无法强制执行上述命令（例如嵌套在必须用于插入的函数中）groups), 下一个最好的事情 is a trigger https://www.postgresql.org/docs/current/triggers.html AFTER INSERT执行第二个INSERT。稍微复杂一点，成本也高一些。但工作是否：

CREATE OR REPLACE FUNCTION trg_ins_row_in_groupmembers()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO groupmembers (groupid, userid)
   SELECT NEW.id, (SELECT u.id FROM users u WHERE u.email = NEW.creator);

   RETURN NEW;  -- doesn't matter much what you return here
END
$func$  LANGUAGE plpgsql;

还有一个触发器AFTER INSERT on groups:

CREATE TRIGGER groups_ins_aft
AFTER INSERT ON groups
FOR EACH ROW EXECUTE PROCEDURE trg_ins_row_in_groupmembers();

Related:

• 对表进行 INSERT 在连接的表中插入数据 https://stackoverflow.com/questions/10119005/on-insert-to-a-table-insert-data-in-connected-tables/10402234#10402234