几个PostgreSQL的简单内置函数几个PostgreSQL的简单内置函数缘起 1.问题背景: PostgreSQL

几个PostgreSQL的简单内置函数

缘起

1.问题背景:

PostgreSQL是类似于MySQL的关系型数据库，完全开源免费.并有详细的中文文档

2.问题描述:

在通过视图的方式给第三方提供数据,对于查询到的表user表(一个假装的表)的年龄user_age字段(一个假装的字段),必须为数字类型.而数据库中存储的是各种各样.

user_age以文本类型存储,形式包括但不限于87岁,11月25天,7岁9月.这些需要对应修改成87,0,7.

解决

分析

问题很简单,因为提供的视图即是数据,不能使用程序处理.只能使用SQL去处理.之前很少用到,百度上关于pg 的这方面资料比较少．

代码

SELECT 
    CASE WHEN POSITION('岁' IN user_age) > 0 THEN REGEXP_REPLACE(user_age :: TEXT, '岁.*' :: TEXT, '' :: TEXT):: INTEGER
         ELSE  0
    END AS age
FROM
    user

简单解释

从user中提取user_age字段,如果user_age中存在'岁'这个字符,使用正则替换以'岁'开头到结尾的字串为''.如果不存在则是0岁.

总结

这里主要是在下面记录用到的PostgreSQL语法

另外：pg的中文文档可太香了: www.postgres.cn/docs/12/

再提供一个正则练习网站:regex101.com/

各函数详细解析

1. POSITION

语法:

POSITION(substring IN string)

作用: 定位指定子串.

可以用返回值是否大于0,判断substring是否存在于string.

官方文档: www.postgresql.org/docs/9.3/st…

示例

SELECT POSITION('a' IN 'abcd')
//结果
1

SELECT POSITION('ab' IN 'abcd')
//结果
1

SELECT POSITION('om' IN 'Thomas')
//结果
3

2. CASE WHEN

语法:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END
--变种语法:
CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

作用: CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句.而变种语法类似于``Switch`语句.

官方文档: postgres.cn/docs/12/fun…

Case示例

SELECT * FROM test;
//结果
 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
FROM test;
//结果
 a | case
---+-------
 1 | one
 2 | two
 3 | other

变种示例:

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

CASE表达式并不计算任何无助于判断结果的子表达式。例如，下面是一个可以避免被零除错误的方法：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

3. REPLACE

语法: REPLACE(text, from , to )
作用: 替换text中的所有from为to
示例
```
SELECT REPLACE('aiana', 'a', 'n')
```

4. REGEXP_REPLACE

语法: REGEXP_REPLACE(string text, pattern text, replacement* text [, flags text])
作用: 替换匹配一个POSIX正则表达式的子串。

示例:

SELECT REGEXP_REPLACE('Thomas', '.[mN]a.', 'M')
--结果
ThM

SELECT REGEXP_REPALCE('foobarbaz', 'b..', 'X')
--结果
fooXbaz

SELECT REGEXP_REPLACE('abcde', 'c.*', '')
--结果
ab

结尾

参考资料:

pg中文官网: www.postgres.cn/docs/12/. 正则练习: regex101.com/.