PostgreSQL多列插入...带有多个返回列

Question

我正在使用Postgres 9.3作为后端构建一个数据库，其中包含3个表：

table1 (user_id, username, name, surname, emp_date)
table2 (pass_id, user_id, password)
table3 (user_dt_id, user_id, adress, city, phone)

可以看到，table2和table3是table1的子表。
我可以从table1（父表）中提取新插入行的user_id。

INSERT INTO "table1" (default,'johnee','john','smith',default) RETURNING userid;

我需要将从table1中新提取的id插入到table2和table3的user_id列中，同时插入这些表格唯一的其他数据。基本上是3个INSERT ...

我该怎么做？

- user3483211

---

关于 WITH INSERT UPDATE - with t as (insert ... returning ...) update ... set ...; 语法，请参考 https://dev59.com/N2025IYBdhLWcg3wChOb#45465626。 - Y Y

Answer 1

使用数据修改的CTE来链接您的三个INSERT语句。类似这样：

WITH ins1 AS (
   INSERT INTO table1 (username, name,  surname)
   VALUES ('johnee','john','smith')
   RETURNING user_id
   )
, ins2 AS (
   INSERT INTO table2 (user_id, password)
   SELECT ins1.user_id, 'secret'
   FROM   ins1                            -- nothing to return here
   )
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM   ins1
RETURNING user_id;

• 通常最好为INSERT语句添加列定义列表（除非有特殊情况）。否则，如果表结构发生变化，您的代码可能会以令人惊讶的方式出现故障。

• 我省略了您只需输入DEFAULT的列。默认值会自动插入。更短，结果相同。

• 最后，可选的RETURNING返回生成的user_id —— 显然是从序列或其他默认源获取的。实际上，这是来自table3的user_id，但除非有某些触发器或其他干扰，否则它们是相同的。

有关可修改数据（即“可写”）CTE的更多信息：

• SELECT类型查询是唯一可以嵌套的类型吗？

- Erwin Brandstetter

---

谢谢，这正是我在寻找的。而且解释得很清楚，我想这些可以嵌套在任何操作（DELETE、UPDATE等）中以相同的方式进行，同时最后的返回不需要...无论如何，非常准确和易懂。 - user3483211

Answer 2

使用函数插入主从关系。

CREATE OR REPLACE FUNCTION apps.usp_bazar_list_insert_1(
    ip_userid character varying,
    ip_mobileno character varying,
    ip_bazarlisttext text,
    data_details text)
    RETURNS TABLE(id integer) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
    
AS $BODY$
DECLARE master_id integer;
BEGIN
insert into apps.bazar_list(action_by,mobile_no,bazarlisttext,action_date) 
select ip_userId,ip_mobileNo,ip_bazarListText,now() returning list_id into master_id;

insert into apps.bazar_list_images(list_id,action_date,image_name) 
select master_id,now(),image_name from json_populate_recordset(NULL::apps.bazar_list_images, 
                                   data_details::json);
RETURN QUERY
        select 1;
    
END;
$BODY$;

- Mahydul Islam Shajal