Re: Multitable insert syntax support on Postgres?
| От | Corey Huinker |
|---|---|
| Тема | Re: Multitable insert syntax support on Postgres? |
| Дата | |
| Msg-id | CADkLM=fRPe2ftSM_iUYDPfih1qQRRMTzxOw7=C3gWDhXSpOe0g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Multitable insert syntax support on Postgres? (Andres Freund <andres@anarazel.de>) |
| Список | pgsql-hackers |
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
INSERT INTO c SELECT * FROM data_src WHERE d < 5
I suppose you could just do a dummy SELECT at the bottom to make it look more symmetrical
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5)
SELECT true AS inserts_complete;
Or maybe get some diagnostics out of it:
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5 RETURNING NULL),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5 RETURNING NULL),
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5 RETURNING NULL)
SELECT
(SELECT COUNT(*) FROM insert_a) AS new_a_rows,
(SELECT COUNT(*) FROM insert_b) AS new_b_rows,
(SELECT COUNT(*) FROM insert_c) AS new_c_rows;
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5)
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5)
SELECT true AS inserts_complete;
Or maybe get some diagnostics out of it:
WITH data_src AS (SELECT * FROM source_tbl),
insert_a AS (INSERT INTO a SELECT * FROM data_src WHERE d < 5 RETURNING NULL),
insert_b AS (INSERT INTO b SELECT * FROM data_src WHERE d >= 5 RETURNING NULL),
insert_c AS (INSERT INTO c SELECT * FROM data_src WHERE d < 5 RETURNING NULL)
SELECT
(SELECT COUNT(*) FROM insert_a) AS new_a_rows,
(SELECT COUNT(*) FROM insert_b) AS new_b_rows,
(SELECT COUNT(*) FROM insert_c) AS new_c_rows;
В списке pgsql-hackers по дате отправления: