在Postgres的CTE语句中是否可以多次使用返回值？ [英] Is it possible to have multiple secondary use of a returning value in a CTE statement in Postgres?

问题描述

在一个CTE中插入主表后，我想在多个表中插入我的外键。我找不到解决方案，因此可能完全不可能...

I want to insert my foreign keys in multiple tables after a insert into the main table in one CTE. I can't find the solution so it may well be impossible...

请参见以下示例：

CREATE TABLE test_main (main_id serial NOT NULL, main_name character varying(64) default null);
CREATE TABLE test_sub_one (sub_one_id serial NOT NULL,sub_one_main_id integer NOT NULL,sub_one_name character varying(64) default null);
CREATE TABLE test_sub_two (sub_two_id serial NOT NULL,sub_two_main_id integer NOT NULL,sub_two_name character varying(64) default null);

WITH main as (
    INSERT INTO test_main (main_name) VALUES ('test1') RETURNING main_id
) 
INSERT INTO test_sub_one (sub_one_main_id,sub_one_name) SELECT main_id, 'testsub1' FROM main,
INSERT INTO test_sub_two (sub_two_main_id,sub_two_name) SELECT main_id, 'testsub2' FROM main;

推荐答案

第二个插入使用另一个CTE：

Use another CTE for the second insert:

WITH main as (
    INSERT INTO test_main (main_name) VALUES ('test1') RETURNING main_id
), sub1 as (
  INSERT INTO test_sub_one (sub_one_main_id,sub_one_name) 
  SELECT main_id, 'testsub1' FROM main
)
INSERT INTO test_sub_two (sub_two_main_id,sub_two_name) 
SELECT main_id, 'testsub2' FROM main;

这篇关于在Postgres的CTE语句中是否可以多次使用返回值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！