PostgreSQL动态SQL(兼容oracle DBMS_SQL)

oracle中的dbms_sql包可以用来执行动态SQL，让我们在存储过程的动态SQL中使用prepared statement。

oracle中dbms_sql包使用介绍：

例子：
oracle：

DECLARE  
  stmt VARCHAR2(200);  
  dept_no_array DBMS_SQL.NUMBER_TABLE;  
  c NUMBER;  
  dummy NUMBER;  
begin  
  dept_no_array(1) := 10; dept_no_array(2) := 20; /* Put some values into the array */  
  dept_no_array(3) := 30; dept_no_array(4) := 40;  
  dept_no_array(5) := 30; dept_no_array(6) := 40;  
  stmt := 'delete from emp where deptno = :dept_array'; /* A Dynamic SQL String with a bind variable */  
  c := DBMS_SQL.OPEN_CURSOR; /* Open a Cursor! */  
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE); /* Parse the Dynamic SQL , making it happen on the native database to which is connected! */  
  
  DBMS_SQL.BIND_ARRAY(c, ':dept_array', dept_no_array, 1, 4);  
  /* Bind only elements 1 through 4 to the cursor Happens 4 times */  
  
  dummy := DBMS_SQL.EXECUTE(c);  
  /* Execute the Query, and return number of rows deleted! */  
  
  DBMS_SQL.CLOSE_CURSOR(c);   
  
  EXCEPTION WHEN OTHERS THEN  
    IF DBMS_SQL.IS_OPEN(c) THEN  
      DBMS_SQL.CLOSE_CURSOR(c);  
    END IF;  
    RAISE;  
END;  
/  

pg中可以使用服务端绑定变量的方法来实现类似的功能，大致步骤为：
1、PREPARE，准备DB端绑定变量SQL
2、EXECUTE，绑定并执行
3、DEALLOCATE，删除绑定变量

因此可以将上述代码改为：

do language plpgsql $$  
DECLARE  
  stmt VARCHAR(200);  
  dept_no_array numeric[];  
  c numeric;  
begin  
  dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */  
  dept_no_array[3] := 30; dept_no_array[4] := 40;  
  dept_no_array[5] := 30; dept_no_array[6] := 40;  
  execute format('prepare stmt(numeric) as delete from emp where deptno = $1');    /* A Dynamic SQL String with a bind variable */  
  
  foreach c in array dept_no_array[1:4]  
  loop  
    execute format('execute stmt(%s)', c);   -- 执行绑定SQL  
  end loop;  
  
  DEALLOCATE stmt;  
  
  EXCEPTION WHEN OTHERS THEN  
    DEALLOCATE stmt;  
    RAISE;  
END;  
$$;  

如果不需要使用绑定变量的话：

do language plpgsql $$  
DECLARE  
  dept_no_array numeric[];  
  c numeric;  
begin  
  dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */  
  dept_no_array[3] := 30; dept_no_array[4] := 40;  
  dept_no_array[5] := 30; dept_no_array[6] := 40;  
  
  foreach c in array dept_no_array[1:4]  
  loop  
    delete from emp where deptno = c;  
  end loop;  
  
  DEALLOCATE stmt;  
  
  EXCEPTION WHEN OTHERS THEN  
    DEALLOCATE stmt;  
    RAISE;  
END;  
$$;  

在pg中动态SQL和绑定变量这两种方式性能又如何呢？

1、建表

do language plpgsql $$
declare
begin
  execute 'drop table if exists test';
  execute 'create table test(id int primary key, info text, crt_time timestamp)';
  
  for i in 0..1023 loop
    execute format('drop table if exists test%s', i);
    execute format('create table test%s (like test including all)', i);
  end loop;
end;
$$;

2、使用动态SQL，写入目标子表

create or replace function dyn_pre(int) returns void as $$
declare
  suffix int := mod($1,1024);
begin
  execute format('insert into test%s values(%s, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, $1);
end;
$$ language plpgsql strict;

3、使用绑定变量，写入目标子表

create or replace function dyn_pre(int) returns void as $$
declare
  suffix int := mod($1,1024);
begin
  execute format('execute p%s(%s)', suffix, $1);
  exception when others then
    execute format('prepare p%s(int) as insert into test%s values($1, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, suffix);
    execute format('execute p%s(%s)', suffix, $1);
end;
$$ language plpgsql strict;

4、性能测试
vi test.sql

\set id random(1,1000000000)
select dyn_pre(:id);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 60

动态SQL：

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 60 s
number of transactions actually processed: 2543070
latency average = 0.377 ms
latency stddev = 0.254 ms
tps = 42376.154026 (including connections establishing)
tps = 42380.640279 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set id random(1,1000000000)
         0.376  select dyn_pre(:id);

绑定变量：

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 60 s
number of transactions actually processed: 3105687
latency average = 0.308 ms
latency stddev = 0.399 ms
tps = 51747.630800 (including connections establishing)
tps = 51759.435224 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set id random(1,1000000000)
         0.308  select dyn_pre(:id);

1、使用动态SQL，TPS约4.2万。
2、使用函数内绑定变量，TPS约5.1万。
实验使用的是虚拟机，物理机上绑定变量性能差异更明显。

参考链接：
https://docs.oracle.com/database/121/TTPLP/d_sql.htm#TTPLP71257
https://www.postgresql.org/docs/devel/static/sql-prepare.html