PLPGSQL如何获取查询计划到表中，使用匿名代码块执行动态查询

Question

我有Postgres 11版本。我怎么能把解释文本，而执行动态查询在临时表?下面是代码示例，它只显示了一行

do $$
DECLARE reca text default 'EXPLAIN select count(*) from fg.vw_metrics';
DECLARE explanation text;
BEGIN
EXECUTE reca into explanation;
create temp table aaa as select explanation;
end;
$$;

select * from aaa;

通过"Aggregate (cost=564.45..564.46 rows=1 width=8)">

我想我需要记录类型在这里或LOOP。但是我不能设法得到表中的输出:

Aggregate  (cost=564.45..564.46 rows=1 width=8)
->  Seq Scan on t_metrics_dynamic  (cost=0.29..562.70 rows=140 width=1507)
Filter: (ARRAY[(dir_id)::text, 'null'::text] && $1)
CTE cte_rls
->  Function Scan on get_direction_rls  (cost=0.26..0.27 rows=1 width=32)
InitPlan 2 (returns $1)
->  CTE Scan on cte_rls  (cost=0.00..0.02 rows=1 width=32)

问题是我只需要使用匿名代码块。我不能创建函数

Answer 1

设法使它工作

do $$
DECLARE reca text default 'explain select count(*) from prod.sell_movement_partition';
DECLARE explanation record;
BEGIN
create temp table aaa (rrr text);
for explanation in EXECUTE reca loop
insert into aaa (rrr) select explanation; end loop;
end;
$$;

或json格式的第二个变体

do $$
DECLARE reca text default 'explain (format json) SELECT (*)
FROM prod.sell_movement_partition limit 10';
DECLARE explanation jsonb;
BEGIN
EXECUTE reca into explanation;
create temp table aaa as select explanation;
end;
$$;