1listen_addresses = '*'

1## 0.0.0.0/0 可以表示所有ipv4地址，::0/0表示所有 IPv6 地址
2local   all             all                                     trust
3host    all             all             0.0.0.0/0            	password
4host    all             all             ::1/128                 password
5local   all     		all                                     trust
6host    all    			all             0.0.0.0/0            	password
7host    all     		all             ::1/128                 password

1psql   #本地连接pg数据库
2
3#角色相关命令
4postgres=## CREATE ROLE admin LOGIN PASSWORD '123456'		
5postgres=## CREATE USER admin PASSWORD '123456'
6postgres=## DROP ROLE admin;
7postgres=## ALTER ROLE admin WITH PASSWORD '123456';
8postgres=## SELECT rolname FROM pg_roles;

1#创建数据库
2createdb mydb
3dropdb mydb
4
5#可以直接使用交互式终端程序访问
6pgsql mydb

 1CREATE TABLE weather (
 2    city            varchar(80),
 3    temp_lo         int,           -- 最低温度
 4    temp_hi         int,           -- 最高温度
 5    prcp            real          -- 湿度
 6);
 7COMMENT ON TABLE public.weather IS '天气表';
 8COMMENT ON COLUMN public.weather.city IS '最低温度';
 9COMMENT ON COLUMN public.weather.temp_lo IS '最高温度';
10COMMENT ON COLUMN public.weather.temp_hi IS '湿度';
11DROP TABLE weather;
12
13
14-- 根据已存在表结果建新表
15create table employees_his as (select * from employees where 1=0);

 1INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
 2
 3-- 返回新增数据
 4insert into dept (dept_id, dept_name)
 5values ('000001', 'DeptOne') 
 6returning dept_id;   -- 返回新增数据的dept_id字段
 7
 8-- 通过通用表表达式，将新增数据数据插入备份表
 9with inserts as (
10 insert into employees values (206,'William','Gietz','WGIETZ','515.123.8181','2002-06-07','AC_ACCOUNT',8800.00,NULL,205,110)
11 returning *
12)
13insert into employees_his select * from inserts;  -- 过查询插入数据

 1UPDATE weather
 2    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
 3    WHERE date > '1994-11-28';
 4    
 5    
 6-- 将更新之前的数据插入备份表
 7-- 因为在一个语句中，所有的操作都在一个事务中，所以主查询中的 employees 是修改之前的状态  
 8with updates as (
 9 update employees set salary = salary + 500 where employee_id = 206
10 RETURNING *  -- 这里返回的是更新后的数据
11)
12insert into employees_his select * from employees where employee_id = 206;
13
14
15-- 将更新后的数据插入备份表
16with updates as (
17 update employees set salary = salary + 500 where employee_id = 206
18 RETURNING *  -- 这里返回的是更新后的数据
19)
20insert into employees_his select * from updates;

 1DELETE FROM weather WHERE city = 'Hayward';
 2
 3-- 连表删除，删除emp1中 emp存在的数据
 4delete from emp1
 5using emp WHERE emp1.employee_id = emp.employee_id;
 6
 7-- 利用通用表表达式备份删除的数据
 8with deles as (
 9	delete from employees where employee_id='206'
10	returning *   -- 返回被删除的所有数据
11)
12insert into employees_his select * from deles;  -- 过查询插入数据

 1-- 查询pgsql版本
 2select version() as "数据库版本"
 3
 4
 5-- 基本查询 =  !=  <>  <  >  in  not-in  between
 6select first_name,last_name from employees where employee_id = 100;
 7
 8
 9-- 模糊查询 %任意多个字符  _任意一个字符
