英文:
How can I output all of the ID's stored inside of a declared ::text[]
问题
我正在尝试批量插入一个复制的记录,以进行应用程序的压力测试。
DO $$
DECLARE
f text[];
BEGIN
FOR counter IN 1..5 LOOP
WITH success_insert AS (
WITH appointment AS (
SELECT * FROM scheduler_appointments LIMIT 1
)
INSERT INTO scheduler_appointments (id, schedule_id, call_id, identifier, name, starts_at, ends_at, created_at, updated_at, type_id)
SELECT gen_random_uuid(), schedule_id, call_id, gen_random_uuid(), 'TEST FROM MASS PGSQL', starts_at, ends_at, now(), now(), type_id
FROM appointment
RETURNING id
)
SELECT array_agg(id) INTO f FROM success_insert;
END LOOP;
SELECT * FROM unnest(f);
END; $$
然而,我似乎无法获得创建的所有ID的列表。而是收到以下错误:
ERROR: query has no destination for result data
英文:
I am trying to mass insert a replicated record for stress testing the application.
DO $$
DECLARE
f text[];
BEGIN
FOR counter IN 1..5 LOOP
WITH success_insert AS (
WITH appointment AS (
SELECT * FROM scheduler_appointments LIMIT 1
)
INSERT INTO scheduler_appointments (id, schedule_id, call_id, identifier, name, starts_at, ends_at, created_at, updated_at, type_id)
SELECT gen_random_uuid(), schedule_id, call_id, gen_random_uuid(), 'TEST FROM MASS PGSQL', starts_at, ends_at, now(), now(), type_id
FROM appointment
RETURNING id
)
SELECT array_agg(id) INTO f FROM success_insert;
END LOOP;
SELECT * FROM unnest(f);
END; $$
However, I cannot seem to get a list of all the ID's that where created. I instead recieve te following error:
> ERROR: query has no destination for result data
答案1
得分: 1
最终的语句,SELECT * FROM unnest(f);
,没有 INTO
子句。我假设意图是返回所有插入的 id
;然而,当运行最后的语句时,f
只会包含最后一次循环的 id
。不需要使用 PLPGSQL。以下 SQL 将生成 scheduler_appointments
并返回 id
的数组:
WITH appointment AS (
SELECT * FROM scheduler_appointments LIMIT 1
),
success_insert AS (
INSERT INTO scheduler_appointments (id, schedule_id, call_id, identifier, name, starts_at, ends_at, created_at, updated_at, type_id)
SELECT gen_random_uuid(), schedule_id, call_id, gen_random_uuid(), 'TEST FROM MASS PGSQL', starts_at, ends_at, now(), now(), type_id
FROM appointment CROSS JOIN generate_series(1,5) s(counter)
RETURNING id
)
SELECT array_agg(id)
FROM success_insert;
英文:
The final statement, SELECT * FROM unnest(f);
, doesn't have an INTO
clause. I assume the intent is to return all of the inserted id
s; however, when that last statement is run, f
will only have the id
from the last pass through the loop. There's no need to use PLPGSQL. The following SQL will generate scheduler_appointments
and return an array of the id
s:
WITH appointment AS (
SELECT * FROM scheduler_appointments LIMIT 1
),
success_insert AS (
INSERT INTO scheduler_appointments (id, schedule_id, call_id, identifier, name, starts_at, ends_at, created_at, updated_at, type_id)
SELECT gen_random_uuid(), schedule_id, call_id, gen_random_uuid(), 'TEST FROM MASS PGSQL', starts_at, ends_at, now(), now(), type_id
FROM appointment CROSS JOIN generate_series(1,5) s(counter)
RETURNING id
)
SELECT array_agg(id)
FROM success_insert;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论