一个PostgreSQL存储过程和类型转换的例子(修正版)

wwashington

发信人: 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

谢谢分享

xktop

太好了，正需要，感谢楼主。

dlts26

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

是自己创造的吗？》