PostgreSQL 函数的使用 - 让数据层帮你解决一部分业务逻辑

快一周没有更新了.今天老聂(他的博客 http://niefengjun.cn/ )  终于忍不住了,QQ上提醒了我,上周回了趟老家,耽误了几天. 

PostgreSQL只有函数，是的,没有我们传说中的存储过程,其实PG里的函数已经包括了它,或者说可以利用函数实现它.

前面有2篇关于 PostgreSQL 的文章:

mac 下初始 PostgreSQL 数据库

PostgreSQL 使用 node-postgres 在 node.js 中的应用

函数的组成:

  函数头

  函数体

  所使用语言

函数头用来定义函数及变量，

函数体主要是函数的实现，

函数的语言是指该函数实现的方式，目前内置的有c,plpgsql,sql和internal.

可以通过pg_language系统表来查看当前DB支持的语言.

huheren=# SELECT * FROM pg_language;

显示的就是默认支持的4种语言.

PostgreSQL 函数中支持的逻辑操作符:

< ,> ,= , 或者组合 >= , <= , !=

还支持

BETWEEN  AND , OR , AND 等等.

数学操作符:

+,-,*,/,%,!,&,<<,>>,~,^ 等等.

PostgreSQL 内置了很多很多函数,你可以在自定义函数或者sql语句中直接使用.下面列出的都是常见的,不常见的请参看文档查询

常见的数学操作函数:

abs(x)绝对值函数

ceil(double/numeric)不小于参数的最小整数

floor(double/numeric)不大于参数的最大整数

mod(y,x)取余数

random()取0.0到1.0之间的随机数值

.......

字符串操作函数:

char_length(string)字符串里的字符个数

convert(string,编码名称)使用指定编码转化字符串

lower(string)把字符串转化为小写

upper(string)把字符串转化为大写

substring(string,[开始位置],[个数])截取指定位置的字符串

trim([leading|trailing|both])三个参数对应清除头部|尾部|两边 的空格

replace(原字符串,查找被替换字符串,替换字符串)从原字符串中查找指定的字符串然后全部替换

........

数据类型格式化函数:

下面的所有函数 第一个参数是原始数据,第二个参数是转化格式

to_char(timestamp,text)把时间戳转化成字符串

to_char(interval,text)把时间间隔转化为字符串

to_char(int,text)把整数转化为字符串

to_char(numeric,text)把numeric转化为字符串

to_date(text,text)把字符串转化为日期

to_timestamp(text,text)把字符串转化为时间戳

........

获取当前时间的函数:

CURRENT_DATE//精确到天

CURRENT_TIME//精确到小时

CURRENT_TIMESTAMP//当前时间戳

太多函数,用的时候查询吧,下面要说的是 自定义函数,我们终于回到正题了,但是上面的很多函数我们可以在自定义函数中直接使用,非常方便.

下面我们创建一个非常简单的自定义函数

查询 job 表中的所有数据:

huheren=# create or replace function fun_get_job()
huheren-# returns setof job
huheren-# as
huheren-# $$
huheren$# select * from job;
huheren$# $$
huheren-# language 'sql';
CREATE FUNCTION

出现 CREATE FUNCTION 说明你的函数创建成功了,下面我们来调用一下.

huheren=# SELECT * FROM fun_get_job();
 _id | user_id | title | company | isvip | area | jobyear | address | pay | content |   mobile    |   email   | ext
-----+---------+-------+---------+-------+------+---------+---------+-----+---------+-------------+-----------+-----
   1 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   2 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   3 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   4 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   5 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   6 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   7 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
   8 |   11111 | 测试1 | aaa     | f     | 玉泉 |       5 | bbbbb   | 10  | cccccc  | 13800138000 | dd@ee.com |
(8 rows)

那我们在代码层如何使用呢?

和这里一模一样的,我们代码里的sql做一点小小的改动

之前 JobInfo.prototype.sql_option 对象下的 select_all 属性对应的是 "SELECT * FROM job"

现在我们改成这样的" SELECT * FROM fun_get_job()"

还记得我们在基类 _Base.js 中有一句 debug 模式下打印 SQL 语句的代码吗

//是否打印SQL语句
    if (config.debug) {
        console.log('[SQL:]', sql, '[:SQL]');
        console.log('[PARAMS:]', params, '[:PARAMS]');
    }

所以一会我们可以通过控制台输出日志查看上面改写的SQL 是否生效.

我们启动web项目,输入路由:http://localhost:8887/job

返回数据没有问题,接着我们看下控制台log日志打印出来的SQL

[SQL:] SELECT * FROM fun_get_job() [:SQL]

OK,没有问题.

有人可能有疑问了,执行效果完全和 SELECT * FROM job; 一样,创建一个函数反而没少写代码,这里只是一个简单的DEMO ,pg 里面的指定函数远远不是我们想象中的一个函数,什么意思! 你完全可以把他看做是一个视图,对,就是视图.

下面我们就把 fun_get_job() 当做视图查询看看.

因为job表里面的测试数据随便添加进去的,除了_id 自增ID不一致外没啥区别,所以我们就用_id来做一下过滤:

继续改sql语句如下:

SELECT * FROM fun_get_job() WHERE _id>6

运行看下web程序执行效果:

变化看出来了,一共查询出来2条数据,_id大于6的只有 7,8 ,所以这个函数是不是有点视图的功能啊

刚才的函数没有体现出来函数的3个元素(头,体,尾) 因为没有参数传入,感觉是一个不完整的函数头,下面我们再来一个具体的全功能函数说明一下,还是刚才的函数:

huheren=# CREATE OR REPLACE FUNCTION fun_get_job(
huheren(# flag int)
huheren-# RETURNS SETOF job
huheren-# AS
huheren-# $$
huheren$# SELECT * FROM job WHERE _id>flag;
huheren$# $$
huheren-# LANGUAGE 'sql';
CREATE FUNCTION

上面的语句执行成功, fun_get_job 函数被 replace 了一次,这次和上一次的区别就是多了一个参数 flag

我们从函数的3部分来认识:

函数头 --> 定义了函数名 fun_get_job  ,定义了参数名称和类型  flag int ,指定了返回值  returns setof job 

函数体 --> $$ 之间的部分,及SQL语句或者是相应语言编写的查询语句  $$

函数尾 --> LANGUAGE  'sql' ;  指定函数实现语言是标准 sql

上面函数在调用的时候需要给 flag 参数赋值,不再演示!

最后还遗留一小部分内容,就是通过调用PostgreSQL 函数,将业务逻辑放到函数里,这并不是唯一的,

一般涉及到频繁的数据库操作

或者需要执行一个事务,保证数据一致性

但是,如果并发特别高,反而有的系统会把业务提取出来放到中间件web服务器(毕竟中间件可以横向扩展,负载均衡)

还有一种情况就是换数据库,如果存储介质遇到瓶颈涉及到换数据库时,大量写到数据库的业务逻辑让你骑虎难下!

所以,涉及到数据安全,一致性等事务操作,我们会把业务逻辑放到数据库来处理.

比如微信转账,A打赏B 50元.

对于A来说,他只想得到的结果是成功/失败. 

至于A 账户有没有余额够不够50,

B 有没有绑定银行卡,

对于整个交易过程是不关心的.

上面是一个标准的事务处理过程,先从A账户扣款 50元,然后给B账户加 50元(也许不绑定银行卡也可以放到零钱里,没实地试过)如果任何一个流程不符,事务回滚,保证A,B 2人资金无损,数据一致.

上面这个事务我们会单独开博介绍 PostgreSQL 自定义函数实现事务功能.

晚安!

***end***