postgresql手册

一、Postgresql中可以直接对时间进行加减运算：

查询系统当前时间：

selectnow();
或者
selectcurrent_timestamp;

SELECT now()::timestamp + '1 year'; --当前时间加1年
SELECT now()::timestamp + '1 month'; --当前时间加一个月
SELECT now()::timestamp + '1 day'; --当前时间加一天
SELECT now()::timestamp + '1 hour'; --当前时间加一个小时
SELECT now()::timestamp + '1 min'; --当前时间加一分钟
SELECT now()::timestamp + '1 sec'; --加一秒钟
select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec'; --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ' day')::interval FROM table --把col字段转换成天 然后相加

二、Postgresql存储过程小案例

1、创建存储过程格式：

CREATEORREPLACEFUNCTION函数名(参数1，[整型int4,整型数组_int4,...])
RETURNS返回值类型AS
$BODY$
DECLARE
变量声明
BEGIN
函数体
END;
$BODY$
LANGUAGE‘plpgsql’VOLATILE;

实例：

CREATEORREPLACEFUNCTIONmessage_deletes(ids"varchar",useridint8)
RETURNSint4AS
$BODY$
DECLARE
rRECORD;
delbool;
numint4:=0;
sql"varchar";
BEGIN
sql:='selectid,receiveuserid,senduserid,senddelete,receivedeletefrommessagewhereidin('||ids||')';
FORrINEXECUTEsqlLOOP
del:=false;
IFr.receiveuserid=useridandr.senduserid=useridTHEN
del:=true;
ELSEIFr.receiveuserid=useridTHEN
IFr.senddelete=falseTHEN
updatemessagesetreceivedelete=truewhereid=r.id;
ELSE
del:=true;
ENDIF;
ELSEIFr.senduserid=useridTHEN
IFr.receivedelete=falseTHEN
updatemessagesetsenddelete=truewhereid=r.id;
ELSE
del:=true;
ENDIF;
ENDIF;
IFdelTHEN
deletefrommessagewhereid=r.id;
num:=num+1;
ENDIF;
ENDLOOP;
returnnum;
END;
$BODY$
LANGUAGE'plpgsql'VOLATILE;

2、查看自己建立的存储过程或者说函数的命令

SELECT
pg_proc.pronameAS"函数名称",pg_type.typnameAS"返回值数据类型",pg_proc.pronargsAS"参数个数"
FROM
pg_proc
JOINpg_type
ON(pg_proc.prorettype=pg_type.oid)
WHERE
pg_type.typname!='void'
ANDpronamespace=(SELECTpg_namespace.oidFROMpg_namespaceWHEREnspname='public');

例如：

函数名称|返回值数据类型|参数个数
---------------------------+----------------+----------
somefunc|int4|0
getemp|emp|1
delucsmmemprevIoUsdata|trigger|0
deldiskstatusprevIoUsdata|trigger|0

3、删除函数

例如删除delucsmmemprevIoUsdata函数：

delete from pg_proc where pg_proc.proname='delucsmmemprevIoUsdata';

二、Postgresql触发器

参考：Postgresql触发器

Postgresql的触发器是数据库自动执行\指定的数据库事件发生时调用的回调函数。以下是有关Postgresql的触发器的要点：www.yiibai.com

• Postgresql的触发可以指定触发操作前尝试一行（在检查约束之前INSERT，UPDATE或DELETE）或操作完成后（在检查约束之后和INSERT，UPDATE或DELETE（删除）已完成），或替代的操作（在视图上插入，更新或删除的情况下）..

• FOR EACH ROW触发器被标记的操作修改的每一行被称为一次。相比之下，FOR EACH STATEMENT触发器为只执行一次对于任何给定的操作，不管它有多少行修改。

• WHEN子句和触发器动作可能访问的行元素被插入，删除或更新使用的形式NEW.column-name和OLD.column-name，其中列名是从表中的列名的引用该触发器相关联的。www.yiibai.com

• 如果提供WHEN子句，Postgresql的报表只执行WHEN子句为true的行。如果没有提供WHEN子句，Postgresql的语句执行的所有行。

• 如果有多个相同类型的触发器定义了相同的事件，他们将被触发名称是按字母顺序排列。

