英文:
How can I output all of the ID's stored inside of a declared ::text[]
问题
我正在尝试批量插入一个复制的记录，以进行应用程序的压力测试。
DO $$DECLAREf text[];BEGINFOR counter IN 1..5 LOOPWITH 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_idFROM appointmentRETURNING 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 $$DECLAREf text[];BEGINFOR counter IN 1..5 LOOPWITH 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_idFROM appointmentRETURNING 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_idFROM 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 ids; 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 ids:
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_idFROM appointment CROSS JOIN generate_series(1,5) s(counter)RETURNING id)SELECT array_agg(id)FROM success_insert;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论