plpgsql 时间相减，分区表 ，主键自增， 触发器，过程存储，触发器获取最新插入的记录

一、表结构

drop table if exists VehicleDataAndLocation cascade;
create table VehicleDataAndLocation ( 
id serial, 
time timestamp without time zone, 
vin varchar(255), 
chargingStatus int, 
speed decimal(20,6), 
mileage decimal(20,6), 
stateOfCharge int, 
latitude decimal(20,6),  
primary key (id)) ;
CREATE INDEX VehicleDataAndLocation_id_index ON VehicleDataAndLocation (id);
CREATE INDEX VehicleDataAndLocation_id_time ON VehicleDataAndLocation (time);
CREATE INDEX VehicleDataAndLocation_id_vin ON VehicleDataAndLocation (vin);
CREATE TABLE VehicleDataAndLocation_0 ( check (id >= 0 and id < 500000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_1 ( check (id >= 500000 and id< 1000000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_2 ( check (id >= 1000000 and id< 1500000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_3 ( check (id >= 2000000 and id< 25000000) ) INHERITS (VehicleDataAndLocation);

知识点

1、主键自增id， 类型 serial

2、timestamp without time zone  ---  '2020-02-20 20:20:20'，不包含时区

      timestamp with time zone --- '2020-02-20 20:20:20 +时区'  ，含时区

3、decimal(刻度,精度)  : 精确数据类型

4、删除。其中 cascade代表级联删除，没有级联可以去掉cascade

drop table if exists IntervalMileage cascade;

 二、过程存储（触发器函数）

--发生停车充电：chargingStatus = 1, 找到上一条 充电完成：chargingStatus = 4 的记录，并插入新记录
create or replace function getIntervalOfMileage() returns trigger as
$body$
DECLARE
	--表结构对应变量
	vin varchar;
	_time timestamp without time zone;
	imleageInterval decimal(20,6);
	socInterval decimal(20,6);
	timeInterval decimal(20,6);
	latitude decimal(20,6);
	
	--变量
	startRecord record;
	--动态游标
	startCur refcursor;
	
	chargeNum integer default 1;
	completeNum integer default 4;
	
	endRecord record default null;
	endCur refcursor ;
		  
	
BEGIN
	--NEW 新插入的记录
	if NEW.chargingStatus != chargeNum then
		return null;
	end if;

	open startCur for
			 select t.id, t.time, t.vin, t.chargingStatus, t.mileage, t.stateOfCharge, t.latitude
			 from VehicleDataAndLocation t where t.id = NEW.id;
	fetch startCur into startRecord;
	close startCur;
	
	open endCur for 
		  select t.id, t.time, t.vin, t.chargingStatus, t.mileage, t.stateOfCharge, t.latitude
		  from VehicleDataAndLocation t 
		  where t.vin = startRecord.vin
		  and t.time <= startRecord.time
		  and t.chargingStatus = completeNum
		  order by t.time desc
		  limit 1;
	loop
		fetch endCur into endRecord;
		exit when not found;
		if endRecord.chargingStatus = completeNum and endRecord.vin = startRecord.vin then
			 exit;
		end if;
	end loop;
	close endCur;
	
	if endRecord is null then 
		return null;
	end if;
	
	--计算常量
	vin := endRecord.vin;
	_time := startRecord.time;
	imleageInterval := endRecord.mileage - startRecord.mileage;
	socInterval := startRecord.stateOfCharge - endRecord.stateOfCharge;
	--时间相减
	timeInterval := (extract(epoch from startRecord.time) - extract(epoch from endRecord.time)) * 1000 ;
	latitude := endRecord.latitude;
	
	--插入结果表
	insert into IntervalMileage(
	vin ,
	time ,
	mileageInterval ,
	socInterval ,
	timeInterval ,
	latitude 
	)values(
	vin,
	_time,
	imleageInterval,
	socInterval,
	timeInterval,
	latitude);
	return null;
END;
$body$
language plpgsql;


--触发器
create trigger countTheIntervalOfMileage
after insert on VehicleDataAndLocation
for each row 
when (NEW.chargingStatus = 1)
EXECUTE PROCEDURE getIntervalOfMileage();

知识点

1、触发器函数，返回trigger并且函数无参

create or replace function getIntervalOfMileage() returns trigger 

2、动态游标，可以在需要的时候再进行绑定

startCur refcursor;

3、访问新插入的记录，可以用new进行访问，无需传递

if NEW.chargingStatus != chargeNum then

4、游标cursor搭配记录record进行使用，获取一条记录

open startCur for
    select t.id, t.time, t.vin, t.chargingStatus, t.mileage, t.stateOfCharge, t.latitude
    from VehicleDataAndLocation t where t.id = NEW.id;
fetch startCur into startRecord;
close startCur;

fetch从游标中获取记录防盗startRecord中，需要可以如下声明，单条记录不用循环

open startCur(
    i INTEGER
) 
for
    select t.id, t.time, t.vin, t.chargingStatus, t.mileage, t.stateOfCharge, t.latitude
    from VehicleDataAndLocation t where t.id = NEW.id;
fetch startCur into startRecord;
close startCur;

5、多条记录要循环 loop; end loop;

open endCur for 
		  select t.id, t.time, t.vin, t.chargingStatus, t.mileage, t.stateOfCharge, t.latitude
		  from VehicleDataAndLocation t 
		  where t.vin = startRecord.vin
		  and t.time <= startRecord.time
		  and t.chargingStatus = completeNum
		  order by t.time desc
		  limit 1;
	loop
		fetch endCur into endRecord;
		exit when not found;
		if endRecord.chargingStatus = completeNum and endRecord.vin = startRecord.vin then
			 exit;
		end if;
	end loop;
	close endCur;

5、当游标没有记录时，可以将record判断是否为空，声明时默认为null值

endRecord record default null;

if endRecord is null then 
    return null;
end if;

6、时间相减，需求是计算时间差值。时间格式为timestamp '2020-20-20 20:20:20' 

timeInterval := (extract(epoch from startRecord.time) - extract(epoch from endRecord.time)) * 1000 ;

采用 extract(epoch from startRecord.time) 变为时间戳，但转变后已秒为单位。转为ms，乘1000即可。

7、触发器需要返回值，要在最后添上返回值

return null;

8、查询结果为null，返回默认值

select  isnull(f.time,t.time)

 

sqlserver：
    select isnull(字段,默认值) from 表名

MySQL：
    select ifnull(字段,默认值) from 表名

oracle：
    select nvl(字段,默认值) from 表名 

plpgsql:
    select coalesce(字段,默认值) from 表名 

三、触发器

create trigger countTheIntervalOfMileage
after insert on VehicleDataAndLocation
for each row 
when (NEW.chargingStatus = 1)
EXECUTE PROCEDURE getIntervalOfMileage();

知识点：

1、访问触发器前后的记录可以用 new、old来进行访问，代表一条记录

update： 有old , new
delete：只有 old
insert：只有 new

2、触发器格式

CREATE [CONSTRAINT] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ...] } 
ON table_name
[ FROM referenced_table_name ]
{[ NOT DEFERRABLE ][ DEFERRABLE ]{[ INITIALLY IMMEDIATE ][ INITIALLY DEFERRED]}} 
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN (condition) ]
EXECUTE PROCEDURE function_name ( arguments )