Postgresql 从小白到高手六：函数、视图、触发器

simensk

已于 2024-04-19 20:13:35 修改

阅读量966

点赞数 26

文章标签： postgresql 数据库

于 2024-04-19 13:32:03 首次发布

本文链接：https://blog.csdn.net/simensk/article/details/137934267

版权

一：Postgresql 函数

一 :函数

1：数学函数

函数： abs ( numeric_type ) → numeric_type
含义：绝对值
例子：abs(-17.4) → 17.4

cbrt ( double precision ) → double precision
立方根
cbrt(64.0) → 4

ceil ( numeric ) → numeric
ceil ( double precision ) → double precision
大于或等于参数的最接近的整数
ceil(42.2) → 43
ceil(-42.8) → -42

ceiling ( numeric ) → numeric
ceiling ( double precision ) → double precision
大于或等于参数的最接近的整数 (与 ceil 相同)
ceiling(95.3) → 96

degrees ( double precision ) → double precision
将弧度转换为角度
degrees(0.5) → 28.64788975654116

div ( y numeric, x numeric ) → numeric
y/x 的整数商（截断为零位）
div(9, 4) → 2

exp ( numeric ) → numeric
exp ( double precision ) → double precision
指数 (e 的给定次方)
exp(1.0) → 2.7182818284590452

factorial ( bigint ) → numeric
阶乘
factorial(5) → 120

floor ( numeric ) → numeric
floor ( double precision ) → double precision
小于或等于参数的最接近整数
floor(42.8) → 42
floor(-42.8) → -43

gcd ( numeric_type, numeric_type ) → numeric_type
最大公约数 (能将两个输入数整除而无余数的最大正数); 如果两个输入为零则返回 0 ; 适用于 integer, bigint,和 numeric
gcd(1071, 462) → 21

lcm ( numeric_type, numeric_type ) → numeric_type
最小公倍数(两个输入的整数倍的最小的严格正数)；如果任意一个输入值为零则返回0；适用于integer，bigint，和 numeric
lcm(1071, 462) → 23562

ln ( numeric ) → numeric
ln ( double precision ) → double precision
自然对数
ln(2.0) → 0.6931471805599453

log ( numeric ) → numeric
log ( double precision ) → double precision
以10为底的对数
log(100) → 2

log10 ( numeric ) → numeric
log10 ( double precision ) → double precision
以10为底的对数 (与 log 相同)
log10(1000) → 3

log ( b numeric, x numeric ) → numeric
以 b 为底的 x的对数
log(2.0, 64.0) → 6.0000000000

min_scale ( numeric ) → integer
精确表示所提供值所需的最小刻度（小数位数）
min_scale(8.4100) → 2

mod ( y numeric_type, x numeric_type ) → numeric_type
y/x的余数；适用于smallint、integer、bigint、和 numeric
mod(9, 4) → 1

pi ( ) → double precision
π的近似值
pi() → 3.141592653589793

power ( a numeric, b numeric ) → numeric

power ( a double precision, b double precision ) → double precision
a的b次幂
power(9, 3) → 729

radians ( double precision ) → double precision
将角度转换为弧度
radians(45.0) → 0.7853981633974483

round ( v numeric, s integer ) → numeric
把 v 四舍五入到 s 位小数。通过从零舍入来截断ties。
round(42.4382, 2) → 42.44

scale ( numeric ) → integer
参数的刻度（小数点后的位数）
scale(8.4100) → 4

sign ( numeric ) → numeric
sign ( double precision ) → double precision
参数的符号 (-1, 0, 或 +1)
sign(-8.4) → -1

sqrt ( numeric ) → numeric
sqrt ( double precision ) → double precision
平方根
sqrt(2) → 1.4142135623730951

trim_scale ( numeric ) → numeric
通过删除尾数部分的零来降低值的刻度(小数位数)
trim_scale(8.4100) → 8.41

trunc ( numeric ) → numeric
trunc ( double precision ) → double precision
截断整数 (向零靠近)
trunc(42.8) → 42
trunc(-42.8) → -42

trunc ( v numeric, s integer ) → numeric
截断 v 到 s 位小数位置的数字
trunc(42.4382, 2) → 42.43

width_bucket ( operand numeric, low numeric, high numeric, count integer ) → integer
width_bucket ( operand double precision, low double precision, high double precision, count integer ) → integer
返回包含count等宽柱的柱状图中operand所在的柱的编号，范围从low到high。超出该范围的输入则返回0或计数+1。
width_bucket(5.35, 0.024, 10.06, 5) → 3

