- 追加された行はこの色です。
- 削除された行はこの色です。
#author("2020-07-05T11:53:07+09:00","default:ryuichi","ryuichi")
* CTEでINSERTされた行のデータを別のINSERTに使う [#i5b52873]
** 前提 [#uddfed02]
CREATE TABLE category ( category_id serial primary key, category_name text );
CREATE TABLE item ( item_id serial primary key, item_name text, category_id int );
** CTE [#la2034b7]
WITH category_cte AS (
INSERT INTO category ( category_name ) VALUES ( 'cat_a' )
RETURNING category_id
)
INSERT INTO item ( item_name, category_id )
SELECT 'item_a', category_id FROM category_cte;
[category]:
category_id | category_name
-------------+---------------
1 | cat_a
[item]:
item_id | item_name | category_id
---------+-----------+-------------
1 | item_a | 1