postgresql copy的使用一例

CREATE OR REPLACE FUNCTION dbo.gps_write_files()
  RETURNS trigger AS
$BODY$
declare
dir varchar(100);
begin
INSERT INTO dbo.temp_gps_location_data VALUES (new.*,now());

if (select (select count(*) from dbo.temp_gps_location_data where ((now()- insert_datetime) > interval '3 Sec')) > 0) then
 select 'D:\GPS\'||to_char(now(),'YYYYMMDDHH24MISS')|| uuid_generate_v1()||'.txt' into dir;
 EXECUTE 'COPY (select ''{"hytera":{''||
         ''"device_alias":"''          ||g.device_alias    ||
         ''","device_number":"''       ||g.device_id       ||
         ''","device_user":"''         ||
         ''","belong_organization":"'' ||
         ''","speed":"''               ||g.speed           ||
         ''","direction":"''           ||g.direction       ||
         ''","longitude":"''           ||g.longitude       ||
         ''","latitude":"''            ||g.latitude        ||
         ''","receive_datetime":"''    ||g.receive_datetime||
         ''"}}''
         from  dbo.temp_gps_location_data as g
         ) TO '''||''||dir||'''';
        
 truncate table dbo.temp_gps_location_data;
end if ;
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION dbo.gps_write_files()

  OWNER TO postgres;

--生成文件
CREATE OR REPLACE FUNCTION gps_write_files()
  RETURNS trigger AS
$BODY$
declare 
dir varchar(100);
begin
create table temp_gps_location_data as select * from gps_location_data where 1=2;
INSERT INTO temp_gps_location_data VALUES (new.*);
select 'D:\'||to_char(now(),'YYYYMMDDHH24MISSMS')||'.txt' into dir;
EXECUTE 'COPY (select * from temp_gps_location_data) TO '''||''||dir||'''';
drop table temp_gps_location_data;
return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;




--创建触发器
create trigger gps_write_files_trigger after insert on gps_location_data for each row execute procedure gps_write_files();