Background information

PolarDB-O does not support FORALL statements except for the "FORALL index IN lower_bound .. upper_bound" type.

How KEDA works

It is necessary to switch between handler and SQL procedural language Oracle PL/SQL. If it is a large LOOP, the performance will be seriously degraded if more is switched.

Therefore, for processing scenarios where PL/SQL needs to call SQL multiple times, Oracle has come up with the bulk collect processing method. For example, when a user submits an array, PL/SQL requires that all elements of the array be inserted into the table, the value in the table be updated, or the value in the table be deleted.


Similar to the batch insertion usage of Oracle FORALL, an array is used to represent conditions. Another array indicates that if there are multiple conditions or values, the record array or hstore(Key-VALUE type) array can be used to represent values.
CREATE OR REPLACE FUNCTION public.f_bulk_insert1(i_k integer[], i_v text[])
 LANGUAGE plpgsql
AS $function$
  i_length int := array_length(i_k,1);
  s timestamp;
  e timestamp;
  s := clock_timestamp(); 
  raise notice 'start: %', s;
  insert into test select i_k[i], i_v[i] from generate_series(1, i_length) t(i); 
  e := clock_timestamp(); 
  raise notice 'end: %, %', e, e-s;