英文:
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 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_id
    FROM appointment CROSS JOIN generate_series(1,5) s(counter)
    RETURNING id
)
SELECT array_agg(id)
  FROM success_insert;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论