DECLARE    curParkingTime TIMESTAMP ;    curLeavingTime TIMESTAMP;        parkingSpaceIndexCode VARCHAR[];    days INTEGER; --间隔天数    duration INTEGER; -- 停车时长    i INTEGER;        plateNo VARCHAR; -- 车牌号  BEGIN    curLeavingTime = $2;    --RAISE INFO ' aaa  aaa';        --获取indexcode数组    select ARRAY_AGG(indexcode) INTO parkingSpaceIndexCode from pms_parkingspace;        if (array_length(parkingSpaceIndexCode, 1)>0) then            i :=0;            WHILE i < $1 LOOP                i := i+1;                for index in 1..array_length(parkingSpaceIndexCode, 1) loop                    --得到停车时长和车牌5位随机数                    select '浙A'|| trunc(random() * (99999-10000 + 1) + 10000) into plateNo;                    duration = cast (random()*1000 as INTEGER);                    --使leavingtime随机                    curLeavingTime = curLeavingTime + cast(cast (random()*100 as INTEGER)|| 'min' as INTERVAL);                    --leavingtime减去duration得到parkingtime                    curParkingTime = curLeavingTime - cast( duration || 'min' as INTERVAL) ;                    --RAISE NOTICE 'indexCode is %,plateNo is %',parkingSpaceIndexCode[index],plateNo;                    INSERT INTO pms_parking_space_history(space_index_code,parking_time,leaving_time,parking_duration,plate_no) values(parkingSpaceIndexCode[index],curParkingTime,curLeavingTime,duration,plateNo);                                    end loop;                             --使下次循环的日期递减             curLeavingTime = curParkingTime - INTERVAL '1 day';            END LOOP;        end if;  END;

执行

select generate_history_data(2, '2016-11-30 10:10:00')

 导出后的建函数语句：

CREATE OR REPLACE FUNCTION "public"."generate_history_data(loopcounts int4, enddate timestamp)"(loopcounts int4, enddate timestamp)  RETURNS "pg_catalog"."void" AS $BODY$  DECLARE    curParkingTime TIMESTAMP ;    curLeavingTime TIMESTAMP;        parkingSpaceIndexCode VARCHAR[];    days INTEGER; --间隔天数    duration INTEGER; -- 停车时长    i INTEGER;        plateNo VARCHAR; -- 车牌号  BEGIN    curLeavingTime = $2;    --RAISE INFO ' aaa  aaa';        --获取indexcode数组    select ARRAY_AGG(indexcode) INTO parkingSpaceIndexCode from pms_parkingspace;        if (array_length(parkingSpaceIndexCode, 1)>0) then            i :=0;            WHILE i < $1 LOOP                i := i+1;                for index in 1..array_length(parkingSpaceIndexCode, 1) loop                    --得到停车时长和车牌5位随机数                    select '浙A'|| trunc(random() * (99999-10000 + 1) + 10000) into plateNo;                    duration = cast (random()*1000 as INTEGER);                    --使leavingtime随机                    curLeavingTime = curLeavingTime + cast(cast (random()*100 as INTEGER)|| 'min' as INTERVAL);                    --leavingtime减去duration得到parkingtime                    curParkingTime = curLeavingTime - cast( duration || 'min' as INTERVAL) ;                    --RAISE NOTICE 'indexCode is %,plateNo is %',parkingSpaceIndexCode[index],plateNo;                    INSERT INTO pms_parking_space_history(space_index_code,parking_time,leaving_time,parking_duration,plate_no) values(parkingSpaceIndexCode[index],curParkingTime,curLeavingTime,duration,plateNo);                                    end loop;                             --使下次循环的日期递减             curLeavingTime = curParkingTime - INTERVAL '1 day';            END LOOP;        end if;  END;  $BODY$  LANGUAGE 'plpgsql' VOLATILE COST 100;ALTER FUNCTION "public"."generate_history_data(loopcounts int4, enddate timestamp)"(loopcounts int4, enddate timestamp) OWNER TO "postgres";