CREATE OR REPLACE FUNCTION insert_default_system_app(varchar, integer,varchar,varchar) RETURNS bigint AS $$
declare
ownerid integer;
tempSql varchar := '';
tempId bigint;
rowcount int4;
selectOwnerIdSql varchar := '';
BEGIN
tempSql:=format('dbname=systemdb host=%s port=%s user=%s password=%s', $1,$2,$3,$4 );
perform dblink_connect('test_dblink',tempSql);
rowcount := 0;
selectOwnerIdSql:=format('SELECT DISTINCT owner_id FROM hm_base_serv WHERE service_type=%L AND owner_id!=0 AND app_code NOT IN(1258,1259,1260)','APPLICATION');
for ownerid in execute selectOwnerIdSql loop
tempSql:=format('select allocatorEntityId(%s,3)', ownerid ); --call other db connect query data
select * INTO tempId from dblink('test_dblink',tempSql) as t1(id bigint);
INSERT INTO hm_base_serv(id, owner_id, name, full_name, app_code, predefined, idle_timeout, created_at, updated_at, application_description, app_serv_catg_id, service_type)
VALUES(tempId,ownerid,'PTCLKCAR','Point Click Care',1258,false,300,now(),now(),'Point Clock Health Management System',48006,'APPLICATION');
tempId:=tempId+1;
INSERT INTO hm_base_serv(id, owner_id, name, full_name, app_code, predefined, idle_timeout, created_at, updated_at, application_description, app_serv_catg_id, service_type)
VALUES(tempId,ownerid,'SHORETEL', 'Shoretel',1259,false,300,now(),now(),'shoretel application',48012,'APPLICATION');
tempId:=tempId+1;
INSERT INTO hm_base_serv(id, owner_id, name, full_name, app_code, predefined, idle_timeout, created_at, updated_at, application_description, app_serv_catg_id, service_type)
VALUES(tempId,ownerid,'TESTNAV','Testnav',1260,false,300,now(),now(),'Testnav site and its services',48004,'APPLICATION');
tempId:=tempId+1;
rowcount := rowcount + 1;
end loop;
perform dblink_disconnect('test_dblink');
return rowcount;
END;
$$ LANGUAGE plpgsql;
Execute function
SELECT insert_default_system_app('127.0.0.1',5432,postgres,postgres);
system_db function:
CREATE OR REPLACE FUNCTION allocatorEntityId(i integer,allocatorSize integer) RETURNS bigint AS $$
DECLARE entity entity_id %rowtype;
alloc_size integer;
BEGIN
LOOP
alloc_size=($2/5+1)*5;
select * INTO entity from entity_id where owner_id=$1;
update entity_id set current_max=entity.current_max+alloc_size, version=entity.version+1
where owner_id=$1 and version=entity.version;
IF found THEN
RETURN entity.current_max+1;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;