You should be able to do something like this with a writable CTE:
WITH i AS (
INSERT INTO host (hostname, hostrole) VALUES ('foobar', 'Virtual') RETURNING id
)
INSERT INTO interface (name, mac, host)
SELECT 'eth0', '00:50:56:9d:34:d4', id
FROM i
(untested, but it should be something like that)
Writable CTE is in PostgreSQL 9.1 and up.
This would do what you desire:
WITH p AS (
INSERT INTO parent_table (column_1)
SELECT $1
RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, t.a, t.b
FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t
The subtle difference here is that unnest()
calls in the same SELECT
list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:
You could use a cleaner form with generate_subscripts()
or other techniques, but those would be much more verbose. Details in this related question:
Postgres 9.4
The new WITH ORDINALITY
in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:
WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b
FROM p
, unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);
And this special case can be even simpler with the new form of unnest()
that accepts multiple arrays:
WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b
FROM p, unnest($2::text[], $3::bigint[]) AS t(a, b);
Example in this related answer.
Best Answer
Using
generate_series()
and ctes. Tested in rextester.com:For the case when there is only one column and it's a
serial
, I see no way to use thedefault
. Using the generate_series is straight-forward:clock_timestamp()
, the statement will have to be adjusted accordingly, like the serial case.