Insert into multiple tables with one query

Question

I would like to add data to at least three tables with one query. I thought of something like this:

WITH ins AS (
 INSERT INTO core.adr
 (street, "number", postal_code, city)
 VALUES
 ('test 1', '25a', '00912', 'villageman')
 RETURNING id)
INSERT INTO core.adr_information
 (idref, info)
SELECT id, 'test data'
FROM ins;

which works perfectly for exactly two tables.

Main problem here is, that all additional queries require the id value from the first INSERT query, which seems not manageable this way. I think it could be easily done with a stored procedure or transaction, but I would like the solution to be a simple and solid query.

Am I overlooking something? Is it possible that way or another (no stored procedures* or transactions)?

---

Note

_{* Altough, strictly speaking, Postgres does not have stored procedures, only functions, as @Erwin Brandstetter rightly pointed out via comments. See this series of posts for relevant information.}

• a_vlad: other way - trigger on one table with insert into any number of other
• Erwin Brandstetter: What makes it seem not manageable this way? Because it is.

Answer 1

I think you can easily pile the CTEs on top of each other, like so:

WITH ins AS (
 INSERT INTO core.adr
 (street, "number", postal_code, city)
 VALUES
 ('test 1', '25a', '00912', 'villageman')
 RETURNING id),
ins2 AS (
 INSERT INTO someothertable
 (id, something)
 SELECT id, 'something' FROM ins
 RETURNING id -- this is necessary for CTE, but not used
)
INSERT INTO core.adr_information
 (idref, info)
SELECT id, 'test data'
FROM ins;

• Erwin Brandstetter: Correct. Except, the 2nd RETURNING is not necessary. Data-modifying CTEs are always executed to completion either way: stackoverflow.com/a/15810159/939860
• mustaccio: @ErwinBrandstetter I guess that's true if you don't explicitly specify CTE columns.
• Erwin Brandstetter: What do you mean by explicitly specify CTE columns? Test your code without the 2nd RETURNING and you'll see that it works. Consider the quotes from the manual in the related answer I linked.
• mustaccio: with cte ( col1 ) as (select ...) explicitly specifies a column (col1) in which case you'll need returning.
• Erwin Brandstetter: That's correct, of course. You wouldn't declare columns to be returned if you are not going to return columns ... Nitpicking: Your example in the comment uses select, where this does not apply. On the contrary: Basic CTEs (not data-modifying) which are not referenced by other CTEs or the outer statement are never executed! dba.stackexchange.com/a/69650/3684