10select first_name,last_name from employees where first_name like 'Sa' || '%';
11
12
13-- 模糊查询 正则 ~ 大小写相关   ~* 大小写无关
14select first_name,last_name from employees where first_name ~ '^S.*n$';
15
16
17-- 多个查询条件  and  or  not   优先级:not>and>or
18select first_name,last_name from employees where employee_id >100 and first_name='Neena';
19
20
21-- 排序，默认为acs; asc 升序   desc 降序
22select first_name,last_name from employees order by first_name asc,last_name asc;
23
24
25-- 限制limit  
26-- 查询前5条记录
27select first_name,last_name from employees order by first_name limit 5;
28select first_name,last_name from employees order by first_name fetch first 5 rows only;
29-- 查询第11到15的记录
30select first_name,last_name from employees order by first_name limit 5 offset 10;
31select first_name,last_name from employees order by first_name offset 10 rows  fetch first 5 rows only;
32
33-- 分组聚合  count()  avg() sum() string_agg(field,'',order by)等
34select manager_id,avg(salary)  from employees group by manager_id having avg(salary)>9000;
35-- 分组多个字段时，汇总；  小计、合计、总计
36-- cube = grouping sets ((job_id,"year"),"year",job_id)
37-- rollup = grouping sets ((job_id,"year"),"year")
38-- grouping(job_id) 可以查看哪些数据是基于job_id汇总的
39-- grouping sets 自定义分组集
40select job_id ,extract(year from hire_date) as "year",count(1),grouping(job_id) from employees group by grouping sets ((job_id,"year"),"year",job_id);
41
42
43-- 内连接
44select e.first_name ,d.department_name  from employees e join departments d ON e.department_id=d.department_id;
45
46
47-- 外连接 left join 、  right join 、  full join
48select e.first_name ,d.department_name  from employees e left join departments d ON e.department_id=d.department_id;
49
50
51-- 交叉连接 会构造一张很大的表  cross join
52select concat(t1,'*',t2)  from generate_series(1,9) t1 ,generate_series(1,9) t2;
53
54
55-- exists/not exists 将每行数据带入exists 后查询中匹配，能查到数据则返回
56select * from employees e where exists (select 1 from departments where department_id=e.employee_id)
57
58
59-- 当两个结果集很大时，all效率总是高于distinct
60-- 并集  union all 不去重、union distinct 去重，默认
61select * from (values (1),(2),(3)) as t1 union select * from (values (1)) t2;
62
63-- 交集  intersect all 不去重、intersect distinct 去重
64select * from (values (1),(2),(3)) as t1 intersect select * from (values (1)) t2;
65
66-- 差集  except all 不去重 、 except distinct 去重
67select * from (values (1),(2),(3)) as t1 except select * from (values (1)) t2;
68
69
70-- 通用表表达式 with temp as ( select ...)   select * from temp 
71with temp as (select 1) select * from temp;
72
73-- 递归通用表表达式 需要关键词recursive；组织架构图展示
74with recursive tb as (
75	select employee_id  as empid,concat(first_name,last_name) as name,concat(first_name,last_name) as path from employees where manager_id is null    -- 初始条件
76	union all
77	select employee_id  as empid,concat(first_name,last_name) as name,concat(tb.path,'/',first_name,last_name) from employees join tb on manager_id=tb.empid    -- 递归条件
78) select * from tb

 1-- case 等值条件 如果不写else，未匹配到case的会返回null值
 2select
 3	first_name,last_name ,
 4	case
 5		department_id 
 6	when 90 then '管理'
 7		when 60 then '开发'
 8		else '其他'
 9	end as "部门"
10from
11	employees e
12
13-- case 逻辑条件
14select
15	first_name,
16	case 
17		when salary < 5000 then '低收入'
18		when salary between 5000 and 10000 then '中收入'
19		else '高收入'
20	end
21	
22from
23	employees e
24	
25-- case 行转列；count会忽略null值，利用case进行转列
26select
27	count(case department_id when 10 then 1 end) dept10_count,
28	count(case department_id when 20 then 1 end) dept10_count,
29	count(case department_id when 30 then 1 end) dept10_count
30from
31	employees e
32	
33	
34-- nullif(a,b)  如果a=b,返回null，1/null 值就为null
35select 1/nullif(0,0)
36
37-- coalesce(a,b,..)  返回第一个不为空的值
38select coalesce(null,'as')

 1-- 绝对值
 2select abs(-100);
 3
 4-- 取整 ceil向上找最近的整数、floor向下找最近的整数、round向零找最近的整数
 5select ceil(2.3);
 6
 7-- 乘方和开方 power(a,b)a的b次方、sqrt(a)a的平方根、cbrt(a)a的立方根
 8select sqrt(4);
 9
