PostgreSQL筆記 預存程序 迴圈插入資料

 CREATE OR REPLACE FUNCTION autoGenFeature_function() RETURNS text AS<br />$$<br />DECLARE<br />b_count int;<br />beacon_id int;<br />_total int;<br /> BEGIN<br />select count(*) into b_count from beacon;<br />_total := b_count;<br />while b_count>0 loop<br />select pid into beacon_id from beacon limit 1 offset b_count-1;<br />insert into workfeature(typeid, typeactid, beaconid, creatorid, createdate, remark) values (7, 7, beacon_id, 1, now(), '系統自動產生的航標巡檢作業');<br />b_count := b_count-1;<br />end loop;<br /> RETURN '一共添加了' || _total || '個航標的巡檢作業';<br /> END;<br />$$<br />LANGUAGE plpgsql;

 

 

 

 

PostgreSQL 預存程序小樣本

 

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

 

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