读《PostgreSQL服务器编程》笔记

最新推荐文章于 2023-05-04 09:13:15 发布

宸瑞

最新推荐文章于 2023-05-04 09:13:15 发布

阅读量210

点赞数

分类专栏： PostgreSQL 文章标签：数据库 sql

本文链接：https://blog.csdn.net/qq1130207965/article/details/103672396

版权

PostgreSQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

为什么在服务器中进行程序设计

使用PL/pgSQL进行完整性检查

示例：使用一个函数来完成收付款

初始结构及数据

-- 创建表
CREATE TABLE ACCOUNTS(owner text,balance numeric);
--插入数据
INSERT INTO ACCOUNTS VALUES('Bob',100);
INSERT INTO ACCOUNTS VALUES('Mary',200);

函数如下

CREATE OR REPLACE FUNCTION transfer(i_payer text,i_recipient text,i_amount numeric(15,2))
RETURN text
AS
$$
DECLARE
	payer_bal numeric;
BEGIN
	SELECT balance INTO payer_bal FROM accounts WHERE owner = i_payer FOR UPDATE;
	
	IF NOT FOUND THEN
		RETURN 'Payer account not found';
	END IF;
	IF payer_bal < i_amount THEN 
		RETURN 'Not enough funds';
	END IF;
	
	UPDATE accounts
	SET balance = balance + i_amount
	WHERE owner = i_recipient;
	IF NOT FOUND THEN
		RETURN 'Recipient does not exist';
	END IF;

	UPDATE accounts
	SET balance = balance - i_amount
	WHERE owner = i_payer;
	RETURN 'OK';
	
END;
$$ LANGUAGE plpgsql;

使用

SELECT * FROM accounts;
------------------
--"Bob"		100
--"Mary"	200
-----------------
SELECT * FROM transfer('Bob','Mary',14.00);
----------------
"OK"
---------------
SELECT * FROM accounts;
----------------
--"Mary"	214.00
--"Bob"		86.00
----------------

除此之外，我们还可以扩展该函数，去做更多检查工作，比如最小可转账金额，并确保这个可以避免执行，保证完整性

三个可能会返回的错误

SELECT * FROM transfer('Fred','Mary',14.00);
------------------------
--"Payer account not found"
------------------------
SELECT * FROM transfer('Bob','Fred',14.00);
------------------
--"Recipient does not exist"
------------------
SELECT * FROM transfer('Bob','Mary',500.00);
---------------
--"Not enough funds"
---------------

psql工具的格式化输出
- 切换到扩展显示
- psql -x -c "SELECT 1 AS TEST"
- 或者
  - \x
  - SELECT 1 AS test

超越简单函数

使用运算符完成数据比较

示例：定义类型fruit_qty来表示水果的数量，并且告诉PostgreSQL可以来比较苹果与橘子的价值，假设一个橘子等于1.5个苹果的价值

-- 自定义类型
CREATE TYPE FRUIT_QTY as (name text,qty int);
-- 测试
SELECT '("APPLE",3)'::FRUIT_QTY;
--------------------
-- "(APPLE,3)"
--------------------
-- 创建函数
CREATE FUNCTION fruit_qty_larger_than(left_fruit FRUIT_QTY,right_fruit FRUIT_QTY)
RETURNS BOOL
AS $$
BEGIN
	IF (left_fruit.name = 'APPLE' AND right_fruit.name = 'ORANGE') THEN
		RETURN left_fruit.qty > (1.5 * right_fruit.qty); 
	END IF;
	IF (left_fruit.name = 'ORANGE' AND right_fruit.name = 'APPLE') THEN
		RETURN (1.5 * left_fruit.qty) > right_fruit.qty; 
	END IF;
	
	RETURN left_fruit.qty > right_fruit.qty; 
	
END;
$$
LANGUAGE plpgsql;	

-- 测试函数
SELECT fruit_qty_larger_than('("APPLE",4)'::FRUIT_QTY,'("ORANGE",2)'::FRUIT_QTY);
------------------------
-- true
------------------------
SELECT fruit_qty_larger_than('("APPLE",3)'::FRUIT_QTY,'("ORANGE",2)'::FRUIT_QTY);
------------------------
-- false
------------------------
-- 简化,定义操作符
CREATE OPERATOR > (
	leftarg = FRUIT_QTY,
	rightarg = FRUIT_QTY,
	procedure = fruit_qty_larger_than,
	commutator = >
);
-- 测试
SELECT '("ORANGE",2)'::FRUIT_QTY > '("APPLE",2)'::FRUIT_QTY;
---------------------------------
-- true
---------------------------------
SELECT '("ORANGE",2)'::FRUIT_QTY > '("APPLE",3)'::FRUIT_QTY;
---------------------------------
-- false
---------------------------------

使用触发器管理相关数据

服务器程序设计也包括了对自动化的动作（触发器）的设定。设定了自动化动作后，数据库中的一些操作便可触发其他一些事情也跟随发生
例如可以设定一个处理过程，一边对一些商品进行供给，一边在库存表里进行自动预定处理

