
CREATE [ CONSTRAINT ]TRIGGER name { BEFORE │ AFTER │ INSTEAD OF } { event [ OR ... ] }
ON table_name
[FROM referenced_table_name ]
{ NOT DEFERRABLE |〔DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED
} }
[ FOR [ EACH ] {ROW | STATEMENT } ]
[ WHEN( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )

建触发器的步骤:
先为触发器建一个执行函数，此函数的返回类型为触发器类型;然后即可创建相应的触发器。
下面使用一个例子来讲解触发器的使用，假设有一张学生表( student)和一张考试成绩表( score)，定义如下:


CREATE TABLE student (
student_no int primary key,student_name varchar ( 40),
age int
);
CREATE TABLE score (
student_no int,
chinese_score int,
math_score int,
test_date date
) ;

6.5.2 删除触发器

删除触发器的语法如下:

DROP TRIGGER [ IF EXISTS ] name ow table [ CASCADE | RESTRICT ];

6.5.3 触发器函数中的特殊变量

6.6 事件触发器

PostgreSQL 从9.3版开始支持事件触发器(Event Trigger)，这种触发器主要用于弥补PostgreSQL以前版本不支持DDL触发器的功能。目前，事件触发器支持以下三种 DDL事件。
（1） ddl_command_start：一个DDL开始执行前被触发。
（2）ddl_command_end：一个DDL执行完成后被触发。
（3）sql_drop:删除一个数据库对象前被触发。
由于事件触发器涉及的权限较大，比如能禁止DDL操作等,所以只有超级用户才能创建和修改事件触发器。

6.7 表空间

6.7.1 表空间的定义

有时我们需要把不同的表放到不同的存储介质或不同的文件系统下，这时就需要使用表空间。在 PostgreSQL中，表空间实际上是为表指定一个存储的目录。在创建数据库时可以为数据库指定默认的表空间。创建表和索引时可以指定表空间，这样表、索引就可以存储到表空间对应的目录下了。

6.7.2 表空间的使用

创建表空间的语法为:


CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'
示例如下:·
osdba=# CREATE TABLESPACE tbs_data location '/data/pgdata ';
CREATE TABLESPACE

创建数据库时可以指定默认的表空间，这样以后在这个数据库中创建表、索引时，就可自动存储到这个表空间指定的目录下:

create database db01 tablespace tbs_data;

改变一个数据库的默认表空间时,语法如下:


osdba=# ALTER DATABASE db01 set TABLESPACE tbs_data;
ALTER DATABASE

注意，在做该操作时，必须没有人同时连接到这个数据库上，否则会报如下错误:


osdba=#ALTER DATABASE db01 set TABLESPACE pg_default;
ERROR:database "db01" is being accessed by other users
DETAIL:There is 1 other session using the database.

另外，改变数据库的默认表空间时，数据库中已有表的表空间并不会改变。创建表的时候也可以指定表空间，命令如下:


osdba=# create table test01(id int,note text) tablespace tbs_data;
CREATE TABLE

创建索引的时候同样可以指定表空间，命令如下:


osdba=# create index idx_test01_id on test01(id) tablespace tbs_data;
CREATE INDEX

建唯一约束时可指定约束索引的表空间，命令如下:


osdba=# ALTER TABLE test01 ADD CONSTRAINT unique_test01_id unique(id) USING INDEX
TABLESPACE tbs_data;
NOTICE:ALTER TABLE / ADDUNIQUE will create implicit index "unique_test01_id"
for table "test01"
ALTER TABLE

增加主键时也可指定主键索引的表空间，命令如下:


ALTER TABLE test01 ADD CONSTRAINT pk_test01_id primary key(id) USING INDEX
TABLESPACE tbs_data;

把表从一个表空间移动到另一个表空间的命令如下:


osdba=# alter table test01 set tablespace pg_default;
ALTER TABLE

注意，在移动表的时候会锁表，此时对该表的所有操作都将被阻塞，包括SELECT操作，所以请考虑在合适的时机做这个操作。

6.8 视图

6.8.1 视图的定义

通俗的说，视图就是由查询语句定义的虚拟表。对用户来说，看到的视图就如同一张真的表。从视图中看到的数据可能来自数据库中的一张或多张表，也可能来自外部，这主要取决于视图的查询语句是如何定义的。
使用视图一般有以下几个原因。
（1）可使复杂的查询易于理解和使用。
（2）安全原因。视图可以隐藏一些数据，如在一张用户表中，可以通过定义一张视图，把密码等敏感字段去掉。
（3）把一些函数返回的结果映射成视图。
一般数据库提供的视图大多是只读的，在 PostgreSQL数据库中提供的视图默认也是只读的，但可以使用规则系统做出一张可更新的视图。

6.8.2 创建视图

创建视图的语法如下:


CREATE [ OR REPLACE ] ( TEMP |TEMPORARY ] VIEW name [ ( column_name [，...] ) ]
As query

例如，建一张用户表“users”，其中包含了敏感字段“password"，现在要建一张视图把敏感字段“password”排除掉，如下:


CREATE TALBE users (
id int,
user_name varchar ( 40),
password varchar (256),
user_email text, 
user_mark text
) ;
CREATE VIEW vw_users AS SELEcT id,user_name，user_email,user_mark FROM users;

6.9 索引

6.9.1 索引简介

索引是数据库中一种快速查询数据的方法。索引中记录了表中一列或多列的值与其物理位置之间的对应关系，就好比是一本书正文面的目录，通过目录后面的页号能快速定位到需要查询的内容。
建立索引的好处是加快对表中记录的查找或排序。但建索引也是有代价的:
（1）增加了数据库的存储空间。
（2）在插人和修改数据时要花费较多的时间，因为索引也要随之更新。
索引除了有加快查询的作用外，还有一些其他的用处，比如唯一索引还起到唯一约束的作用。

6.9.2 索引的分类

6.9.3 创建索引

创建索引的语法如下:


CREATE [ UNIQUE ]INDEX[ CONCURRENTLY ] [ name ] ON table_name ( USING method ]
( { column_name | ( expression ) }[ COLLATE collation ] [ opclass ] [ ASC |
DESC ] [NULLS { FIRST l LAST }] [, ...] )
[WITH( storage_parameter = value l, ... ] ) ]
[TABLESPACE tablespace_name ]
[WHERE predicate ]

