PGSQL

创建序列

create sequence if not exists inf_topology_node_device_id_seq increment by 1 minvalue 1 no maxvalue start with 1;

nextval('inf_topology_node_device_id_seq'::regclass)

//查询序列
SELECT c.relname FROM pg_class c WHERE c.relkind ='S';

函数

CREATE OR REPLACE FUNCTION merge_db(key1 varchar, key2 varchar, data varchar) RETURNS INT AS
$$
BEGIN
    LOOP
				IF (syncstatus is null or syncstatus = '') THEN
						syncstatus='0';
				END IF;
        UPDATE info_topology_node_device 
				SET device_id = deviceid, 
					device_name = devicename,
					group_id=groupid,
					group_name=groupname,
					syn_status=syncstatus 
				WHERE apm_id = apmid and template_component_id = templatecomponentid;
        IF found THEN
            RETURN 1;
        END IF;
        INSERT INTO info_topology_node_device 
				(apm_id,template_component_id,template_component_name,device_id,device_name,group_id,group_name) 
				VALUES (apmid , templatecomponentid ,templatecomponentname , deviceid ,devicename ,groupid ,groupname ) ;
				IF found THEN
            RETURN 1;
        END IF;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

插入更新

--多字段联合约束
INSERT INTO info_topology_node_device (apm_id,template_component_id,template_component_name,device_id,device_name,group_id,group_name) 
     VALUES ('2','2','1','1','1','1','1')
ON CONFLICT ON CONSTRAINT simple_unique DO  UPDATE SET 
device_id = '2', device_name = '2';

树形结构的查询

with recursive cte as (
-- 先查询root节点
select id,name,alias,group_id,parent_id,template_id,apm_id,status,sync_status,srp_version FROM info_topology_node where id = '0'
union all
-- 通过cte递归查询root节点的直接子节点
select origin.id, origin.name,origin.alias,origin.group_id, cte.id as parent_id,origin.template_id,origin.apm_id,origin.status,origin.sync_status,origin.srp_version
from cte join info_topology_node as origin on origin.parent_id = cte.id )
select id,name,alias,group_id,parent_id,template_id,apm_id,status,srp_version  from cte

统计

--节点的设备绑定信息 s1未绑定设备， s2绑定设备
select * from info_topology topology LEFT JOIN(
	select group_id,sum(case when d_apm_id is null then 0 else 1 end)  s1,sum(case when d_apm_id is not null then 0 else 1 end) s2 
	from (
		select distinct node.group_id,node.id,node.apm_id,device.apm_id d_apm_id from info_topology_node node  LEFT JOIN info_topology_node_device device on node.apm_id=device.apm_id
	) a group by group_id
) aaaa on topology.id=aaaa.group_id