width_bucket ( operand anycompatible, thresholds anycompatiblearray ) → integer
返回一个柱号，这个柱是在给定数组中operand将被分配的柱。对于一个低于第一个下界的输入返回0。 operand和数组元素可以是具有标准比较操作符的任何类型。 thresholds数组必须被排好序，最小的排在最前面，否则将会得到意想不到的结果。
width_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) → 2

2.随机函数

random ( ) → double precision
返回一个范围 0.0 <= x < 1.0 中的随机值
random() → 0.897124072839091

setseed ( double precision ) → void
为后续的random()调用设置种子；参数必须在-1.0和1.0之间，包括边界值
setseed(0.12345)

3.三角函数

acos ( double precision ) → double precision
反余弦，结果为弧度
acos(1) → 0

acosd ( double precision ) → double precision
反余弦，结果为度数
acosd(0.5) → 60

asin ( double precision ) → double precision
反正弦，结果为弧度
asin(1) → 1.5707963267948966

asind ( double precision ) → double precision
反正弦，结果为度数
asind(0.5) → 30

atan ( double precision ) → double precision
反正切，结果为弧度
atan(1) → 0.7853981633974483

atand ( double precision ) → double precision
反正切，结果为度数
atand(1) → 45

atan2 ( y double precision, x double precision ) → double precision
y/x的反正切，结果为弧度
atan2(1, 0) → 1.5707963267948966
atan2d ( y double precision, x double precision ) → double precision
y/x的反正切，结果为度数
atan2d(1, 0) → 90

cos ( double precision ) → double precision
余弦，参数为弧度
cos(0) → 1

cosd ( double precision ) → double precision
余弦，参数为度数
cosd(60) → 0.5

cot ( double precision ) → double precision
余切，参数为弧度
cot(0.5) → 1.830487721712452

cotd ( double precision ) → double precision
余切，参数为度数
cotd(45) → 1

sin ( double precision ) → double precision
正弦，参数为弧度
sin(1) → 0.8414709848078965

sind ( double precision ) → double precision
正弦，参数为度数
sind(30) → 0.5

tan ( double precision ) → double precision
正切，参数为弧度
tan(1) → 1.5574077246549023

tand ( double precision ) → double precision
正切，参数为度数
tand(45) → 1

sinh ( double precision ) → double precision
双曲正弦
sinh(1) → 1.1752011936438014

cosh ( double precision ) → double precision
双曲余弦
cosh(0) → 1

tanh ( double precision ) → double precision
双曲切线
tanh(1) → 0.7615941559557649

asinh ( double precision ) → double precision
反双曲正弦
asinh(1) → 0.881373587019543

acosh ( double precision ) → double precision
反双曲余弦
acosh(1) → 0

atanh ( double precision ) → double precision
反双曲切线
atanh(0.5) → 0.5493061443340548

4.字符串函数及操作符

text || text → text
连接两个字符串。
‘Post’ || ‘greSQL’ → PostgreSQL
text || anynonarray → text
anynonarray || text → text
将非字符串输入转换为文本，然后将两个字符串串联在一起。（非字符串输入不能为数组类型，因为这将在||操作符的数组中造成歧义。如果你想连接一个数组的文本相等的，请显式地将其转换为text 。）
‘Value: ’ || 42 → Value: 42
text IS [NOT] [form] NORMALIZED → boolean
检查字符串是否在指定的 Unicode 规范化表单中。可选的form关键词指定表单：NFC (默认的), NFD, NFKC, 或 NFKD。只有在服务器编码为UTF8时，才能使用此表达式。请注意，使用这个表达式检查规范化通常比规范化可能已经规范化的字符串要快。
U&’\0061\0308bc’ IS NFD NORMALIZED → t

bit_length ( text ) → integer
返回字符串中的位数(8倍于octet_length)。
bit_length(‘jose’) → 32

char_length ( text ) → integer
character_length ( text ) → integer
返回字符串中的字符数。
char_length(‘josé’) → 4

lower ( text ) → text
根据数据库的语言环境规则，将字符串转换为全部小写。
lower(‘TOM’) → tom

normalize ( text [, form ] ) → text
将字符串转换为指定的Unicode规范化形式。可选的form关键字指定了如下形式:NFC (the default)，NFD， NFKC，或NFKD。该函数只能在服务器编码为UTF8时使用。
normalize(U&‘\0061\0308bc’, NFC) → U&‘\00E4bc’