10-- 返回参数正负数，可能返回值1、0、-1
11select sign(-5) 
12
13-- 随机数 返回一个0-1之间的双精度随机数
14select random();

 1-- 字符串拼接
 2select 'a' || 'b' || 'c';
 3select concat('a','b','c');
 4select concat_ws('_','a','b','c');  -- 指定 _ 分隔符连接
 5
 6-- 获取字符串长度  length 按字符计算、bit_length 按比特计算、octet_length 按字节数计算
 7select length('abc');
 8
 9-- 大小写转换 upper 转换大写、lower 转换小写、initcap 首字母大写
10select upper('abc');
11
12-- 字串查找与替换
13select substring('abcd',1,2); -- 从位置1开始，查找2个 ab
14select left('abcd',2);  -- 从左边开始查找2个   ab
15select right('abcd',2);  -- 从右边开始第2个到最后2个  cd
16select replace('abcd','a','d');  -- 替换，dbcd
17select translate('abcd','ac','1');  -- 对应替换  a替换为1，c直接删除  1bd
18
19-- 截断
20select trim(' abcd ');  -- 去除前后空串
21select trim(both 'as' from 'abcdas' );  -- 两端截断包含 'a'或's'  bcd
22
23-- 字符串格式化
24select format('hello %s','world');  -- %s占位符  hello world
25
26-- 返回md5值
27select md5('asd');
28
29-- 反转字符串
30select reverse('abcd');   -- dcba

1-- 获取当前日期、时间、时间戳
2select current_date,current_time,current_timestamp;
3
4-- 当前时间加上一年 interval为时间段修饰词
5select current_date + interval '1 year';
6
7-- 获取时间戳的年份
8select date_part('year',current_timestamp);

 1-- 将'123' 转换为整型 123
 2select cast('123' as integer);
 3
 4-- 将 '2022-06-12' 转换为日期
 5select cast('2022-06-12' as date);
 6
 7-- 转换为日期
 8select to_date('2022/06/25','YYYY/MM/DD');
 9
10-- 转化为时间
11select to_timestamp('2022/06/25','YYYY/MM/DD');
12
13-- 转化为字符串
14select to_char(current_date,'YYYY-MM-DD');

1create view view_name as query;
2
3#实例
4create view myview as
5    select city, temp_lo, temp_hi, prcp
6        from weather
7        where date>'2022-01-12';

1#修改视图定义中的查询
2#PostgreSQL只支持追加视图定义中的字段，不支持减少字段或者修改字段的名称或顺序
3create or replace view view_name  as  query;
4
5#PostgreSQL 提供了 ALTER VIEW 语句修改视图的属性
6#修改视图名称
7alter view empdetailsview rename to empinfovie;

1#删除视图
2#if exists可以避免删除一个不存在的视图时产生错误
3#cascade 表示级联删除依赖于该视图的对象
4#restrict 表示如果存在依赖对象则提示错误信息，这是默认值
5drop view [ if exists ] name [ cascade | restrict ];
6
7#实例
8drop view myview;

 1CREATE TABLE cities (
 2        city     varchar(80) primary key,
 3        location point
 4);
 5
 6CREATE TABLE weather (
 7        city      varchar(80) references cities(city),
 8        temp_lo   int,
 9        temp_hi   int,
10        prcp      real,
11        date      date
12);

1begin;
2update cities set location = '(24,24)';
3savepoint my_savepoint;    -- 回滚结点
4update cities set location = '(48,48)';
5rollback to my_savepoint;  -- 会使回滚结点之后的操作回滚
6update weather set prcp = 14;
7commit;

