网站开发项目工作分解,学编程要什么学历,专业提供网站建设服务的企业,宁波外客网络科技有限公司【PostgreSQL】在DBeaver中实现序列、函数、触发器、视图设计 基本配置一、序列1.1、序列使用1.1.1、设置字段为主键#xff0c;数据类型默认整型1.1.2、自定义序列#xff0c;数据类型自定义 1.2、序列延申1.2.1、理论1.2.2、测试1.2.3、小结 二、函数2.1、SQL直接创建2.1.1… 【PostgreSQL】在DBeaver中实现序列、函数、触发器、视图设计 基本配置一、序列1.1、序列使用1.1.1、设置字段为主键数据类型默认整型1.1.2、自定义序列数据类型自定义 1.2、序列延申1.2.1、理论1.2.2、测试1.2.3、小结 二、函数2.1、SQL直接创建2.1.1、理论2.1.2、测试 2.2、借用DBeaver创建 三、视图3.1、SQL语句3.2、示例 四、触发器4.1、SQL语句4.1.1、触发器函数4.1.2、函数与表关联 4.2、示例 基本配置 数据库管理工具DBeaver23.2.3 PostgreSQL 14.6 测试数据库在博文中已经资源绑定分享 一、序列
1.1、序列使用
在MySQL数据库中实现主键自增只需要设置字段为主键即可但在Pg数据库中却有所不同。 实现的途径主要有2种
1.1.1、设置字段为主键数据类型默认整型
此时字段默认为serial4即自增 4 字节整数范围1 到 2147483647。以表employees_history为例主键字段id默认值为nextval(‘employees_history_id_seq’::regclass)同时会自动添加序列employees_history_id_seq。 函数 nextval(regclass) 返回类型bigint描述如下 递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval每个进程也会安全地收到一个唯一的序列值。 1.1.2、自定义序列数据类型自定义
当主键数据类型不是整型时使用序列自增主要采用该种方法。 首先用SQL语句创建testseq_d_seq序列
CREATE SEQUENCE public.testseq_d_seqINCREMENT BY 1MINVALUE 1MAXVALUE 2147483647START 1CACHE 1NO CYCLE;为方便起见也可在DBeaver数据库的序列中右键新建序列在完成序列命名后完成序列的创建。
其次根据需要将主键字段设置成所需数据类型并与创建的序列绑定设置默认值。
nextval(testseq_d_seq::regclass)1.2、序列延申
在内容的存储过程有时候会遇到预处理数据后再存储的情况本节以实现‘A-%’格式存储为例即所有存储的主键字段必须以A-开头展开介绍。
1.2.1、理论
在展开介绍前首先查看了一些资料以PostgreSQL 字符串函数汇总为主该大神的博文中清晰的罗列了基本的字符串函数因此本文就不再进行重复论述。根据需求我们从中选取合适的函数开展数据预处理设置主键默认值如下
(A-::text || nextval(testseq_d_seq::regclass))1.2.2、测试
通过输入SQL插入记录进行测试最终输出的记录主键字段为‘A-2’实现需求。
INSERT INTO public.testseq
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES(3091122, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)1.2.3、小结
在本小节中我们以一个简单的示例介绍了数据库的数据预处理存储后续大家也可以根据需要在数据库中自定义的使用函数进行需求实现提高开发效率。当然由于提供函数有限针对较为复杂的预处理依旧还是采用后端处理后再存储到数据库中。
二、函数
在PostgreSQL 数据库中自定义一些函数可以有效帮助我们提高开发效率。本章主要介绍函数创建的2种方式
2.1、SQL直接创建
2.1.1、理论
采用SQL创建属于万能的方式基本的语句结构如下
CREATE FUNCTION function_name(parameter1 datatype1, parameter2 datatype2, OUT output_parameter datatype)
RETURNS return_type AS $$
-- 函数体
$$ LANGUAGE language_name;function_name函数名parameter传参datatype参数类型output_parameter输出参数return_type函数返回类型language_name编程语言
为方便函数内容的更新同时也避免由于相同函数命名存在导致执行报错的发生增加OR REPLACE优化后的语句结构如下
CREATE OR REPLACE FUNCTION function_name(parameter1 datatype1, parameter2 datatype2, OUT output_parameter datatype)
RETURNS return_type AS $$
-- 函数体
$$ LANGUAGE language_name;2.1.2、测试
用SQL创建函数需求如下
可传不定长字符串、整型可更新时间可选传参数。
编写的SQL语句如下所示其中character varying为不定长字符串数据类型
CREATE OR REPLACE FUNCTION public.test_han(_employee_id integer, _last_name character varying, _salary integer DEFAULT 10, _job_id character varying DEFAULT NULL, OUT _id character varying)RETURNS character varying AS $$BEGININSERT INTO testseq (employee_id,last_name,hire_date,job_id,salary)VALUES(_employee_id,_last_name,now( ),_job_id,_salary) RETURNING id INTO _id;
END;
$$ LANGUAGE plpgsql在DBeaver运行后最终函数显示的源如下所示
CREATE OR REPLACE FUNCTION public.test_han(_employee_id integer, _last_name character varying, _salary integer DEFAULT 10, _job_id character varying DEFAULT NULL::character varying, OUT _id character varying)RETURNS character varyingLANGUAGE plpgsql
AS $function$BEGININSERT INTO testseq (employee_id,last_name,hire_date,job_id,salary)VALUES(_employee_id,_last_name,now( ),_job_id,_salary) RETURNING id INTO _id;
END;
$function$
;两个SQL语句都可以正常运行。用语句select test_han(12,test)进行函数调用最终完成记录的插入同时返回参数如下
2.2、借用DBeaver创建 该方法本质依旧是执行SQL语句只不过不需要进行函数创建的SQL语句编写更关注于函数体的业务需求实现。 首先将DBeaver切到public-存储过程然后右键 新建 存储过程 填写函数名称、语言类型、返回参数类型完成函数架构的搭建。 然后在架构中编写函数体与传参。最后快捷键CtrlS保存点击执行完成函数创建。
三、视图
视图是一张假表只不过是通过相关的名称存储在数据库中的一个 PostgreSQL语句。而且视图是只读的因此可能无法在视图上执行DELETE、INSERT 或UPDATE语句。但是可以在视图上创建一个触发器当尝试 DELETE、INSERT 或 UPDATE 视图时触发需要做的动作在触发器内容中定义。
3.1、SQL语句
创建视图的SQL语句结构如下所示注意以 结尾
CREATE VIEW view_name AS
--SELECT语句view_name视图名
同样的为了方便更新视图避免出现存在同命名导致SQL执行失败情况的出现采用OR REPLACE优化SQL语句优化后的结构如下
CREATE OR REPLACE VIEW view_name AS
--SELECT语句3.2、示例
获取表employees中数据创建视图SQL语句如下
CREATE OR REPLACE VIEW asd as select * from employees e 四、触发器
PostgreSQL支持两种级别的触发方式行级row-level触发器和 语句级statement-level触发器区别在于触发的时机和触发次数。例如对于一个影响 20 行数据的 UPDATE 语句行级触发器将会触发器 20 次而语句级触发器只会触发 1 次。
4.1、SQL语句
创建触发器一共2步首先用CREATE [OR REPLACE] FUNCTION创建触发器函数其次用create trigger将触发器函数与表关联起来从而完成创建。
4.1.1、触发器函数
基本的触发器函数SQL结构如下所示
CREATE [ OR REPLACE ] FUNCTION trigger_function()
RETURNS trigger AS $$
-- 函数体
$$ LANGUAGE language_name;触发器函数与普通函数创建类似区别在于触发器函数没有传参而且返回类型是trigger 。同时在触发器函数的内部系统自动封装了许多特殊的变量这个在大神postgresql-触发器的博文中有清晰的罗列这边就不进行重复的讲述主要记录一些常用的
NEW 类型为 RECORD代表了行级触发器 INSERT、UPDATE 操作之后的新数据行。对于 DELETE 操作或者语句级触发器而言该变量为 null。OLD类型为 RECORD代表了行级触发器 UPDATE、DELETE 操作之前的旧数据行。对于 INSERT 操作或者语句级触发器而言该变量为 null。TG_OP触发的操作INSERT、UPDATE、DELETE 或者 TRUNCATE。
4.1.2、函数与表关联
基本的关联SQL语句结构如下
-- 使用 CREATE TRIGGER 语句创建一个触发器语法如下
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}ON table_name[FOR [EACH] {ROW | STATEMENT}][WHEN ( condition ) ]EXECUTE FUNCTION trigger_function;event 可以是在所提到的表table_name上的INSERT、UPDATE、DELETE、TRUNCATE操作而且UPDATE 支持在表的一个或多个指定列上操作UPDATE OF col1, clo2。触发器可以在事件之前BEFORE或者之后AFTER 触发INSTEAD OF 只能用于替代视图上的 INSERT、UPDATE 或者 DELETE 操作。FOR EACH ROW 表示行级触发器FOR EACH STATEMENT 表示语句级触发器。WHEN 用于指定一个额外的触发条件满足条件才会真正支持触发器函数。 DROP 可以删除整个表包括表结构和数据速度最快 TRUNCATE 可以快速地删除表中的所有数据但不删除表结构速度中等 DELETE 可以删除表中的数据不包括表结构速度最慢。 4.2、示例 需求实现当对表employees进行insert、delete、update时进行历史记录保存保存到employees_history。 首先创建触发器函数SQL语句如下
CREATE OR REPLACE FUNCTION public.track_emp_change()RETURNS triggerLANGUAGE plpgsql
AS $function$
begin -- tg_op 触发的操作 if tg_op INSERT theninsert into public.employees_history(employee_id, action_type, change_dt)values(new.employee_id,INSERT,now());elsif tg_op UPDATE theninsert into public.employees_history(employee_id, action_type, change_dt)values(old.employee_id, UPDATE,now());elsif tg_op DELETE theninsert into public.employees_history(employee_id, action_type, change_dt)values(old.employee_id,DELETE,now());end if;return new;
end ;
$function$
;其次创建表与函数的关联SQL语句如下
create trigger trg_employees_change before
insertor
deleteor
updateonpublic.employees for each row execute function track_emp_change()最终通过INSERT INTO public.employees(id, department_id, time)VALUES(2390, 601, now())进行调用测试完成需求实现。
