如何在PostgreSQL中实现嵌套INSERT语句？ [英] How to achieve nested INSERT statement in PostgreSQL?

问题描述

我有两个表，group和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()
  );

GROUP表中的INSERT语句为：

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

如何添加将值插入groupmembers表的groupid和userid列的另一个INSERT语句？

我看到了这一点，但似乎没有回答我的问题：
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约束定义的，因此[INNER] JOIN是好的。否则我会考虑LEFT JOIN。

与the answer you referred非常相似。或者这些：

• Insert data in 3 tables at a time using Postgres
• Insert inserted id to another table

如果由于某种原因不能强制执行上述命令(如嵌套在必须用于插入到groups的函数中)，下一个最好的方法是triggerAFTER 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在groups：

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

相关：

• On INSERT to a table INSERT data in connected tables

这篇关于如何在PostgreSQL中实现嵌套INSERT语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！