一般在创建索引的过程中，会把表的数据都读一遍，这个过程所用时间由表的大小决定，对于比较大的表，可能会花很久的时间。在创建索引的过程中，对表的查询可以正常运行，但对表的增、删、改等操作需要等索引建完后才能进行，为此PostgreSQL提供了一种并发创建索引的方法，具体见下一节的内容。

6.9.4 修改索引

修改索引的语法如下:


ALTER INDEXname RENAME TO new name
ALTER INDEX name SETTABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEx name RESET ( storage_parameter [, ... ] )

6.9.5 删除索引

删除索引的语法如下:

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE |RESTRICT ]

6.10 用户及权限管理

6.10.1 用户和角色

PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常会把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在PostgreSQL 中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。
用户和角色在整个数据库实例中都是全局的，且在同一个实例中的不同数据库中，看到的用户也都是相同的。
在初始化数据库系统时，有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户“postgres”(通常把数据库安装在此用户下)下的，这个数据库超级用户的名称也会叫“postgres”。可以用这个超级用户连接数据库，然后创建出更多的用户。

6.10.2 创建用户和角色

创建用户与角色的语法如下:


CREATE ROLE name [ [ WITH ] option [ ... ] ]
或
CREATE USER name [ [ WITH ] option [ ... ] ]

6.11 事务、并发、锁

待续......

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】

PostgreSQL修炼之道笔记之触发器与表空间等（十）_pg怎样删除触发器

第六章 逻辑结构管理（三）

6.5 触发器

6.5.1 创建触发器