I am looking to insert a couple of values associated to a pair of ids without hardcoding those ids in the query.
More concretely, I have this table foo:
create table if not exists foo(id int, val text);
and I can insert my values by doing:
insert into foo
values
(10, 'qwe_1'),
(10, 'qwe_2'),
(10, 'qwe_3'),
(20, 'qwe_2'),
(20, 'asd_3'),
(20, 'asd_4');
but I do not want to repeat those 10 and 20.
I asked a similar question a while ago (SQL - Using WITH to declare variable on INSERT INTO) but it does not solve my problem. Neither can I understand how to use a join or similar as suggested in INSERT repeating values in SQL, since the list of values I want to add per id is arbitrary.
While not strictly needed I would like to use a with statement to first declare my ids:
with c (first_id, second_id) as (values (10, 20))
select * from c;
but I don't understand how to combine that with the insert into statement. I have this non working query but which illustrates what I'm trying to achieve:
with c (first_id, second_id) as (values (10, 20))
insert into foo
values
(c.first_id, 'qwe_1'),
(c.first_id, 'qwe_2'),
(c.first_id, 'qwe_3'),
(c.second_id, 'qwe_2'),
(c.second_id, 'asd_3'),
(c.second_id, 'asd_4')
from c;
My understanding is that the values (...), ... statement returns a table so maybe what I am lacking is a way of combining this table with the c table.
You could use a lateral join:
insert into foo (id, val)
select v.id, v.val
from (values (10, 20)) c(first_id, second_id) cross join lateral
(values (c.first_id, 'qwe_1'),
(c.first_id, 'qwe_2'),
(c.first_id, 'qwe_3'),
(c.second_id, 'qwe_2'),
(c.second_id, 'asd_3'),
(c.second_id, 'asd_4')
) v(id, val);
If you're able to use a block structure, I would use that route.
do $$
DEFINE
v_first_id NUMBER := 10;
v_second_id NUMBER := 20;
BEGIN
... Your Insert Statement ...
END; $$