navicat循环插入gp数据库，设备数据

–顺序插入设备表数据，Ps:暂时无法识别下划线参数需要修改-------
–可以去掉带下划线的参数，或者暂时把下划线修改再改回来
–place_code，setup_state，create_time，update_time，is_online

– 如果存在删除函数INSERT_DATA_TO_DEVICE…
DROP PROCEDURE
IF EXISTS INSERT_DATA_TO_DEVICE;
– 创建函数
CREATE OR REPLACE PROCEDURE “public”.“insert_data_to_device”()
AS $BODY$BEGIN
DECLARE n int;
BEGIN
– 定义n的初始数值··············································
n = 100;
– 设置n的终止值··············································
WHILE n < 200 LOOP
INSERT INTO app_device( id, name, type ,createtime,enabled )
VALUES
( n, ‘stu’, ( 1 + ceil(random()*10)) , now(),(1+ceil(random()*1)));
n = n +1 ;
END LOOP;
end;
END$BODY$
LANGUAGE plpgsql;
– 执行函数
CALL INSERT_DATA_TO_DEVICE ();

–查询
SELECT * FROM app_device;

创建表
DROP TABLE IF EXISTS “public”.“app_device”;
CREATE TABLE “public”.“app_device” (
“id” varchar(32) COLLATE “pg_catalog”.“default” NOT NULL,
“name” varchar(256) COLLATE “pg_catalog”.“default” NOT NULL,
“building_id” varchar(32) COLLATE “pg_catalog”.“default”,
“organization_id” varchar(32) COLLATE “pg_catalog”.“default”,
“type” varchar(2) COLLATE “pg_catalog”.“default” NOT NULL,
“mac” varchar(128) COLLATE “pg_catalog”.“default”,
“external_id” varchar(256) COLLATE “pg_catalog”.“default”,
“gb_id” varchar(20) COLLATE “pg_catalog”.“default”,
“address” varchar(256) COLLATE “pg_catalog”.“default”,
“place_code” varchar(20) COLLATE “pg_catalog”.“default”,
“latitude” float8,
“longitude” float8,
“manufacturer” varchar(256) COLLATE “pg_catalog”.“default”,
“model” varchar(20) COLLATE “pg_catalog”.“default”,
“setup_state” varchar(2) COLLATE “pg_catalog”.“default”,
“setup_time” timestamp(6),
“description” text COLLATE “pg_catalog”.“default”,
“createtime” timestamp(6),
“create_by” varchar(32) COLLATE “pg_catalog”.“default”,
“update_time” timestamp(6),
“update_by” varchar(32) COLLATE “pg_catalog”.“default”,
“last_report_time” timestamp(6),
“last_report_data” text COLLATE “pg_catalog”.“default”,
“maintenance_time” timestamp(6),
“is_online” varchar(1) COLLATE “pg_catalog”.“default”,
“contact” varchar(256) COLLATE “pg_catalog”.“default”,
“phone_number” varchar(256) COLLATE “pg_catalog”.“default”,
“total_running_time” int8,
“soft_version” varchar(256) COLLATE “pg_catalog”.“default”,
“warranty” timestamp(6),
“control_password” varchar(256) COLLATE “pg_catalog”.“default”,
“enabled” varchar(1) COLLATE “pg_catalog”.“default”,
“heading” float8,
“pitch” float8,
“height” float8,
“area_id” varchar(32) COLLATE “pg_catalog”.“default”,
“sub_type” varchar(20) COLLATE “pg_catalog”.“default”,
“region_id” varchar(32) COLLATE “pg_catalog”.“default”,
“features” varchar(1024) COLLATE “pg_catalog”.“default”,
“is_indoor” varchar(1) COLLATE “pg_catalog”.“default” DEFAULT ‘0’::character varying,
“sync_exclude_col” varchar(1024) COLLATE “pg_catalog”.“default”,
“last_online_time” timestamp(6),
“building_floor_id” varchar(32) COLLATE “pg_catalog”.“default”,
“direction” varchar(3) COLLATE “pg_catalog”.“default”,
“building_room_id” varchar(32) COLLATE “pg_catalog”.“default”,
“entrance_or_exit” varchar(1) COLLATE “pg_catalog”.“default” DEFAULT ‘0’::character varying,
“geohash” varchar(12) COLLATE “pg_catalog”.“default”,
“ip_address” varchar(15) COLLATE “pg_catalog”.“default”,
“port” int4,
“link_url” varchar(2048) COLLATE “pg_catalog”.“default”,
“protocol_version” varchar(20) COLLATE “pg_catalog”.“default”,
“login_name” varchar(256) COLLATE “pg_catalog”.“default”,
“login_passwd” varchar(256) COLLATE “pg_catalog”.“default”,
“camera_soft_version” varchar(256) COLLATE “pg_catalog”.“default”,
“user_name” varchar(256) COLLATE “pg_catalog”.“default”,
“control_port” int4
)
;
COMMENT ON COLUMN “public”.“app_device”.“id” IS ‘设备ID’;
COMMENT ON COLUMN “public”.“app_device”.“name” IS ‘设备名称’;
COMMENT ON COLUMN “public”.“app_device”.“building_id” IS ‘建筑ID’;
COMMENT ON COLUMN “public”.“app_device”.“organization_id” IS ‘所属组织’;
COMMENT ON COLUMN “public”.“app_device”.“type” IS ‘设备类型
1：摄像机
2：环境传感器
3：测温设备
4：报警主机
5：车牌设备
6：门禁控制器
7：智能井盖
8：智能垃圾箱
9：地磁
10:人脸门禁
11：塔吊’;
COMMENT ON COLUMN “public”.“app_device”.“mac” IS ‘设备MAC地址’;
COMMENT ON COLUMN “public”.“app_device”.“external_id” IS ‘外部平台ID’;
COMMENT ON COLUMN “public”.“app_device”.“gb_id” IS ‘28181国标编码’;
COMMENT ON COLUMN “public”.“app_device”.“address” IS ‘安装地址’;
COMMENT ON COLUMN “public”.“app_device”.“place_code” IS ‘所属地区’;
COMMENT ON COLUMN “public”.“app_device”.“latitude” IS ‘纬度’;
COMMENT ON COLUMN “public”.“app_device”.“longitude” IS ‘经度’;
COMMENT ON COLUMN “public”.“app_device”.“manufacturer” IS ‘生产厂家’;
COMMENT ON COLUMN “public”.“app_device”.“model” IS ‘设备型号’;
COMMENT ON COLUMN “public”.“app_device”.“setup_state” IS ‘设备安装状态：1：已安装 0：未安装’;
COMMENT ON COLUMN “public”.“app_device”.“setup_time” IS ‘安装时间’;
COMMENT ON COLUMN “public”.“app_device”.“description” IS ‘描述’;
COMMENT ON COLUMN “public”.“app_device”.“createtime” IS ‘创建时间’;
COMMENT ON COLUMN “public”.“app_device”.“create_by” IS ‘创建用户ID’;
COMMENT ON COLUMN “public”.“app_device”.“update_time” IS ‘最后修改时间’;
COMMENT ON COLUMN “public”.“app_device”.“update_by” IS ‘最后修改用户ID’;
COMMENT ON COLUMN “public”.“app_device”.“last_report_time” IS ‘最后上报时间’;
COMMENT ON COLUMN “public”.“app_device”.“last_report_data” IS ‘最后上报数据’;
COMMENT ON COLUMN “public”.“app_device”.“maintenance_time” IS ‘最后一次维护时间’;
COMMENT ON COLUMN “public”.“app_device”.“is_online” IS ‘在线状态：0-离线 1-在线’;
COMMENT ON COLUMN “public”.“app_device”.“contact” IS ‘联系人’;
COMMENT ON COLUMN “public”.“app_device”.“phone_number” IS ‘联系方式’;
COMMENT ON COLUMN “public”.“app_device”.“total_running_time” IS ‘运行时长（分钟）’;
COMMENT ON COLUMN “public”.“app_device”.“soft_version” IS ‘固件版本’;
COMMENT ON COLUMN “public”.“app_device”.“warranty” IS ‘保修期’;
COMMENT ON COLUMN “public”.“app_device”.“control_password” IS ‘控制密码（MD5后）’;
COMMENT ON COLUMN “public”.“app_device”.“enabled” IS ‘启用状态：0-禁用 1-启用’;
COMMENT ON COLUMN “public”.“app_device”.“heading” IS ‘朝向角（取值：0 ~ 360，0 代表正北）’;
COMMENT ON COLUMN “public”.“app_device”.“pitch” IS ‘俯仰角（取值：-180 ~ 180, 0 代表水平）’;
COMMENT ON COLUMN “public”.“app_device”.“height” IS ‘高度（厘米）’;
COMMENT ON COLUMN “public”.“app_device”.“area_id” IS ‘地区ID’;
COMMENT ON COLUMN “public”.“app_device”.“sub_type” IS ‘设备子分类：
1-摄像机子分类：1-枪机 2-球机 3-半球’;
COMMENT ON COLUMN “public”.“app_device”.“region_id” IS ‘区域ID’;
COMMENT ON COLUMN “public”.“app_device”.“features” IS '设备功能集合（有多个功能时，以半角;分隔）
取值：
人脸识别能力：face-anal