1-- 查看pgsql默认事务隔离级别
2show transaction_isolation   
3
4
5-- 更改事务隔离级别
6begin;
7SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ 
8COMMITTED | READ UNCOMMITTED };

1window_function ( expression, ... ) OVER (
2 PARTITION BY ...
3 ORDER BY ...
4 frame_clause
5)
6-- window_function 是窗口函数的名称
7-- expression 是函数参数，有些函数不需要参数
8-- OVER 子句包含三个选项：分区(PARTITION BY)、排序(ORDER BY)以及窗口大小(frame_clause)

1{ ROWS | RANGE } frame_start
2{ ROWS | RANGE } BETWEEN frame_start AND frame_end

 1-- 查询每个工作每年总共人数
 2select
 3	job_id,
 4	extract(year from hire_date),
 5	count(1) over (
 6	 partition by job_id  -- 根据工作分组
 7	 order by extract(year from hire_date)  -- 根据年份排序
 8	 rows between unbounded preceding and current row   -- 定义计算窗口，第一行到当前行
 9	)
10from
11	employees
12	
13	
14-- 排名窗口函数  查询员工在各自部门薪资排行
15select department_id,concat(first_name,last_name) as name,
16	row_number() over w1  as "row_number",  -- 1-10  每行分配序号，连续
17	rank() over w1 as "rank",  -- 1-8 8 10	相同的会跳跃
18	dense_rank() over w1 as "dense_rank",  -- 1-8 8 9 10  相同的数据，后续依旧连续
19	percent_rank() over w1 as "percent_rank"  -- 以百分比的形式显示每行数据在其分区中的名次
20from employees
21window w1 as (partition by department_id order by salary desc);
22
23
24-- 取值窗口函数
25select department_id,concat(first_name,last_name) as name,
26	first_value(salary) over w1 "first_value",   -- 返回窗口内第一行的数据
27	last_value(salary) over w1 as "last_value",  -- 返回窗口内最后一行的数据
28	nth_value(salary,2) over w1 as "nth_value",  -- 返回窗口内第 N 行的数据
29	lag(salary) over w1 as "lag",  -- 返回分区中当前行之前的第 N 行的数据
30	lead(salary) over w1 as "lead"  -- 返回分区中当前行之后第 N 行的数据
31from employees
32window w1 as (partition by department_id order by salary desc); -- 这里定义的窗口可以在前面语句中直接使用 
33
34-- 使用lag函数实现环比分析，各个部门根据年排序，取得本期汇总薪水和上期汇总薪水
35select
36	department_id,
37	"year" ,
38	salary,
39	lag(salary, 1) over (partition by department_id order by "year") as "presal"
40from
41	(selectdepartment_id,extract(year from hire_date) as "year",sum(salary) as "salary" 
42	    from employees group by 1, 2) as t

 1-- 创建test表
 2CREATE TABLE test ( id integer, name text);
 3-- 模拟生成10000000条数据
 4INSERT INTO test SELECT v,'val:'||v FROM generate_series(1, 10000000)  v;
 5
 6-- 查看执行计划，需要花费5秒
 7explain analyze SELECT name FROM test WHERE id = 10000;
 8/*
 9 * Gather (cost=1000 rows=1 width=11) (actual time=5410 rows=1 loops=1)
10 * ....
11 * Execution Time: 1981.174 ms
12 */
13
14-- 在id字段上创建索引
15create index test_id_index on test (id);
16
17-- 再次查看执行计划，走了索引且执行之间仅为 1.4 ms
18explain analyze SELECT name FROM test WHERE id = 10000;
19/*
20 * Index Scan using test_id_index on test (cost=0.56 rows=1 width=12) (actual time=1.0 rows=1 loops=1)
21 * Execution Time: 1.405 ms
22 */

