如何输出已声明的 ::text[] 中存储的所有 ID?

huangapple go评论57阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年6月19日 23:25:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76508044.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定