sql - 数组与postgres中的select语句的交集

Question

我正在尝试将 select 语句与在循环中动态计算的数组相交，然后测量每个相交查询所花费的时间。

所以在循环的第一次迭代中，我希望查询是这样的:

'select sub_id from table where main_id=1 INTERSECT select array[''80'']';

在第二次迭代中:

'select sub_id from table where main_id=1 INTERSECT select array[''80'', ''81'']';

等等，并测量每次迭代执行查询所花费的时间。

当我运行这个脚本时，出现错误:

CONTEXT: PL/pgSQL function inline_code_block at assignment

这对应于我初始化 query1 的行。如何更正对 query1 的分配？我正在使用 PostgreSQL 9.1。

arr := '{}';
for j in 80..120 loop
  arr := array_append(arr, j::text);
  query1 := 'select sub_id from table where main_id=1 INTERSECT' || ' select unnest(arr)';
  execute 'explain (analyse, format json) ' || query1 into p;
  t := (p->0->>'Planning Time')::float + (p->0->>'Execution Time')::float;
  total_time := total_time + t;
end loop;

Answer 1

您执行 的查询文本不能从调用范围引用变量。您必须使用 using 将变量作为参数传递，例如:

query1 := 'select sub_id from YourTable where main_id=1 INTERSECT select unnest($1)';
execute 'explain (analyse, format json) ' || query1 using arr into p;
                                                    ^^^^^^^^^

Working example at rextester.com