Postgresql Function

最新推荐文章于 2024-07-31 16:35:24 发布

aoci4631

最新推荐文章于 2024-07-31 16:35:24 发布

阅读量169

点赞数

文章标签：数据库

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

版权

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;