1select * from pg_indexes where tablename = 'test';

 1-- 重命名索引
 2alter index index_name rename to new_name;
 3
 4-- 移动索引到其他表空间
 5alter index index_name set tablespace tablespace_name;
 6
 7-- 重建索引数据，支持不同级别的索引重建
 8reindex [ ( verbose ) ] { index | table | schema | database | system } index_name;
 9
10-- 实例 重建id_unique索引
11reindex index id_unique

1-- CASCADE 表示级联删除其他依赖该索引的对象
2-- RESTRICT 表示如果存在依赖于该索引的对象，将会拒绝删除操作。默认为 RESTRICT
3drop index index_name [ cascade | restrict ];
4
5-- 实例
6drop index test_id_index;

1-- 所有的语句都使用分号（;）结束，END 之后的分号表示代码块结束
2
3[ <<label>> ]   -- 可选的代码块标签，可用于退出语句或限定变量名称
4[ DECLARE  -- 可选的声明部分，用于定义变量
5    declarations ]
6BEGIN	-- BEGIN 和 END 之间是代码主体，也就是主要的功能代码
7    statements
8END [ label ];

1do $$      -- $$ 替换'
2declare
3 name text;  -- 定义了变量name，类型为text
4begin 
5 name := 'pl/pgsql';   -- 给变量赋值
6 raise notice 'hello %!', name;   -- 输出通知信息
7end $$;

 1do $$
 2<< outerblock >>
 3declare
 4    quantity integer := 30;
 5begin
 6    raise notice 'quantity here is %', quantity;  -- prints 30
 7    quantity := 50;
 8    --
 9    -- 创建一个子块
10    --
11    declare
12        quantity integer := 80;
13    begin
14        raise notice 'quantity here is %', quantity;  -- prints 80
15        raise notice 'outer quantity here is %', outerblock.quantity;  -- prints 50
16    end;
17
18    raise notice 'quantity here is %', quantity;  -- prints 50
19end;
20$$;

1-- variable_name 是变量的名称
2-- data_type 是变量的类型，可以是任何 SQL 数据类型
3-- 指定了 NOT NULL，必须使用后面的表达式为变量指定初始值
4-- DEFAULT 指定变量初始值
5variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

1user_id integer;
2quantity numeric(5) DEFAULT 0;
3url varchar := 'http://mysite.com';

1-- myrow 是一个行类型的变量，可以存储查询语句返回的数据行（数据行的结构要和 tablename相同）
2-- myfield 的数据类型取决于 tablename.columnname 字段的定义
3-- amount 和 quantity 的类型一致
4
5myrow tablename%ROWTYPE;
6myfield tablename.columnname%TYPE;
7amount quantity%TYPE;

1arow RECORD;

1-- newname 和 oldname 代表了相同的对象
2newname ALIAS FOR oldname;

1-- 定义常量PI
2do $$ 
3declare
4 PI constant numeric := 3.14159265;
5 radius numeric;
6begin 
7 radius := 1.0;
8 raise notice 'the area is %', PI * radius * radius;
9end $$;

1-- boolean-expression为真，执行Then后语句
2IF boolean-expression THEN
3 statements
4END IF;

 1do $$ 
 2declare 
 3	a integer default 65;
 4begin
 5	if a>80 then
 6		raise notice '优秀';
 7	elsif a>60 then 
 8		raise notice '良好';
 9	else 
10		raise notice '不及格';
11	end if;
12end; $$
13-- 输出：良好

1case search-expression
2 when expression [, expression [ ... ]] then
3 statements
4 [ when expression [, expression [ ... ]] then
5 statements  ... ]
6 [ else statements ]
7end case;

 1do $$ 
 2declare
 3	sorce integer := 65;
 4begin
 5	case
 6	when sorce >= 80 then
 7		raise notice '优秀';
 8	when (sorce >=60 and sorce < 80) then 
 9		raise notice '良好';
10	else 
11		raise notice '不及格';
12	end case;
13end; $$

1exit [ label ] [ when boolean-expression ];

1continue [ label ] [ when boolean-expression ];