octet_length ( text ) → integer
返回字符串的字节数。
octet_length(‘josé’) → 5 (if server encoding is UTF8)
octet_length ( character ) → integer
返回字符串中的字节数。由于此版本的函数直接接受character类型，它不会剥离尾随空格。
octet_length('abc '::character(4)) → 4

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text
替换string从start字符开始的子串，并用newsubstring扩展到count字符。如果省略了count，则默认为newsubstring的长度。
overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) → Thomas

position ( substring text IN string text ) → integer
返回string中指定的substring的第一个起始索引，如果不存在则返回零，。
position(‘om’ in ‘Thomas’) → 3

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
如果已指定，提取string从start字符开始的子串，并且在count字符后停止。如果已指定的话。提供至少一个start和count中的至少一个。
substring(‘Thomas’ from 2 for 3) → hom
substring(‘Thomas’ from 3) → omas
substring(‘Thomas’ for 2) → Th
substring ( string text FROM pattern text ) → text
提取匹配POSIX正则表达式的第一个子字符串; 参见第 9.7.3 节。
substring(‘Thomas’ from ‘…$’) → mas
substring ( string text SIMILAR pattern text ESCAPE escape text ) → text
substring ( string text FROM pattern text FOR escape text ) → text
提取匹配 SQL 正则表达式的第一个字串；参见第 9.7.2 节。第一种形式自从SQL:2003被指定，第二种形式仅在SQL:1999中，并应认为是废弃的。
substring(‘Thomas’ similar ‘%#“o_a#”_’ escape ‘#’) → oma

trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text
从string的开始、末端或两端(默认为BOTH )移除仅包含characters(默认为空格)字符的最长字符串。
trim(both ‘xyz’ from ‘yxTomxx’) → Tom
trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text
这是一个非标准的trim()语法。
trim(both from ‘yxTomxx’, ‘xyz’) → Tom

upper ( text ) → text
根据数据库的定位规则，将字符串转换为所有大写。
upper(‘tom’) → TOM

5.函数类型清单：

psql 的函数非常丰富
9.1. 逻辑操作符
9.2. 比较函数和操作符
9.3. 数学函数和操作符
9.4. 字符串函数和操作符
9.5. 二进制串函数和操作符
9.6. 位串函数和操作符
9.7. 模式匹配
9.8. 数据类型格式化函数
9.9. 时间/日期函数和操作符
9.10. 枚举支持函数
9.11. 几何函数和操作符
9.12. 网络地址函数和操作符
9.13. 文本搜索函数和操作符
9.14. UUID 函数
9.15. XML 函数
9.16. JSON 函数和操作符
9.17. 序列操作函数
9.18. 条件表达式
9.19. 数组函数和操作符
9.20. 范围/多范围函数和运算符
9.21. 聚集函数
9.22. 窗口函数
9.23. 子查询表达式
9.24. 行和数组比较
9.25. 集合返回函数
9.26. 系统信息函数和运算符
9.27. 系统管理函数
9.28. 触发器函数
9.29. 事件触发器函数
9.30. 统计信息函数

二：视图

视图就相当于一个临时表，内部可以灵活，我们使用不受业务改动的太大影响。
CREATE VIEW myview AS
SELECT name, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;

三：功能函数 Funtion

1.类别

PostgreSQL提供四种函数：
a 查询语言函数（用SQL格式编写的函数）
CREATE FUNCTION one() RETURNS integer AS $SE L ECT 1 A S res u lt;$ LANGUAGE SQL;

– Alternative syntax for string literal:
CREATE FUNCTION one() RETURNS integer AS ’
SELECT 1 AS result;
’ LANGUAGE SQL;

SELECT one();
011
复杂的函数建议用过程语言写存储过程。

b 过程语言函数（例如，用PL/pgSQL或PL/Tcl编写的函数）
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS ‘function body text’
LANGUAGE plpgsql;
用PL/pgSQL 结构语言写函数、存储过程、触发器，详见后续。

c 内部函数：
内部函数由 C 编写并且已经被静态链接到PostgreSQL 服务器中。该函数定义的“主体”指定该函数的 C 语言名称，它必须和声明 SQL 函数所用的名称一样。
例如： CREATE FUNCTION square_root(double precision) RETURNS double precision
AS ‘dsqrt’
LANGUAGE internal
STRICT;

d C 语言函数：
c语言扩展函数。