• BEFORE，AFTER或INSTEAD OF关键字决定何时触发动作将被执行，相对于插入，修改或移除相关的行。www.yiibai.com

• 触发器表，它们与丢弃时自动删除。yiibai.com

• 要修改的表必须存在，在同一数据库中的表或视图，触发器被附加，必须使用表名而不使用database.tablename。

• 约束时指定的选项创建一个约束触发器。这是一个普通的触发器除外，可以调整使用SET（设定）约束的触发器触发的定时相同。预计约束触发器违反他们所实施的限制时引发异常..

语法:

创建触发器的基本语法如下：

CREATETRIGGERtrigger_name[BEFORE|AFTER|INSTEADOF]event_name
ONtable_name[
--Triggerlogicgoeshere....];

Hereevent_namecould beINSERT,DELETE,UPDATE,andTRUNCATEdatabase operation on the mentioned tabletable_name. You can optionally specify FOR EACH ROW after table name.

Following is the Syntax of creating a trigger on an UPDATE operation on one or more specified columns of a table as follows:

CREATETRIGGERtrigger_name[BEFORE|AFTER]UPDATEOFcolumn_name
ONtable_name[
--Triggerlogicgoeshere....];

例子

让我们考虑一个情况下，我们要保持审核COMPANY表中的每一条记录被插入，我们将创建新如下（如果已经创建过，那么删除COMPANY表）

testdb=#CREATETABLECOMPANY(
IDINTPRIMARYKEYNOTNULL,
NAMETEXTNOTNULL,
AGEINTNOTNULL,
ADDRESSCHAR(50),
SALARYREAL);

为了保持审核的测试，我们将创建一个新的表被称为审计将被插入日志消息每当有一个新的记录条目表COMPANY：www.yiibai.com

testdb=#CREATETABLEAUDIT(
EMP_IDINTNOTNULL,
ENTRY_DATETEXTNOTNULL);

这里的ID是审计记录ID，EMP_ID的ID来自COMPANY表，日期将保持公司表时的记录将被创建时间戳。所以，现在让我们创建一个触发器，COMPANY表如下：

testdb=#CREATETRIGGERexample_triggerAFTERINSERTONCOMPANY
FOREACHROWEXECUTEPROCEDUREauditlogfunc();

auditlogfunc（）是一个Postgresql的过程，有以下定义：

CREATEORREPLACEFUNCTIONauditlogfunc()RETURNSTRIGGERAS$example_table$BEGIN
INSERTINTOAUDIT(EMP_ID,ENTRY_DATE)VALUES(new.ID,current_timestamp);
RETURNNEW;
END;$example_table$LANGUAGEplpgsql;

现在，让我们开始COMPANY表插入记录，这将导致在审核表中创建审计日志记录。因此，让我们创建一个COMPANY表记录如下：

testdb=#INSERTINTOCOMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Paul',32,'California',20000.00);

这将创建一个记录COMPANY表如下：yiibai.com

id|name|age|address|salary
----+------+-----+--------------+--------
1|Paul|32|California|20000

同时审核表中创建一条记录。这条记录是一个触发我们已经创建了COMPANY表上的INSERT操作的结果。类似的方式，也可以创建触发器，UPDATE和DELETE操作根据要求。

emp_id|entry_date
--------+-------------------------------
1|2013-05-0515:49:59.968+05:30
(1row)

列出触发器

可以列出当前数据库中的所有触发器从pg_trigger表如下：

testdb=#SELECT*FROMpg_trigger; 
或者 
select trigger_name from information_schema.triggers;

以上Postgresql的表会列出所有触发器。

如果要列出特定表上的触发器，然后使用条款与表名如下：

testdb=#SELECTtgnameFROMpg_trigger,pg_classWHEREtgrelid=pg_class.oidANDrelname='company';

以上Postgresql的表也会列出只有一个条目如下：

tgname
-----------------
example_trigger
(1row)

删除触发器

以下是DROP命令可以用来删除一个现有的触发：yiibai.com

testdb=#DROPTRIGGERtrigger_name on tablename;

三、centos中执行postgresql相关的命令

1、进入数据库终端

psql 数据库名 -U 用户名

2、查看所有的数据库名

\l或者\list