Postgresql Function

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;

Postgresql Function

原文：http://www.cnblogs.com/shua/p/4365877.html