sql - 查询仅在 pl/pgSQL 代码块之外有效

Question

为什么以下仅在do-declare-block 之外有效？

create table gd(id integer, a1 float, a2 float, b float);
insert into gd values (1, 1::float, 1::float, 1::float);

do $$
declare 
   counter integer := 1;
begin
   while counter <= 50 loop
      insert into gd (select (id + 1)::integer as id,
                             (a1- 0.001 * avg(d_a1))::float as a1,
                             (a2- 0.001 * avg(d_a2))::float as a2,
                             (b - 0.001 * avg(d_b))::float as b
                      from my_func(...)
                      group by id, a1, a2, b);
       counter := counter + 1;
   end loop;
end$$;

insert into gd (select (id + 1)::integer as id,
                             (a1- 0.001 * avg(d_a1))::float as a1,
                             (a2- 0.001 * avg(d_a2))::float as a2,
                             (b - 0.001 * avg(d_b))::float as b
                      from my_func(...)
                      group by id, a1, a2, b);

select * from gd;

my_func(...)以以下形式返回setof record：

(id integer, a1 float, a2 float, b float, result float, d_a1 float, d_a2 float, d_b float)

如下所述，这可以正常工作，并且需要返回 a setof record，因为输出会根据输入发生变化。

do-declare发布的代码仅在-block之外调用时才有效。它在那里完成它的工作并将结果输入到gd.

id |        a1         |        a2         |         b         
----+-------------------+-------------------+-------------------
  1 |                 1 |                 1 |                 1
  2 | 0.998758771300921 | 0.998219775962089 | 0.997517869616542
(2 rows)

为什么查询只能在do-declare-block 之外工作？

-block的错误消息do-declare是：

test.sql:138: ERROR:  invalid memory alloc request size 45808919440

标签： sqlpostgresqlplpgsql