自定义函数中对分区表进行分区裁剪的方法

1. 问题描述：自定义函数里如果 SQL 是静态写法，参数以及变量传递给分区条件不进行分区裁剪，做全表扫描；必需动态 SQL 写法，才能走分区裁剪。

2. 详细解释：
   (1) 创建分区表

CREATE TABLE test_part
( id numeric,
 Timestamp timestamp)
WITH (appendonly=true, orientation=parquet) 
DISTRIBUTED BY (id)
PARTITION BY RANGE (Timestamp) 
( START (date '2014-10-01') INCLUSIVE   
END (date '2014-11-01') EXCLUSIVE   
EVERY (INTERVAL '1 day'),DEFAULT PARTITION other );

(2) 创建结果表

Create table tmp_test_part (like test_part);

(3) 创建动态写法函数

Create Or Replace Function f_Test(f_Day Text) Returns Integer As
	$body$               
Declare 
        v_Start Date;
	v_Before1_Day_Start  Date;
	v_Rowcount           Numeric;
	Strslq               Varchar(200000);
	Psl                  Text;
Begin
	v_Start  := To_Timestamp(f_Day, 'yyyymmddhh24miss');
	v_Before1_Day_Start := To_Timestamp(f_Day, 'yyyymmddhh24miss') + Interval '6 day';
	Strslq := 'insert into tmp_test_part select * from test_part a where a.timestamp>=';
	Strslq := Strslq || '''' || v_Start || ''' and a.timestamp<=''' || v_Before1_Day_Start || '''';
	Execute Strslq;
	Select Pg_Sleep(60) Into Psl;
	Raise Notice 'print %', Strslq;
	Return v_Rowcount;
Exception
	When Others Then
		Return Strslq;
End; $body$
	Language Plpgsql Volatile;

(4) 执行函数

select f_test('20141001');

(5) 在函数执行中查询锁表，监控总共锁了多少分区，判断是否进行了全表扫描

SELECT  procpid,pid, granted,current_query, datname ,mode,pg_class.relname
FROM pg_locks, 
pg_stat_activity ,pg_class
WHERE pg_stat_activity.procpid=pg_locks.pid and pg_class.oid=pg_locks.relation
and procpid<>pg_backend_pid();

(6) 创建静态写法函数

CREATE OR REPLACE FUNCTION f_test2(f_day text)
  RETURNS integer AS
$BODY$
  declare
  v_start              date;
  v_before1_day_start  date;
  v_rowcount           numeric;
  strSLQ VARCHAR(200000);
  psl text;
begin
  v_start:=to_timestamp(f_day,'yyyymmddhh24miss');
  v_before1_day_start:=to_timestamp(f_day,'yyyymmddhh24miss') + interval '6 day';
             insert into tmp_test_part select * from test_part a where a.timestamp>=v_start and a.timestamp<=v_before1_day_start;
  select pg_sleep(60) into psl;
  return v_rowcount;
  EXCEPTION WHEN OTHERS THEN 
  return strSLQ;
end; 
$BODY$
  LANGUAGE plpgsql VOLATILE;

(7) 执行函数

select f_tes2('20141001');

(8) 在函数执行中查询锁表，监控总共锁了多少分区，判断是否进行了全表扫描

SELECT  procpid,pid, granted,current_query, datname ,mode,pg_class.relname
FROM pg_locks, 
pg_stat_activity ,pg_class
WHERE pg_stat_activity.procpid=pg_locks.pid and pg_class.oid=pg_locks.relation
and procpid<>pg_backend_pid();