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)?