本文介绍了是否可以在 Postgres 的 CTE 语句中多次使用返回值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在一个 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 语句中多次使用返回值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!