车辆识别能力：motor-vehicle-anal

车牌识别能力：plate-no-anal

人体识别能力：body-anal

夜视星光：night

高空抛物识别能力：throw-anal

车辆异常行为识别能力：motor-vehicle-exception-anal

高点相机：high-point

人体测温能力：person-temp

环境测温能力：env-temp

塔吊：tower-crane

例子：face-anal;night’;
COMMENT ON COLUMN “public”.“app_device”.“is_indoor” IS ‘是否室内设备：0-否 1-是’;
COMMENT ON COLUMN “public”.“app_device”.“sync_exclude_col” IS ‘不需要同步的字段名称，支持多个字段用;分隔’;
COMMENT ON COLUMN “public”.“app_device”.“last_online_time” IS ‘最后在线时间’;
COMMENT ON COLUMN “public”.“app_device”.“building_floor_id” IS ‘所属楼层ID’;
COMMENT ON COLUMN “public”.“app_device”.“direction” IS ‘设备方向：1-EAST 2-WEST 3-SOUTH 4-NORTH 5-NORTHEAST 6-SOUTHWEST 7-SOUTHEAST 8-NORTHWEST 9-OTHER’;
COMMENT ON COLUMN “public”.“app_device”.“building_room_id” IS ‘房间ID’;
COMMENT ON COLUMN “public”.“app_device”.“entrance_or_exit” IS ‘出入口：0-非出入口 1-入口 2-出口’;
COMMENT ON COLUMN “public”.“app_device”.“geohash” IS ‘GEOHASH’;
COMMENT ON COLUMN “public”.“app_device”.“ip_address” IS ‘ip地址’;
COMMENT ON COLUMN “public”.“app_device”.“port” IS ‘端口号’;
COMMENT ON COLUMN “public”.“app_device”.“link_url” IS ‘链接url’;
COMMENT ON COLUMN “public”.“app_device”.“protocol_version” IS ‘通信协议版本’;
COMMENT ON COLUMN “public”.“app_device”.“login_name” IS ‘用户名’;
COMMENT ON COLUMN “public”.“app_device”.“login_passwd” IS ‘密码’;
COMMENT ON COLUMN “public”.“app_device”.“camera_soft_version” IS ‘设备软件版本’;
COMMENT ON COLUMN “public”.“app_device”.“user_name” IS ‘控制用户名’;
COMMENT ON COLUMN “public”.“app_device”.“control_port” IS ‘控制端口号’;
COMMENT ON TABLE “public”.“app_device” IS ‘设备’;