--创建一个水果库存表
CREATE TABLE fruits_in_stock (
	name text PRIMARY KEY,
	in_stock integer NOT NULL,
	reserved integer NOT NULL DEFAULT 0,
	CHECK (in_stock between 0 and 1000),
	CHECK (reserved <= in_stock)
);
--这里CHECK约束对一些基本规则的执行进行了限制：你不能有多于1000的水果库存（太多了可能会坏掉），你也不能有负的库存，同时不能为别人供给多余当前库存的水果
-- 供给表
CREATE TABLE fruit_offer (
	offer_id serial PRIMARY KEY,
	recipient_name text,
	offer_date timestamp default current_timestamp,
	fruit_name text REFERENCES fruits_in_stock,
	offered_amount integer
);
-- offer表为每次供给设定一个ID（保证你可以区分以后的每一次供给）、接收者、日期、供给水果的名称和供给数量
-- 为了完成自动化的库存管理，你首先需要一个触发器函数。这个函数可以实现管理逻辑
CREATE OR REPLACE FUNCTION reserve_stock_on_offer() 
RETURNS trigger
AS $$
BEGIN 
	IF TG_OP = 'INSERT' THEN
		UPDATE fruits_in_stock
		SET reserved = reserved + NEW.offered_amount
		WHERE name = NEW.fruit_name;
	ELSIF TG_OP = 'UPDATE' THEN
		UPDATE fruits_in_stock
		SET reserved = reserved - OLD.offered_amount + NEW.offered_amount
		WHERE name = NEW.fruit_name;
	ELSIF TG_OP = 'DELETE' THEN
		UPDATE fruits_in_stock
		SET reserved = reserved - OLD.offered_amount
		WHERE name = NEW.fruit_name;
	END IF;
	RETURN NEW;
END;
$$ lANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER manage_reserve_stock_on_offer_change 
AFTER INSERT OR UPDATE OR DELETE ON fruit_offer
FOR EACH ROW EXECUTE PROCEDURE reserve_stock_on_offer();
-- 新增测试数据
INSERT INTO fruits_in_stock(name,in_stock)VALUES('APPLE',500);
INSERT INTO fruits_in_stock(name,in_stock)VALUES('ORANGE',500);
--测试触发器
INSERT INTO fruit_offer(recipient_name,fruit_name,offered_amount)VALUES('Bob','APPLE',100);
SELECT * FROM fruits_in_stock;
--------------------
-- "ORANGE"	500	0
-- "APPLE"	500	100
--------------------
--更改预定情况
UPDATE fruit_offer 
SET offered_amount = 115
WHERE offer_id = 1;
-------------------------
-- UPDATE 1
-------------------------
-- 查看fruit_offer
-----------------------------------------------------------
-- 1	"Bob"	"2019-12-26 03:16:16.167456"	"APPLE"	115
-----------------------------------------------------------
-- 查看fruits_in_stock
-------------------------
-- "ORANGE"	500	0
-- "APPLE"	500	115
-------------------------

-- 尝试修改库存
UPDATE fruits_in_stock 
SET in_stock = 100
WHERE name = 'APPLE';
----------------------------------------------------------------
-- ERROR:  new row for relation "fruits_in_stock" violates check -- constraint "fruits_in_stock_check"
-- DETAIL:  Failing row contains (APPLE, 100, 115).
-- SQL state: 23514
----------------------------------------------------------------
-- 以上其实违反的是CHECK (reserved <= in_stock)

-- 修改订单
UPDATE fruit_offer
SET offered_amount = 1100
WHERE offer_id = 1;
---------------------------------------------------------------
-- ERROR:  new row for relation "fruits_in_stock" violates check constraint "fruits_in_stock_check"
-- DETAIL:  Failing row contains (APPLE, 500, 1100).
-- CONTEXT:  SQL statement "UPDATE fruits_in_stock
-- 		SET reserved = reserved - OLD.offered_amount + NEW.offered_amount
-- 		WHERE name = NEW.fruit_name"
-- PL/pgSQL function reserve_stock_on_offer() line 8 at SQL statement
-- SQL state: 23514
---------------------------------------------------------------
-- 以上数据，是不违反fruit_offer表中的约束的，但是存在触发器，在修改这条记录后，会尝试更新fruits_in_stock表中的库存数，但是1100远大于库存数（存在这条约束），导致这条更新操作（回滚了），下面查询fruit_offer表，便知道是不是一个事务下的了
--------------------------------------------
-- "ORANGE"	500	0
-- "APPLE"	500	115
--------------------------------------------
-- 由此可知，和触发器是在一个事务下的
-- 删除记录
DELETE FROM fruit_offer WHERE offer_id = 1;
-- DELETE 1
select * from fruit_offer;
-- 无记录
SELECT * FROM fruits_in_stock
------------------------------------
-- "ORANGE"	500	0
-- "APPLE"	500	0
------------------------------------

宸瑞

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
读《PostgreSQL服务器编程》笔记

为什么在服务器中进行程序设计使用PL/pgSQL进行完整性检查示例：使用一个函数来完成首付款初始结构及数据-- 创建表CREATE TABLE ACCOUNTS(owner text,balance numeric);--插入数据INSERT INTO ACCOUNTS VALUES('Bob',100);INSERT INTO ACCOUNTS VALUES('Mary',20...
复制链接

扫一扫