14

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.

Improve this question
2
  • 1
    other way - trigger on one table with insert into any number of other Commented May 31, 2016 at 1:29
  • 1
    What makes it seem not manageable this way? Because it is. Commented May 31, 2016 at 1:33

1 Answer 1

Reset to default
11

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;
Improve this answer
6
  • Correct. Except, the 2nd RETURNING is not necessary. Data-modifying CTEs are always executed to completion either way: stackoverflow.com/a/15810159/939860 Commented May 31, 2016 at 1:31
  • @ErwinBrandstetter I guess that's true if you don't explicitly specify CTE columns. Commented May 31, 2016 at 1:33
  • 1
    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. Commented May 31, 2016 at 1:38
  • with cte ( col1 ) as (select ...) explicitly specifies a column (col1) in which case you'll need returning. Commented May 31, 2016 at 2:08
  • 1
    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 Commented May 31, 2016 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.