pg学习_视图、序列、触发器、存储过程

视图、序列、触发器、存储过程
1、视图
视图是从一个或多个基本表（或视图）导出的“虚表”。视图在使用之前必须创建，一旦创建，就可以像基本表一样被查询，也可以在视图上创建新的视图。视图是只读的，不允许对视图进行插入、删除和更新。 
注意：HighGo DB 目前不直接支持物化视图。这一点与 oracle 不相同，迁移数据的时候，若有物化视图，需要通过规则进行转化。
创建和删除视图
highgo=# \h create view
Command:     CREATE VIEW
Description: define a new view
Syntax:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query

highgo=# \h drop view
Command:     DROP VIEW
Description: remove a view
Syntax:
DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

2、序列
序列是 HighgoDB 的一种特殊计算器，它由用户创建，由系统管理。创建一个序列后，可以将其作为某个字段的默认值。在 insert 命令的执行期间，序列的值会被自动生成，并作为数据行的唯一性编号，存放在该数据行相应的字段中。 
2.1序列的创建
序列不能由系统自动创建，必须用 create sequence 命令显示创建。
highgo=#  create sequence seqtest; 
CREATE SEQUENCE
注意：HighGo DB 默认情况下，建立的序列最大值可以是：9223372036854775807，超过此值将是无效的。 
这一点与 oracle 是有区别的，oracle 的最大值是：999999999999999999999999999。
序列的操作
Nextvul 序列名）：函数返回下一个可用序列计数器的值，并自动将序列计算器的值加 1 
Currval 序列名）：函数返回当前可用的序列计数器的值，并且不修改序列计数器的值 
Setval 序列名，新计数器值）：函数将序列计数器的值设置为指定的值。 
highgo=#  create sequence seqtest; 
CREATE SEQUENCE
highgo=# select currval('seqtest');
错误:  在此会话中序列 "seqtest" 的 currval 仍没被定义
highgo=# select nextval('seqtest');
 nextval 
---------
       1
(1 row)

highgo=# select currval('seqtest');
 currval 
---------
       1
(1 row)

highgo=# select nextval('seqtest');
 nextval 
---------
       2
(1 row)

highgo=# select currval('seqtest');
 currval 
---------
       2
(1 row)

highgo=# select nextval('seqtest');
 nextval 
---------
       3
(1 row)

highgo=# select currval('seqtest');
 currval 
---------
       3
(1 row)

highgo=# select setval('seqtest',100);
 setval 
--------
    100
(1 row)

highgo=# select currval('seqtest');
 currval 
---------
     100
(1 row)

highgo=# select nextval('seqtest');
 nextval 
---------
     101
(1 row)

highgo=# select currval('seqtest');
 currval 
---------
     101
(1 row)
2.2序列的使用
使用序列插入记录
highgo=# select * from test1;
 id |  name  
----+--------
  1 | adam
  2 | lilith
(2 rows)
highgo=# select currval('seqtest');
 currval 
---------
     101
(1 row)
highgo=# insert into test1 values(
highgo(# nextval('seqtest'),'eva');
INSERT 0 1
highgo=# select * from test1;
 id  |  name  
-----+--------
   1 | adam
   2 | lilith
 102 | eva
(3 rows)
创建表的时候，默认某个字段为序列值
highgo=# create table studentseq( 
highgo(#  num int default nextval('seqtest'), 
highgo(#  name varchar(10) 
highgo(# );
CREATE TABLE
highgo=#  insert into studentseq(name) values('name1'); 
INSERT 0 1
highgo=# insert into studentseq(name) values('name2'); 
INSERT 0 1
highgo=#  insert into studentseq(name) values('name3'); 
INSERT 0 1
highgo=#  select currval('seqtest'); 
 currval 
---------
     105
(1 row)
2.3序列的删除
highgo=# \h drop sequence
Command:     DROP SEQUENCE
Description: remove a sequence
Syntax:
DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
注意：当有正在使用的序列是无法删除的。 
highgo=# drop sequence seqtest;
错误:  无法删除 序列 seqtest 因为有其它对象倚赖它
DETAIL:  表 studentseq 字段 num的缺省 倚赖于 序列 seqtest
HINT:  使用 DROP .. CASCADE 把倚赖对象一并删除.
highgo=# drop table studentseq;
DROP TABLE
highgo=# drop sequence seqtest;
DROP SEQUENCE

3、触发器
3.1触发器的创建
语法结构
highgo=# \h create trigger
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
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 )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE
例如： 假设有一个表 a 需要被触发监控，我们建立一个日志表 alog
highgo=#  create table a(field1 varchar); 
CREATE TABLE
highgo=#  create table alog(opertype varchar,oldfield1 varchar,newfield1 varchar); 
CREATE TABLE
创建触发器函数： 
highgo=# create or replace function fun1() 
highgo-# returns "trigger" as $body$ begin 
highgo$# if (TG_OP='DELETE')then 
highgo$# insert into alog values('delete',OLD.field1,null); 
highgo$# elsif(TG_OP='UPDATE')then 
highgo$# insert into alog values('update',OLD.field1,NEW.field1); 
highgo$# elsif(TG_OP='INSERT')then 
highgo$# insert into alog values('insert',null,NEW.field1); 
highgo$# end if; 
highgo$# return null; 
highgo$# end; 
highgo$# $body$ 
highgo-# language 'plpgsql' volatile; 
CREATE FUNCTION
创建触发器： 
highgo=# create trigger xx 
highgo-# after insert or update or delete on a 
highgo-# for each row 
highgo-# execute procedure fun1();
CREATE TRIGGER
测试：
highgo=# insert into a values('aaa');
INSERT 0 1
highgo=# update a set field1='bbb';
UPDATE 1
highgo=# delete from a;
DELETE 1
highgo=# select * from alog;
 opertype | oldfield1 | newfield1 
----------+-----------+-----------
 insert   |           | aaa
 update   | aaa       | bbb
 delete   | bbb       | 
(3 rows)
3.2触发器的删除
highgo=# DROP TRIGGER  xx ON a CASCADE; 
DROP TRIGGER
highgo=# drop function  fun1();
DROP FUNCTION

4、存储过程
highgo=# create function fun3(int) 
highgo-# returns int as 
highgo-# $body$ 
highgo$# begin 
highgo$# return $1+1; 
highgo$# end; 
highgo$# $body$ 
highgo-# language 'plpgsql'; 
CREATE FUNCTION
highgo=# select fun3(9);
 fun3 
------
   10
(1 row)
注意：不能使用 create or replace procedure 存储过程名（）来进行创建，这一点是与 oracle 有所不同的。