PostgreSQL笔记 存储过程 循环插入数据

 CREATE OR REPLACE FUNCTION autoGenFeature_function() RETURNS text AS $$DECLAREb_count int;beacon_id int;_total int; BEGINselect count(*) into b_count from beacon;_total := b_count;while b_count>0 loopselect pid into beacon_id from beacon limit 1 offset b_count-1;insert into workfeature(typeid, typeactid, beaconid, creatorid, createdate, remark) values (7, 7, beacon_id, 1, now(), '系统自动生成的航标巡检作业');b_count := b_count-1;end loop; RETURN '一共添加了' || _total || '个航标的巡检作业'; END;$$ LANGUAGE plpgsql;

 

 

 

 

PostgreSQL 存储过程小示例

 

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END;$$ LANGUAGE plpgsql;

 

CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;