PostgreSQL函數用法

 

測試環境:win2003+PostgreSQL8.3(PostgreSQL89.0的不好用)

 

一、建立資料庫語言

1.開啟「開始」菜單/程式/PostgreSQL 8.3/命令提示字元

2.執行命令“createlang -U postgres plpgsql postgres”

如(E:/Program Files/PostgreSQL/8.3/bin>createlang -U postgres plpgsql postgres)

 

二、建立資料庫

create table co_schedule(n_progid int,dt_starttime timestamp,dt_endtime timestamp);

三、建立函數:
create function add_program_time(int4,timestamp,int4,int4,int4) returns bool as '
declare
    prog_id alias for $1;
    duration_min alias for $3;
    period_min alias for $4;
    repeat_times alias for $5;
    i int;
    starttime timestamp;
    ins_starttime timestamp;
    ins_endtime timestamp;
begin
    starttime :=$2;
    i := 0;
    while i<repeat_times loop
        ins_starttime := starttime;
        ins_endtime := timestamp_pl_interval(ins_starttime, CAST(duration_min || ''mins'' AS interval));
        starttime := timestamp_pl_interval(ins_starttime, CAST(period_min || ''mins'' AS interval));
        insert into co_schedule values(prog_id,ins_starttime,ins_endtime);
        i := i+1;
    end loop;
    if i<repeat_times then
        return false;
    else
        return true;
    end if;
end;
'language 'plpgsql';

四、執行函數
select add_program_time(1,'2002-10-20 0:0:0','5','120','5');

 

五、查看函數運行後的結果:
select * from co_schedule;