sql - 如何在PostgreSQL中实现嵌套的INSERT语句？

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

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语句是：

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

如何添加另一个insert语句，将值插入到groupid表的userid和groupmembers列中？
我看到过，但似乎没有回答我的问题：
PostgreSQL nested INSERTs / WITHs for foreign key insertions

最佳答案

我建议在一个查询中用data-modifying CTE将两个插入内容包装起来，例如：

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;

因为groups.creator是用强制引用完整性的FK约束定义的，所以NOT NULL是好的。否则我会考虑a[INNER] JOIN。
很像the answer you referred到。或者这些：
Insert data in 3 tables at a time using Postgres
Insert inserted id to another table
如果由于某种原因，您不能执行上面的命令（就像嵌套在一个必须用于插入到LEFT JOIN的函数中），那么接下来最好是执行第二个groups。更复杂，更昂贵。但工作是：

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;

一个触发器打开：

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