1-- 一般需要使用 EXIT 或者 RETURN 语句退出循环
2-- label 可以用于 EXIT 或者 CONTINUE 语句退出或者跳到执行的嵌套循环中
3[ <<label>> ]
4loop
5 statements
6end loop [ label ];

 1do $$ 
 2declare 
 3	i integer:=0;
 4begin
 5	loop
 6		raise notice 'Loop: %', i;
 7		exit when i = 5;   -- 当 i=5 时退出循环
 8		i := i + 1;
 9	end loop;
10end; $$

1[ <<label>> ]
2WHILE boolean-expression LOOP
3 statements
4END LOOP [ label ];

1do $$ 
2declare 
3	i integer:=0;
4begin
5	while i <= 5 loop
6		raise notice 'Loop: %', i;
7		i := i + 1;
8	end loop;
9end; $$

1[ <<label>> ]
2for name in [ reverse ] expression .. expression [ by expression ] loop
3 statements
4end loop [ label ];

1-- 变量 i 不需要提前定义
2-- 输出结果为 0 2 4
3do $$ 
4begin
5	for i in 0..5 by 2 loop   -- 步长为2
6		raise notice 'Loop: %', i;
7	end loop;
8end; $$

1[ <<label>> ]
2for target in query loop
3 statements
4end loop [ label ];

1-- 注意这里的dept行变量需要提前定义
2do $$ 
3declare
4	dept record;
5begin
6	for dept in (select * from sys_dept) loop
7		raise notice 'Loop: %,%', dept.dept_id, dept.dept_name;
8	end loop;
9end; $$

1-- scroll，那么游标可以反向滚动
2-- no scroll，那么反向取的动作会被拒绝
3-- 如果二者都没有被指定，那么能否进行反向取就取决于查询
4-- 如果指定了arguments， 那么它是一个逗号分隔的name datatype对的列表， 它们定义在给定查询中要被参数值替换的名称
5name [ [ no ] scroll ] cursor [ ( arguments ) ] for query;

 1do $$ 
 2declare
 3	rec_code record;
 4	cur_code  cursor for select * from sys_code limit 200;
 5begin
 6	-- 打开游标
 7	open cur_code;
 8	loop
 9		-- 获取游标中下一行的记录
10		fetch cur_code into rec_code;
11		-- 没找到记录退出循环
12		exit when  not found;
13		raise notice 'Loop : % %' ,rec_code.code_label,rec_code.code_value;
14	end loop;
15	
16	-- 关闭游标
17	close cur_code;
18end;$$

1-- 不同的 level 代表了错误的不同严重级别，默认level为exception
2--    包括：debug、log、notice、info、warning、exception
3-- format 是一个用于提供信息内容的字符串
4--    可以使用%占位符，接收参数，%% 表示输出 % 本身
5raise level format;

 1do $$ 
 2begin 
 3 raise debug 'This is a debug text.';
 4 raise info 'This is an information.';
 5 raise log 'This is a log.';
 6 raise warning 'This is a warning at %', now();
 7 raise notice 'This is a notice %%';
 8end $$;
 9-- 并非所有的消息都会打印到客户端和服务器日志中
10-- 可以通过配置参数 client_min_messages 和 log_min_messages 进行设置

 1[ <<label>> ]
 2[ declare
 3 declarations ]
 4begin
 5 statements
 6exception
 7 when condition [ or condition ... ] then handler_statements
 8 [ when condition [ or condition ... ] then handler_statements 
 9  ... ]
10end;

 1do $$ 
 2declare 
 3 i integer := 1;
 4begin 
 5 i := i / 0;
 6exception
 7 when division_by_zero then
 8 raise notice '除零错误！';
 9 when others then
