如何运行postgre SQL查询参数与许多不同的值?

Question

所以如果我有一些简单的插入查询，像下面这样，

INSERT INTO user_test_score
(test_id, date_occured, number_of_questions, user_id, score)
VALUES (123,'2023-03-30', 100, $1, $2)

我可以用(user_id,score)数组在debeaver中运行它吗?

我所做的是首先用一些编程生成所有的查询

感谢绑定参数只接受单个值，所以它传递数组，而不是值

Answer 1

unnest可用于将数组转换为表

select some_table_name.*
from unnest(Array[1,2,3], Array[95,88,100]) as some_table_name(user_id,score);

，然后插入查询可以写成

insert into user_test_score (test_id, date_occured, number_of_questions, user_id, score)
select
123 as test_id,
'2023-03-30' as date_occured,
100 as number_of_questions,
user_id_score_table.user_id,
user_id_score_table.score
from
unnest(Array[1,2,3], Array[95,88,100]) as user_id_score_table(user_id,score);