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

视图、序列、触发器、存储过程1、视图视图是从一个或多个基本表（或视图）导出的“虚表”。视图在使用之前必须创建，一旦创建，就可以像基本表一样被查询，也可以在视图上创建新的视图。视图是只读的，不允许对视图进行插入、删除和更新。 注意：HighGo DB 目前不直接支持物化视图。这一点与 oracle 不相同，迁移数据的时候，若有物化视图，需要通过规则进行转化。创建和删除视图highgo=# \h create viewCommand:     CREATE VIEWDescription: define a new viewSyntax:CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]    AS queryhighgo=# \h drop viewCommand:     DROP VIEWDescription: remove a viewSyntax: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 SEQUENCEhighgo=# 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 1highgo=# 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 TABLEhighgo=#  insert into studentseq(name) values('name1'); INSERT 0 1highgo=# insert into studentseq(name) values('name2'); INSERT 0 1highgo=#  insert into studentseq(name) values('name3'); INSERT 0 1highgo=#  select currval('seqtest');  currval ---------     105(1 row)2.3序列的删除highgo=# \h drop sequenceCommand:     DROP SEQUENCEDescription: remove a sequenceSyntax:DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]注意：当有正在使用的序列是无法删除的。 highgo=# drop sequence seqtest;错误:  无法删除 序列 seqtest 因为有其它对象倚赖它DETAIL:  表 studentseq 字段 num的缺省 倚赖于 序列 seqtestHINT:  使用 DROP .. CASCADE 把倚赖对象一并删除.highgo=# drop table studentseq;DROP TABLEhighgo=# drop sequence seqtest;DROP SEQUENCE3、触发器3.1触发器的创建语法结构highgo=# \h create triggerCommand:     CREATE TRIGGERDescription: define a new triggerSyntax: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 需要被触发监控，我们建立一个日志表 aloghighgo=#  create table a(field1 varchar); CREATE TABLEhighgo=#  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 1highgo=# update a set field1='bbb';UPDATE 1highgo=# delete from a;DELETE 1highgo=# 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 TRIGGERhighgo=# drop function  fun1();DROP FUNCTION4、存储过程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 FUNCTIONhighgo=# select fun3(9); fun3 ------   10(1 row)注意：不能使用 create or replace procedure 存储过程名（）来进行创建，这一点是与 oracle 有所不同的。