Chinaunix

标题: 一个PostgreSQL存储过程和类型转换的例子(修正版) [打印本页]

作者: wwashington 时间: 2006-03-26 02:09
标题: 一个PostgreSQL存储过程和类型转换的例子(修正版)
发信人: Wwashington (Jacky), 信区: NewSoftware
标  题: 一个PostgreSQL存储过程和类型转换的例子(修正版)
发信站: 水木社区 (Sun Mar 26 02:01:04 2006), 站内

前言:

我最近研究 EnterpriseDB(一个以 PostgreSQL 为核心的企业级数据库)，看到系统
默认内置了一些存储过程和触发器，于是上网看看有没一些比较简单的案例，作为
研究语法和逻辑的样板。在 Google 查询“PostgreSQL 存储过程”找到几个站点都
转载了同一篇文章，但是内容有错，因此本人将其重写，希望对大家有帮助。

BTW: 开始以为是转载的网站弄丢了内容，后来搜索“timestamp_pl_span”这个出错
的函数，找到了署名原文，不过内容依然错误。经过实践检验，发现并修正两个问题。
一是timestamp_pl_span不在存在，改为timestamp_pl_interval，二是interval类型
的数据不能被自动转换，需要利用 CAST(xxx AS interval) 来进行类型转换。

说明:

感谢本文的原作者dreamsheep提供了一个简单的例子，可惜随着时间推移技术更新，
部分内容已经无法正常使用。我在原文基础上做了一些修正，引用本文请注明出处。

原文:

http://book.chinaz.com/others/web/web/php/index7/17.htm
一个PostgreSQL存储过程的例子 (作者:unknown)
http://www.faq-it.org/archives/m ... dc5a2628e42884d.php
如何将MSSQL2000的存储过程转换成为postgreSQL的函数 (作者:dreamsheep)

需求:
给出如下条件进行批处理编排
- 开始日期时间
- 重复间隔(分钟)
- 重复次数
要求在档期内重复安排节目播出, 比如: 2003.01.01 08:00 开始每隔240分钟
   播出一次, 一共播出100次, 或者用户自行设定的其他时间。

数据库表格(CO_SCHEDULE)
------------------------------
N_PROGID       INT
DT_STARTTIME TIMESTAMP
DT_ENDTIME    TIMESTAMP

存储过程的实现如下所示。

//支持语言:

首先要让你的数据库支持 plpgsql
createlang -U $dbowner plpgsql $dbname

例如：我的用户名 jackyz，数据库名 mydb，
createuser -U postgres jackyz
psql -U postgres template1
> alter user jackyz with password 'xxx';
> \q

notepad %PGDATA%\pg_hba.conf {WinXp}
vi $PGDATA/pg_hba.conf       {Linux}
--> 请参考网上的文章设置 IP 和 MD5 访问。
--> 必须重起数据库使得修改后的配置生效。

createdb -U jackyz mydb
createlang -U jackyz plpgsql mydb

//创建库表:

psql -U jackyz mydb
drop table co_schedule;
create table co_schedule(n_progid int,dt_starttime timestamp,dt_endtime timestamp);

//创建函数:

drop function add_program_time(int4,timestamp,int4,int4,int4);
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';

//执行查询:

delete from co_schedule;
select add_program_time(1,'2002-10-20 0:0:0','5','120','5');
select * from co_schedule;

//运行结果:

mydb=> select * from co_schedule;
n_progid | dt_starttime    |    dt_endtime
----------+---------------------+---------------------
      1 | 2002-10-20 00:00:00 | 2002-10-20 00:05:00
      1 | 2002-10-20 02:00:00 | 2002-10-20 02:05:00
      1 | 2002-10-20 04:00:00 | 2002-10-20 04:05:00
      1 | 2002-10-20 06:00:00 | 2002-10-20 06:05:00
      1 | 2002-10-20 08:00:00 | 2002-10-20 08:05:00
(5 行)

//执行查询:

select add_program_time(2,'2004-10-30 0:0:0','10','60','3');
select * from co_schedule where n_progid=2;
select * from co_schedule;

//运行结果:

mydb=> select * from co_schedule where n_progid=2;
n_progid | dt_starttime    |    dt_endtime
----------+---------------------+---------------------
      2 | 2004-10-30 00:00:00 | 2004-10-30 00:10:00
      2 | 2004-10-30 01:00:00 | 2004-10-30 01:10:00
      2 | 2004-10-30 02:00:00 | 2004-10-30 02:10:00
(3 行)

mydb=> select * from co_schedule;
n_progid | dt_starttime    |    dt_endtime
----------+---------------------+---------------------
      1 | 2002-10-20 00:00:00 | 2002-10-20 00:05:00
      1 | 2002-10-20 02:00:00 | 2002-10-20 02:05:00
      1 | 2002-10-20 04:00:00 | 2002-10-20 04:05:00
      1 | 2002-10-20 06:00:00 | 2002-10-20 06:05:00
      1 | 2002-10-20 08:00:00 | 2002-10-20 08:05:00
      2 | 2004-10-30 00:00:00 | 2004-10-30 00:10:00
      2 | 2004-10-30 01:00:00 | 2004-10-30 01:10:00
      2 | 2004-10-30 02:00:00 | 2004-10-30 02:10:00
(8 行)

作者: flowingtree 时间: 2006-03-26 09:40
谢谢分享

作者: xktop 时间: 2006-03-27 09:58
太好了，正需要，感谢楼主。

作者: dlts26 时间: 2010-09-21 14:48
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));
加粗的地方在我的pgAdmin上编辑的时候，不能识别，改为单引号后运行成功。是因为编译软件的不同，还是楼主不注意写错了？

作者: renxiao2003 时间: 2010-09-21 20:32
是自己创造的吗？》

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)