CREATE OR REPLACE FUNCTION autoGenFeature_function() RETURNS text AS $$ DECLARE b_count int; beacon_id int; _total int; BEGIN select count(*) into b_count from beacon; _total := b_count; while b_count>0 loop select 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(),'系统<a href="https://www.jb51.cc/tag/zidong/" target="_blank" class="keywords">自动</a><a href="https://www.jb51.cc/tag/shengcheng/" target="_blank" class="keywords">生成</a>的航标巡检作业'); b_count := b_count-1; end loop; RETURN '一共<a href="https://www.jb51.cc/tag/tianjia/" target="_blank" class="keywords">添加</a>了' || _total || '个航标的巡检作业'; END; $$ LANGUAGE plpg<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>;

Postgresql 存储过程小示例

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpg<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>;

CREATE FUNCTION add(integer,integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> IMMUTABLE RETURNS NULL ON NULL INPUT;