PostgreSQL存储过程-pgAdmin
文章目录
- pgAdmin
- 基本结构
- 最简单
- 创建存储过程结构
- 调用存储过程
- 查看删除存储过程
- 更优雅一些的结构
- 变量定义
- 完整结构
- 输出信息
- 日志级别
- 日志配置
- 简单示例
- 嵌套块
- if else
- case
- LOOP
- while
- foreach(迭代数组)
- for
- 普通for循环
- 循环select结果
- 游标
- 事务
- 测试表与数据
pgAdmin
如果，不知道怎么安装PostgreSQL的可以参考：PostgreSQL安装用户、角色与权限管理在这篇文章中我们介绍了dbeaver。
这一篇，我们使用PostgreSQL自己带的pgAdmin。
设置连接：
查询工具
基本结构
最简单
我们先从最简单的开始：
do 'BEGIN raise notice ''啊哈''; END'
是不是非常简单，不知道raise是啥东西，不要紧，后面会介绍，现在就把它当做log日志信息好了。
在稍微格式化一下：
do
'BEGIN
raise notice ''啊哈'';
END'
do表示执行，单引号内的就是存储过程，用do表示只执行，不创建。
begin和end之间就是就是存储过程的逻辑体，raise语句中用双引号是因为，存储过程本身是用单引号，所以存储过程中的单引号需要转义。
反斜杠()也需要转义:\
创建存储过程结构
除了直接执行，我们还可以使用CREATE PROCEDURE来创建存储过程，方便多次调用。
-- 不要用驼峰命名法，因为不区分大小写
CREATE PROCEDURE first_procedure()
AS 'BEGIN raise notice ''啊哈''; END'
LANGUAGE plpgsql;
调用存储过程
call first_procedure();
查看删除存储过程
也可以通过命令来删除：
drop procedure first_procedure
\df是describe function，PostgreSQL的函数和存储过程基本是一个东西，有差别不大。
想要了解具体区别，可以参考官方文档，关键字：36.4. User-Defined Procedures
更优雅一些的结构
因为存储过程中，我们多半会大量用到单引号，为了避免转义，我们可以用$$之类的界定符来包裹存储过程，这样存储过程中的单引号就不用转义了。
do $$
BEGIN
raise notice '啊哈';
END
$$
看是不是效果一样。
注意一下，如果多个存储过程一起，标识结束的界定符的$$后面的逗号不能省略。
不一定是$ 才行，只要不是关键字便于区分都可以，例如 才行，只要不是关键字便于区分都可以，例如 才行，只要不是关键字便于区分都可以，例如PROC 、 、 、BODY ，甚至 ，甚至 ，甚至我要开始装了$都可以。
具体可以参考官方文档，关键字：Dollar-Quoted String Constants。
变量定义
declare是用来声明变量，必须先声明才能使用。
do $$
DECLARE
name text:='娘子';
BEGIN
raise notice '啊哈,%',name;
END
$$
更多的变量定义细节：
- 可以使用table_name%ROWTYPE来定义和表相同结构的行
- 可以使用table_name.column_name%TYPE来定义和指定表指定列有相同数据类型，这样表的数据类型变了，存储过程可以不用变
- 可以通过DEFAULT关键字设置默认值
- 可以使用CONSTANT关键字来定义常量，不允许修改
- 可以使用RECORD关键字来定义记录，主要用于查询保存查询数据和ROWTYPE不同在于，它根据select动态列。
DO $$
DECLARE
name text:='娘子';
user_id integer;
myrow user%ROWTYPE;
rid user.id%TYPE:=123;
arow RECORD;
quantity integer DEFAULT 32;
url varchar := 'http://www.meeet.vip';
transaction_time CONSTANT timestamp with time zone := now();
BEGIN
raise notice '啊哈,%',name;
raise notice 'user_id:%',user_id;
raise notice 'rid:%',rid;
raise notice 'url:%',url;
raise notice 'transaction_time:%',transaction_time;
END
$$
完整结构
下面是PostgreSQL存储过程相对完整的结构，基本常用的就是这些。
CREATE OR REPLACE PROCEDURE procedure_name (
[IN | OUT | INOUT] parameter_name data_type,
...
)
AS $$
[<<block_name>>]
DECLARE
-- 定义变量
...
BEGIN
-- 存储过程逻辑
...
END；
$$ LANGUAGE plpgsql
如果想要知道更多细节，可以参考官方文档，关键字:
CREATE PROCEDURE
CREATE PROCEDURE — define a new procedure
输出信息
日志级别
输出信息有6个级别，如果不指定默认是EXCEPTION。
- DEBUG
- LOG
- INFO
- NOTICE
- WARNING
- EXCEPTION
do $$
begin
raise debug 'debug消息';
raise log 'log消息';
raise info 'info信息';
raise notice 'notice消息';
raise warning 'warning消息';
raise exception 'exception消息';
end
$$
除了基本信息，还可以输出一些附加信息：
- MESSAGE:没有指定的时候，默认就是message信息
- DETAIL
- HINT
- ERRCODE
- COLUMN
- CONSTRAINT
- DATATYPE
- TABLE
- SCHEMA
RAISE notice '哈哈' USING HINT = '这里是HINT';
RAISE division_by_zero;
RAISE SQLSTATE '22012';
RAISE 'Duplicate user ID: %', 123 USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', 123 USING ERRCODE = '23505';
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
错误码 ERRCODE
如果想要更详细的输出信息，可以参考：错误与消息
日志配置
debug和log级别的日志默认是输出到日志文件的，默认也没有开启输出到日志文件，需要配置。
配置文件为PGDATA目录下的postgresql.conf
# 开启日志
logging_collector = on
# 记录哪些SQL语句
log_statement = 'all'
# 日志文件目录，默认log，如果不是绝对路径，就在pgdata目录下
log_directory = 'logs'
# 日志文件名称格式
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# 输出的日志类型，stderr、csvlog、josnlog
log_destination = 'stderr'
# 最小日志级别
log_min_messages = debug5
# 客户端最小日志级别
client_min_messages = debug5
# 慢查询日志、记录执行时间超过指定时间的SQL语句-1表示不记录
log_min_duration_statement=60
# 日志文件的生存期，默认1天
log_rotation_age = 1d
# 日志文件最大大小
log_rotation_size = 10MB
# 日志文件已存在时追加
log_truncate_on_rotation = off
配置完成，重启服务，可以通过sql查看是否配置成功
SHOW log_statement;
SHOW log_directory;
SHOW log_filename;
日志文件控制输出的日志级别就更多了：
- DEBUG5
- DEBUG4
- DEBUG3
- DEBUG2
- DEBUG1
- INFO
- NOTICE
- WARNING
- ERROR
- LOG
- FATAL
- PANIC
日志配置可以参考官方文档：日志配置
简单示例
CREATE OR REPLACE PROCEDURE add_numbers(a INTEGER, b INTEGER, OUT result INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
result := a + b;
END;
$$;
DO
$$
DECLARE
v_result INTEGER;
BEGIN
CALL add_numbers(10, 20, v_result);
RAISE NOTICE '计算结果为: %', v_result;
END
$$;
嵌套块
PostgreSQL的逻辑部分可以嵌套：
DO $$
<<outer_block>>
DECLARE
name text;
BEGIN
name := 'outer_btock';
RAISE NOTICE 'outer_block打印name:%',name;
DECLARE
name text:= 'sub';
BEGIN
RAISE NOTICE '嵌套块打印name:%',name;
RAISE NOTICE '嵌套块打印outer_block.name:%',outer_block.name;
END;
RAISE NOTICE '这是外部直接访问:%',name;
END outer_block $$;
嵌套体内可以通过外部块的名字来访问其定义的变量。
if else
if else很简单，和shell脚本的语法非常像：
do $$
declare
a int4:=90;
BEGIN
IF a>=90 THEN
raise notice '优秀';
ELSIF a>=80 then
raise notice '良好';
ELSIF a>=60 then
raise notice '及格';
ELSE
raise notice '不及格';
END IF;
END
$$ LANGUAGE plpgsql
case
DO $$
declare
a int4=80;
BEGIN
case when a>=90 THEN
raise notice '优秀';
when a>=80 then
raise notice '良好';
when a>=60 then
raise notice '及格';
ELSE
raise notice'不及格';
END case;
END
$$ LANGUAGE PLPGSQL
LOOP
loop是无限循环，所以一定要配合exit使用。
do $$
declare i int4:=1;
begin
LOOP
raise notice '第%次循环',i;
i := i+1;
IF i>5 THEN
EXIT;
END IF;
END LOOP;
i=1;
LOOP
raise notice '第%次循环',i;
i:=i+1;
EXIT when i>5;
END LOOP;
end
$$
while
do $$
declare
sum integer default 0;
i int4:=0;
BEGIN
WHILE i<=100 LOOP
sum := sum+i;
i:=i+1;
END LOOP;
raise notice '计算结果为:%',sum;
END
$$
LANGUAGE plpgsql
foreach(迭代数组)
foreach主要用来遍历数组：
do $$
DECLARE
arr int[] := array[1,2,3,4,5,6,7,8,9,10];
s int4 := 0;
x int;
BEGIN
FOREACH x IN ARRAY arr
LOOP
s := s + x;
END LOOP;
raise notice '最终计算结果为:%',s;
END;
$$
LANGUAGE plpgsql
for
普通for循环
从初始值到结束值，每次默认步长1，循环。
REVERSE关键字的时候，使用的是减，所以，注意初始值也结束值的位置。
do $$
declare i int4;
begin
-- 从1递增到5，包含，默认步长1
FOR i IN 1..5 LOOP
raise notice 'i的值:%',i;
END LOOP;
-- reverse 表示用减法
FOR i IN REVERSE 5..1 LOOP
raise notice 'i的值:%',i;
END LOOP;
-- 可以使用by指定步长
FOR i IN REVERSE 10..1 BY 2 LOOP
raise notice 'i的值:%',i;
END LOOP;
end
$$
循环select结果
do $$
declare
rs record;
BEGIN
FOR rs IN select id,name from public.user LOOP
raise notice 'id: %,name: %',rs.id,quote_ident(rs.name);
END LOOP;
END
$$
LANGUAGE plpgsql
quote_ident()的作用是为字符串加上双引号
游标
DO $$
DECLARE
r_user RECORD;
c_user CURSOR(p_wy INTEGER) FOR
SELECT id, name, work_year ,birthday
FROM public.user
WHERE work_year > p_wy;
BEGIN
-- 打开游标，指定参数
OPEN c_user(5);
LOOP
-- 获取游标中的记录
FETCH c_user INTO r_user;
-- fetch没有拿到数据，说明结束，退出循环
EXIT WHEN NOT FOUND;
RAISE NOTICE 'id:%,name:% workYear:%,birthday:%' , r_user.id, r_user.name, r_user.work_year,r_user.birthday;
END LOOP;
-- 关闭游标
CLOSE c_user;
END $$;
事务
存储过程中的事务控制粒度更细，每一条都可以单独提交或者回滚
drop table if exists transaction_test;
create table transaction_test(id int);
CREATE E OR REPLACE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO public.transaction_test (id) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test();
select * from transaction_test;
测试表与数据
--
-- PostgreSQL database dump
--
-- Dumped from database version 17.0
-- Dumped by pg_dump version 17.0
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: user; Type: TABLE; Schema: public; Owner: root
--
CREATE TABLE public."user" (
id bigint NOT NULL,
name character varying(50),
work_year smallint,
birthday date
);
ALTER TABLE public."user" OWNER TO root;
--
-- Name: user_id_seq; Type: SEQUENCE; Schema: public; Owner: root
--
CREATE SEQUENCE public.user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.user_id_seq OWNER TO root;
--
-- Name: user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: root
--
ALTER SEQUENCE public.user_id_seq OWNED BY public."user".id;
--
-- Name: user id; Type: DEFAULT; Schema: public; Owner: root
--
ALTER TABLE ONLY public."user" ALTER COLUMN id SET DEFAULT nextval('public.user_id_seq'::regclass);
--
-- Data for Name: user; Type: TABLE DATA; Schema: public; Owner: root
--
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (21, 'Buda', 10, '1996-02-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (22, 'Blacky', 3, '1995-01-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (23, 'Leo', 3, '1994-12-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (24, 'Solomon', 7, '1996-02-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (25, 'Leo', 5, '1994-12-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (26, 'Kelly', 2, '1995-06-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (27, 'Ivy', 8, '1996-04-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (28, 'Anne', 1, '1994-11-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (29, 'Kylie', 8, '1996-09-10');
INSERT INTO public."user" (id, name, work_year, birthday) VALUES (30, 'Wilson', 1, '1996-09-10');
--
-- Name: user_id_seq; Type: SEQUENCE SET; Schema: public; Owner: root
--
SELECT pg_catalog.setval('public.user_id_seq', 30, true);
--
-- Name: user user_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--
ALTER TABLE ONLY public."user"
ADD CONSTRAINT user_pkey PRIMARY KEY (id);
--
-- PostgreSQL database dump complete
--