PostgreSQL函数用法

iszjw

　　测试环境: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;