10 raise notice '其他错误！';
11end $$;

 1-- create 表示创建函数
 2-- or replace 表示替换函数定义
 3-- name 是函数名；括号内是参数，多个参数使用逗号分隔
 4-- argmode 可以是 in（输入）、out（输出）、inout（输入输出）或者 variadic（数量可变），默认为 in
 5-- argname 是参数名称；argtype 是参数的类型;default_expr是参数的默认值
 6-- rettype 是返回数据的类型
 7-- as 后面是函数的定义，和上文中的匿名块相同
 8-- language 指定函数实现的语言，也可以是其他过程语言
 9
10create [ or replace ] function
11 name ( [ [ argmode ] [ argname ] argtype [ { default | = } default_expr ] 
12[, ...] ] )
13 returns rettype
14as $$
15declare
16 declarations
17begin
18 statements;
19 ...
20end; $$
21language plpgsql;

 1-- 新建函数
 2create or replace function get_count(creator_id integer)
 3returns integer 
 4as $$
 5declare
 6 ln_count integer;
 7begin
 8 select count(*) into ln_count
 9 from sys_code
10where creator = cast(creator_id as varchar);
11 return ln_count;
12end; $$
13language plpgsql
14
15-- 调用函数
16select get_count(1)
17
18-- 删除函数
19drop function get_count(integer);

 1create [ or replace ] procedure
 2 name ( [ [ argmode ] [ argname ] argtype [ { default | = } default_expr ] 
 3[, ...] ] )
 4as $$
 5declare
 6 declarations
 7begin
 8 statements;
 9 ...
10end; $$
11language plpgsql;

 1-- 新建存储过程
 2create or replace procedure update_emp(
 3 p_empid in integer,
 4 p_salary in numeric,
 5 p_phone in varchar)
 6as $$
 7begin
 8 update employees 
 9 set salary = p_salary,
10 phone_number = p_phone
11 where employee_id = p_empid;
12end; $$
13language plpgsql;
14
15-- 调用存储过程
16call update_emp(100, 25000, '515.123.4560')
17
18-- 删除存储过程
19drop procedure update_emp(integer,numeric,varchar);

 1-- 新建测试表
 2create table test(a int);
 3
 4-- 创建存储过程
 5create procedure transaction_test() as $$
 6begin
 7 for i in 0..9 loop
 8 insert into test (a) values (i);
 9   if i % 2 = 0 then
10     commit;
11   else
12     rollback;
13   end if;
14 end loop;
15end
16$$
17language plpgsql;
18 
19-- 调用存储过程
20call transaction_test();
21
22-- 查看结果，只有偶数被存入了表中
23select * from test;
24 
25-- 删除测试表
26drop table test

1-- 暂时只能修改触发器名称
2alter trigger name on table_name rename to new_name;

1alter table table_name {enable | disable} trigger {trigger_name | all | user};

1-- if exists 存在才删除，避免报错
2-- cascade 表示级联删除依赖于该触发器的对象
3-- restrict 表示如果存在依赖于该触发器的对象返回错误，默认为restrict
4drop trigger [if exists] trigger_name on table_name [restrict | cascade];

 1-- 创建两张 test 表
 2create table test_a(a varchar(20),b varchar(20));
 3create table test_b(a varchar(20),b varchar(20));
 4
 5-- 创建触发器函数，实现插入时将数据同样插入test_b表
 6create or replace function track_test() 
 7returns trigger as $$
 8begin
 9	if tg_op = 'INSERT'  then 
10		insert into test_b values ( new.a,new.b);
11	end if;
12	return new;
13end; $$
14language plpgsql;
15
16-- 创建触发器将触发器函数和表相关联，实现触发器函数track_test和表test_a关联，触发时机为增删改之前
17create trigger trg_track_test
18 before insert or update or delete
19 on test_a
20 for each row
21 execute function track_test();
22 
23-- 测试，向test_a表插入数据，然后查看test_b表
24insert into test_a values ('a','b');
25select * from test_a;
26select * from test_b;
27
28-- 删除触发器、触发器函数
29drop trigger if exists trg_track_test on test_a;
30drop function if exists track_test;
31
32-- 删除测试表
33drop table test_a;
34drop table test_b;