目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、如何为PostgreSQL创建一个内置函数，点击前往
2、参考书籍：《PostgreSQL 数据库内核分析》
3、参考书籍：《PostgreSQL服务器编程》
4、参考书籍：《PostgreSQL 必备参考手册》
5、PostgreSQL 13: 新增内置函数Gen_random_uuid()生成UUID数据，点击前往
6、参考书籍：《PostgreSQL 中文手册》
7、参考书籍：《PostgreSQL修炼之道从小工到专家》
PostgreSQL数据库内置函数
- 文章快速说明索引
- 内置函数背景概述
- 内置函数分类汇总
- 数字函数
- 字符串函数
- ASCII
- BIT_LENGTH
- BTRIM
- CHAR_LENGTH
- CHR
- CONCAT
- CONCAT_WS
- CONVERT
- CONVERT_FROM
- CONVERT_TO
- DECODE
- ENCODE
- INITCAP
- LEFT
- MD5
- LENGTH/CHAR_LENGTH/CHARACTER_LENGTH
- LOWER
- LPAD
- LTRIM
- OCTET_LENGTH
- OVERLAY
- PARSE_IDENT
- PG_CLIENT_ENCODING
- QUOTE_IDENT
- QUOTE_LITERAL
- QUOTE_NULLABLE
- REGEXP_MATCH
- REGEXP_MATCHES
- REGEXP_REPLACE
- REGEXP_SPLIT_TO_ARRAY
- REGEXP_SPLIT_TO_TABLE
- REPEAT
- REPLACE
- REVERSE
- RIGHT
- SPLIT_PART
- STRPOS
- POSITION
- STARTS_WITH
- TO_ASCII
- TO_HEX
- RPAD
- RTRIM
- SUBSTRING
- SUBSTR
- TRANSLATE
- TRIM
- UPPER
- FORMAT
- 二进制串函数
- 时间函数
- AGE
- CLOCK_TIMESTAMP
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- DATE_PART
- DATE_TRUNC
- EXTRACT
- ISFINITE
- TIMESTAMP
- JUSTIFY_DAYS
- JUSTIFY_HOURS
- JUSTIFY_INTERVAL
- LOCALTIME
- LOCALTIMESTAMP
- MAKE_DATE
- MAKE_INTERVAL
- MAKE_TIME
- MAKE_TIMESTAMP
- MAKE_TIMESTAMPTZ
- NOW
- STATEMENT_TIMESTAMP
- TIMEOFDAY
- TRANSACTION_TIMESTAMP
- TO_TIMESTAMP
- 增加一个内置函数
文章快速说明索引
学习目标：
前言：之前本人已经更新过 PostgreSQL数据库多种语言下函数的使用方法以及特殊技巧 主要是 基于SQL 和 PL/PGSQL的PostgreSQL函数，点击前往，但是并没有做过PostgreSQL的内置函数的汇总。一来是因为 工作量较大 时间有些紧张；二来作为内置函数，使用和学习都比较简单 平时工作和学习基本上也用不到；三来嘛 互联网上充斥着大量相关的信息 只是都很零碎很片面
思来想去 这件事情还是要去做的，不仅仅为了自己将来的回顾学习 而且这也可以帮到有需要的小伙伴们，除此之外就当是我为 PostgreSQL在中国 做的开源推广。本文将持续更新中(遇到一个补充一个)，此次所使用的PostgreSQL数据库版本为：13.0
Oracle作为行业标杆和龙头大哥般的存在，PostgreSQL的前进之路 路漫漫其修远兮，必将上下而求索！战胜对手的好方法就是向对手学习：要尊重对手 学习对手 分析对手，才能了解对手 掌控对手 战胜对手！而对于当今国内去O大势，PostgreSQL数据库凭借着不隶属于任何组织任何国家的开源特性 强悍的自身性能和最广大开源社区的鼎力支持，成了(很多机构 公司和部门)为数不多的一根救命稻草和狂风暴雨中的一叶孤舟！但是 PostgreSQL永远值得信赖 …
学习内容：（详见目录）
1、PostgreSQL数据库的内置函数
学习时间：
2021年01月10日 03:40:48
学习产出：
1、PostgreSQL数据库的内置函数
2、**** 技术博客 1篇
3、PostgreSQL数据库开源插件orafce的感悟心得
注：下面我们所有的学习环境是Centos7+PostgreSQL13.0+Oracle11g+MySQL5.7
postgres=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.06 sec)
mysql>
内置函数背景概述
在PostgreSQL数据库中，函数和操作符的功能是类似的，它们都接受参数并返回结果。但是在参数的表现形式上，函数不同于操作符，函数的形式一般如下：
functionx(arg1, arg2 ……)
同时函数可以对多个输入参数进行操作，而操作符一般只能处理一个或两个参数 接下来将详细介绍PostgreSQL数据库中支持的函数：这些函数的功能涉及数据库操作的方方面面，大大地提高了数据库的可用性和易用性。内置函数的调用 使用统一的语法格式：
select functionx(arg1, arg2 ……)
PostgreSQL数据库中的内置函数大概在2800+，如下：
postgres=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)
postgres=# select count(*) from pg_proc where prokind = 'f';
count
-------
2881
(1 row)
postgres=#
PostgreSQL的系统表(就是普通表)是关系型数据库存放结构元数据的地方，比如表和字段以及内部登记信息等。内置函数作为一种非常重要的数据库对象，它也具有许多的元信息，用户可通过这些元信息了解内置函数的名字，参数个数和类型，返回值类型，内置函数的实现方法等。而PostgreSQL将内置函数的元信息都记录在系统表pg_proc中，系统表中的每一行对应一个内置函数或内置函数的一个重载（即：函数名相同 参数列表不同） 示例如下：
postgres=# \df+ acos
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | acos | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dacos | arccosine
(1 row)
postgres=# \sf+ acos
CREATE OR REPLACE FUNCTION pg_catalog.acos(double precision)
RETURNS double precision
LANGUAGE internal
IMMUTABLE PARALLEL SAFE STRICT
1 AS $function$dacos$function$
postgres=#
postgres=# select oid,pronamespace,proowner,prolang,procost,pronargs,prorettype,probin from pg_proc where prokind = 'f' and proname='acos';
oid | pronamespace | proowner | prolang | procost | pronargs | prorettype | probin
------+--------------+----------+---------+---------+----------+------------+--------
1601 | 11 | 10 | 12 | 1 | 1 | 701 |
(1 row)
postgres=#
所引用的C函数如下：
// postgres\src\backend\utils\adt\float.c
/*
* dacos - returns the arccos of arg1 (radians)
*/
Datum
dacos(PG_FUNCTION_ARGS)
{
float8 arg1 = PG_GETARG_FLOAT8(0);
float8 result;
/* Per the POSIX spec, return NaN if the input is NaN */
if (isnan(arg1))
PG_RETURN_FLOAT8(get_float8_nan());
/*
* The principal branch of the inverse cosine function maps values in the
* range [-1, 1] to values in the range [0, Pi], so we should reject any
* inputs outside that range and the result will always be finite.
*/
if (arg1 < -1.0 || arg1 > 1.0)
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("input is out of range")));
result = acos(arg1);
if (unlikely(isinf(result)))
float_overflow_error();
PG_RETURN_FLOAT8(result);
}
如上 就是查看一个系统内置函数的整个过程，PostgreSQL内置函数可根据编写语言划分为以下四种类型，我们使用其中任一种语言来自定义一个内置函数：
- internal
- c
- sql
- pluxsql
internal语言是指内部语言，内部函数都是用C写的函数
1、它们通过静态链接的方式嵌入PostgreSQL服务器进程中
2、函数定义的"函数体"确定了函数的C语言名称，它不必与给 SQL 使用的名称相同。出于向下兼容考虑，一个空的函数体也可以被接受，这意味着 C 函数名与 SQL 函数名相同
3、通常情况下，所有在服务器里出现的内部函数都在数据库初始化时定义
4、用户可以用CREATE FUNCTION为内部函数创建额外的别名。内部函数在CREATE FUNCTION命令里是带着internal语言名声明的 示例如下，要为 ACOSD 函数创建一个别名
postgres=# \sf+ ACOSD
CREATE OR REPLACE FUNCTION pg_catalog.acosd(double precision)
RETURNS double precision
LANGUAGE internal
IMMUTABLE PARALLEL SAFE STRICT
1 AS $function$dacosd$function$
postgres=# SELECT ACOSD(-1);
acosd
-------
180
(1 row)
postgres=# CREATE FUNCTION myAcosd (double precision) RETURNS double precision AS 'dacosd' LANGUAGE internal STRICT;
CREATE FUNCTION
postgres=#
postgres=# SELECT myAcosd(0);
myacosd
---------
90
(1 row)
postgres=#
C语言内置函数
1、是用户定义的函数可以用 C 写(或者是与C兼容的语言，比如
C++)
2、这样的函数被编译进动态加载对象(共享库)并且由服务器根据需要进行加载。动态加载的特性是"C 语言函数"和"内部函数"之间的区别 不过，实际的编码习惯在两者之间实际上是一样的
3、若是编写C语言内置函数就应该选用合适的C类型参数，在PostgreSQL中 其内置类型与C语言类型 有着相当的映射关系
4、PostgreSQL对 C 函数有两种调用约定，为函数编写一个PG_FUNCTION_INFO_V1()宏就能指示对该调用约定的支持(“版本1”)。缺少这个宏表示一个老风格的(“版本-0”)函数。现在老风格的函数已经废弃，版本1的C内置函数使用 下面的函数框架
/* 函数声明 */
Datum funcname(PG_FUNCTION_ARGS)
/* 函数版本声明 */
PG_FUNCTION_INFO_V1(funcname);
/* 函数定义 */
Datum funcname(PG_FUNCTION_ARGS)
{
// 函数体
}
下面来看一个简单的示例：
CREATE FUNCTION add_one(integer) RETURNS integer
AS 'DIRECTORY/funcs', 'add_one'
LANGUAGE C STRICT;
-- 注意如下 是SQL函数名“add_one”的重载
CREATE FUNCTION add_one(double precision) RETURNS double precision
AS 'DIRECTORY/funcs', 'add_one_float8'
LANGUAGE C STRICT;
具体如下：
postgres=# CREATE FUNCTION C_add_one(double precision) RETURNS double precision AS '/home/uxdb/Desktop/postgresql/postgres/src/tutorial/funcs','add_one_float8' LANGUAGE C STRICT;
CREATE FUNCTION
postgres=#
postgres=# CREATE FUNCTION C_add_one(integer) RETURNS integer AS '/home/uxdb/Desktop/postgresql/postgres/src/tutorial/funcs','add_one' LANGUAGE C STRICT;
CREATE FUNCTION
postgres=#
postgres=# \df+ C_add_one
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
--------+-----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+-------------
public | c_add_one | double precision | double precision | func | volatile | unsafe | uxdb | invoker | | c | add_one_float8 |
public | c_add_one | integer | integer | func | volatile | unsafe | uxdb | invoker | | c | add_one |
(2 rows)
postgres=# select C_add_one(1);
c_add_one
-----------
2
(1 row)
postgres=# select C_add_one(1.0);
c_add_one
-----------
2
(1 row)
postgres=# select C_add_one(1.1);
c_add_one
-----------
2.1
(1 row)
postgres=#
下面这两部分详见博客：PostgreSQL的学习心得和知识总结（十八）|PostgreSQL数据库函数实践（优秀DBA的必由之路），点击前往
SQL内置函数
1、SQL内置函数是指使用SQL语法编写的内置函数
2、SQL函数执行SQL语句的任意列表，返回列表中最后一个查询结果
3、在简单情况下（非-集合），将返回最后查询结果的第一行。如果最后查询没有返回任何行，则返回空值。
4、另外，一个SQL函数可以声明为返回一个集合（即多行）。方法是把该函数的返回类型声明为SETOF sometype。或者等价声明它为RETURNS TABLE(columns)。这种情况下，最后一条查询结果的所有行都会被返回
PL/PGSQL内置函数
pl/pgsql 是一个块结构语言，函数定义的所有文本内容都必须是一个块。一个块最常用的语法定义格式如下：
[ <<label>> ]
[ declare
declarations ]
begin
statements
end [ label ];
# 注：这里的[] 意指可以省略
pl/pgsql 完整的程序由三个部分组成：声明部分、执行部分、异常处理部分。 其语法格式如下：
declare
--声明部分: 在此声明 pl/sql 用到的变量,类型及游标.
begin
-- 执行部分: 过程及 sql 语句,即程序的主要部分
exception
-- 执行异常部分: 错误处理
end;
如上的函数体代码块实际上为一个字符串，可以用美元符引用：$$书写字符串常量。$$中间可以包含标签名，可以*命名，但是不能以数字开头，比如可以命名为$body$、$_$等等，但是该标签名必须成对出现，且大小写敏感。
解释一下：
1、执行部分不能省略，声明部分和异常处理部分可以
2、块中的每一个declaration和每一条statement都由一个分号终止
3、块支持嵌套：嵌套时子块的 end 后面必须跟一个分号，最外层的块 end 后可不跟分号
4、begin 后面不能跟分号，end 后跟的 标签名 必须和块开始时的标签名一致
5、声明的变量在当前块及其子块中有效，子块开始前可 声明并覆盖 (只在子块内覆盖)外部块的同名变量
6、变量被子块中声明的变量覆盖时，子块可以通过外部块的 label 访问外部块的变量
7、在函数中，使用双减号加注释，它的作用范围是只能在一行有效；而使用/* */来加一行或多行注释
下面来看一个把上面这几点都包括入内的实例：
postgres=# CREATE FUNCTION func2() RETURNS int AS $BODY$
<< outerblock >>
DECLARE
num int := 1;
BEGIN
RAISE NOTICE 'Num here is %', num; -- Prints 1
num := 2;
--
-- Create a subblock
--
DECLARE
num int := 3;
BEGIN
RAISE NOTICE 'Num here is %', num; -- Prints 3
RAISE NOTICE 'Outer num here is %', outerblock.num; -- Prints 2
END;
RAISE NOTICE 'Num here is %', num; -- Prints 2
/* Hello World */
RETURN num;
END outerblock;
$BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# select func2();
NOTICE: Num here is 1
NOTICE: Num here is 3
NOTICE: Outer num here is 2
NOTICE: Num here is 2
func2
-------
2
(1 row)
postgres=#
内置函数分类汇总
| 分类 | 函数名 |
|---|---|
| 聚集函数 | AVG COUNT MAX MIN STDDEV SUM VARIANCE |
| 转换函数 | CAST TO_CHAR TO_DATE TO_NUMBER TO_TIMESTAMP |
| 几何函数 | AREA BOX CENTER CIRCLE DIAMETER HEIGHT ISCLOSED ISOPENV LENGTH LSEG NPOINT PATH PCLOSE POINT POLYGON POPEN RADIUS WIDTH |
| 网络函数 | ABBREV BROADCAST HOST MASKLEN NETMASK NETWORK TEXT TRUNC |
| 数字函数 | ABS ACOS ASIN ATAN ATAN2 CBRT CEIL COS COT DEGREES DIV EXP FLOOR LN LOG MOD PI POW/POWER RADIANS RANDOM ROUND SCALE SIN SIGN SQRT TAN TRUNC WIDTH_BUCKET SINH COSH TANH ASINH ACOSH ATANH |
| SQL函数 | CAST WHEN COALESCE NULLIF |
| 字符串函数 | ASCII BIT_LENGTH BTRIM CHAR_LENGTH CHR CONCAT CONCAT_WS CONVERT CONVERT_FROM CONVERT_TO DECODE ENCODE INITCAP LEFT MD5 LENGTH LOWER LPAD LTRIM OCTET_LENGTH OVERLAY PARSE_IDENT PG_CLIENT_ENCODING QUOTE_IDENT QUOTE_LITERAL QUOTE_NULLABLE REGEXP_MATCH REGEXP_MATCHES REGEXP_REPLACE REGEXP_SPLIT_TO_ARRAY REGEXP_SPLIT_TO_TABLE REPEAT REPLACE REVERSE RIGHT SPLIT_PART STRPOS POSITION STARTS_WITH TO_ASCII TO_HEX RPAD RTRIM SUBSTRING SUBSTR TRANSLATE TRIM UPPER FORMAT |
| 时间函数 | AGE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DATE_PART DATE_TRUNC EXTRACT ISFINITE NOW TIMEOFDAY TIMESTAMP |
| 用户函数 | CURRENT_USER SESSION_USER USER |
| 比较函数 | NUM_NONNULLS NUM_NULLS |
| 其他函数 | ARRAY_DIMS |
数字函数
ABS
其函数语法格式如下：
postgres=# \df+ abs
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------
pg_catalog | abs | bigint | bigint | func | immutable | safe | uxdb | invoker | | internal | int8abs | absolute value
pg_catalog | abs | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | float8abs | absolute value
pg_catalog | abs | integer | integer | func | immutable | safe | uxdb | invoker | | internal | int4abs | absolute value
pg_catalog | abs | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_abs | absolute value
pg_catalog | abs | real | real | func | immutable | safe | uxdb | invoker | | internal | float4abs | absolute value
pg_catalog | abs | smallint | smallint | func | immutable | safe | uxdb | invoker | | internal | int2abs | absolute value
(6 rows)
postgres=#
函数功能：求绝对值
postgres=# select abs(-17.4);
abs
------
17.4
(1 row)
postgres=# select abs(0.0);
abs
-----
0.0
(1 row)
postgres=# select abs(1.59);
abs
------
1.59
(1 row)
postgres=#
注：以下所有这些三角函数都有类型为double precision的参数和返回类型。每一种三角函数都有两个变体，一个以弧度度量角，另一个以角度度量角。
ACOS
其函数语法格式如下：
postgres=# \df+ acos
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | acos | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dacos | arccosine
(1 row)
postgres=# \df+ acosd
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | acosd | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dacosd | arccosine, degrees
(1 row)
postgres=#
函数功能：ACOS返回的是arccos(n)，参数n必须在-1到1的范围内，并且函数返回的值在0到π的范围内，用弧度表示。
postgres=# SELECT ACOS(-1);
acos
-------------------
3.141592653589793
(1 row)
postgres=# SELECT ACOSD(-1);
acosd
-------
180
(1 row)
postgres=#
ASIN
其函数语法格式如下：
postgres=# \df+ ASIN
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | asin | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dasin | arcsine
(1 row)
postgres=# \df+ ASIND
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | asind | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dasind | arcsine, degrees
(1 row)
postgres=#
函数功能：ASIN返回arcsin(n)，参数n必须在-1到1的范围内，函数返回的值在- π/2到π/2的范围内，用弧度表示
postgres=# SELECT ASIN(-1);
asin
---------------------
-1.5707963267948966
(1 row)
postgres=# SELECT ASIND(1);
asind
-------
90
(1 row)
postgres=#
ATAN
其函数语法格式如下：
postgres=# \df+ ATAN
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | atan | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | datan | arctangent
(1 row)
postgres=# \df+ ATAND
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------
pg_catalog | atand | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | datand | arctangent, degrees
(1 row)
postgres=#
函数功能：ATAN返回arctan (n)，参数n可以在一个*范围内，并返回一个范围为- π/2到π/2的值，用弧度表示
postgres=# SELECT ATAN(1)*4;
?column?
-------------------
3.141592653589793
(1 row)
postgres=# SELECT ATAND(-1)*4;
?column?
----------
-180
(1 row)
postgres=#
ATAN2
其函数语法格式如下：
postgres=# \df+ ATAN2
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------
pg_catalog | atan2 | double precision | double precision, double precision | func | immutable | safe | uxdb | invoker | | internal | datan2 | arctangent, two arguments
(1 row)
postgres=# \df+ ATAN2D
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------------------------
pg_catalog | atan2d | double precision | double precision, double precision | func | immutable | safe | uxdb | invoker | | internal | datan2d | arctangent, two arguments, degrees
(1 row)
postgres=#
函数功能：ATAN2返回n1和n2的正切值（atan2(y,x)所表达的意思是坐标原点为起点，指向(x,y)的射线在坐标平面上与x轴正方向之间的角的角度）。参数n1可以在一个无限的范围内，根据n1和n2的符号(用弧度表示)返回一个范围为-pi到pi的值
postgres=# SELECT ATAN2(1,0);
atan2
--------------------
1.5707963267948966
(1 row)
postgres=# SELECT ATAN2D(1,0);
atan2d
--------
90
(1 row)
postgres=# SELECT ATAN2(2,2)*4;
?column?
-------------------
3.141592653589793
(1 row)
postgres=# SELECT ATAN2D(1,1)*4;
?column?
----------
180
(1 row)
postgres=#
CBRT
其函数语法格式如下：
postgres=# \df+ cbrt
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | cbrt | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcbrt | cube root
(1 row)
postgres=#
函数功能：求立方根
postgres=# select cbrt(27.0);
cbrt
--------------------
3.0000000000000004
(1 row)
postgres=# select cbrt(-64.0);
cbrt
------
-4
(1 row)
postgres=# select cbrt(1000);
cbrt
------
10
(1 row)
postgres=# select cbrt(1000.0);
cbrt
------
10
(1 row)
postgres=#
CEIL
其函数语法格式如下：
postgres=# \df+ ceil
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+--------------------------
pg_catalog | ceil | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dceil | nearest integer >= value
pg_catalog | ceil | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_ceil | nearest integer >= value
(2 rows)
postgres=# \df+ ceiling
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+--------------------------
pg_catalog | ceiling | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dceil | nearest integer >= value
pg_catalog | ceiling | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_ceil | nearest integer >= value
(2 rows)
postgres=#
函数功能：不小于参数的最近的整数
postgres=# select ceil(-42.8);
ceil
------
-42
(1 row)
postgres=# select ceil(42.8);
ceil
------
43
(1 row)
postgres=# select ceil(42.1);
ceil
------
43
(1 row)
postgres=# select ceil(-42.1);
ceil
------
-42
(1 row)
postgres=# select ceiling(0.1);
ceiling
---------
1
(1 row)
postgres=#
COS
其函数语法格式如下：
postgres=# \df+ cos
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | cos | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcos | cosine
(1 row)
postgres=# \df+ cosd
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-----------------
pg_catalog | cosd | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcosd | cosine, degrees
(1 row)
postgres=#
函数功能：求余弦
postgres=# select cos(0);
cos
-----
1
(1 row)
postgres=# select cosd(90);
cosd
------
0
(1 row)
postgres=# select cos(PI());
cos
-----
-1
(1 row)
postgres=#
COT
其函数语法格式如下：
postgres=# \df+ cot
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | cot | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcot | cotangent
(1 row)
postgres=# \df+ cotd
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | cotd | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcotd | cotangent, degrees
(1 row)
postgres=#
函数功能：余切
postgres=# select cot(PI()/2);
cot
-----------------------
6.123233995736766e-17
(1 row)
postgres=# select cotd(90);
cotd
------
0
(1 row)
postgres=#
DEGREES
其函数语法格式如下：
postgres=# \df+ degrees
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | degrees | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | degrees | radians to degrees
(1 row)
postgres=#
函数功能：把弧度转为角度(比如 π 就是180°)
postgres=# select degrees(3.1415926);
degrees
--------------------
179.99999692953102
(1 row)
postgres=# select degrees(-3.1415926);
degrees
---------------------
-179.99999692953102
(1 row)
postgres=# select degrees(0);
degrees
---------
0
(1 row)
postgres=#
DIV
其函数语法格式如下：
postgres=# \df+ div
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------+-------------
pg_catalog | div | numeric | numeric, numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_div_trunc | trunc(x/y)
(1 row)
postgres=#
函数功能：y/x的整数商
postgres=# select div(9,4);
div
-----
2
(1 row)
postgres=# select div(12,4);
div
-----
3
(1 row)
postgres=# select div(12,0);
ERROR: division by zero
postgres=#
EXP
其函数语法格式如下：
postgres=# \df+ exp
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------
pg_catalog | exp | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dexp | natural exponential (e^x)
pg_catalog | exp | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_exp | natural exponential (e^x)
(2 rows)
postgres=#
函数功能：自然指数的次幂
postgres=# select exp(1.0);
exp
--------------------
2.7182818284590452
(1 row)
postgres=# select exp(0);
exp
-----
1
(1 row)
postgres=# select exp(-1.0);
exp
--------------------
0.3678794411714423
(1 row)
postgres=# select 1 / exp(-1.0);
?column?
--------------------
2.7182818284590454
(1 row)
postgres=#
FLOOR
其函数语法格式如下：
postgres=# \df+ floor
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+--------------------------
pg_catalog | floor | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dfloor | nearest integer <= value
pg_catalog | floor | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_floor | nearest integer <= value
(2 rows)
postgres=#
函数功能：不大于参数的最近的整数
postgres=# select floor(-42.8);
floor
-------
-43
(1 row)
postgres=# select floor(-42.1);
floor
-------
-43
(1 row)
postgres=# select floor(42.1);
floor
-------
42
(1 row)
postgres=# select floor(0.1);
floor
-------
0
(1 row)
postgres=#
LN
其函数语法格式如下：
postgres=# \df+ ln
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------------
pg_catalog | ln | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dlog1 | natural logarithm
pg_catalog | ln | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_ln | natural logarithm
(2 rows)
postgres=#
函数功能：求自然对数
postgres=# select ln(1.0);
ln
--------------------
0.0000000000000000
(1 row)
postgres=# select ln(2.718281828459);
ln
--------------------
0.9999999999999834
(1 row)
postgres=#
LOG
其函数语法格式如下：
postgres=# \df+ log
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------+-----------------------
pg_catalog | log | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dlog10 | base 10 logarithm
pg_catalog | log | numeric | numeric | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.log(10, $1) | base 10 logarithm
pg_catalog | log | numeric | numeric, numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_log | logarithm base m of n
(3 rows)
postgres=# \df+ log10
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------+-------------------
pg_catalog | log10 | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dlog10 | base 10 logarithm
pg_catalog | log10 | numeric | numeric | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.log(10, $1) | base 10 logarithm
(2 rows)
postgres=#
函数功能：以10为底的对数
postgres=# select log10(100.0);
log10
--------------------
2.0000000000000000
(1 row)
postgres=# select log(100.0);
log
--------------------
2.0000000000000000
(1 row)
postgres=# select log(10);
log
-----
1
(1 row)
postgres=# select log(100);
log
-----
2
(1 row)
postgres=# select log(0.1);
log
---------------------
-1.0000000000000000
(1 row)
postgres=#
和以特定底的对数：
postgres=# select log(2.0, 64.0);
log
--------------------
6.0000000000000000
(1 row)
postgres=# select log(2.0, 1024);
log
---------------------
10.0000000000000000
(1 row)
postgres=# select log(2, 1024);
log
---------------------
10.0000000000000000
(1 row)
postgres=#
MOD
其函数语法格式如下：
postgres=# \df+ mod
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | mod | bigint | bigint, bigint | func | immutable | safe | uxdb | invoker | | internal | int8mod | modulus
pg_catalog | mod | integer | integer, integer | func | immutable | safe | uxdb | invoker | | internal | int4mod | modulus
pg_catalog | mod | numeric | numeric, numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_mod | modulus
pg_catalog | mod | smallint | smallint, smallint | func | immutable | safe | uxdb | invoker | | internal | int2mod | modulus
(4 rows)
postgres=#
函数功能：y/x的余数
postgres=# select mod(9,4);
mod
-----
1
(1 row)
postgres=# select mod(9,3);
mod
-----
0
(1 row)
postgres=# select mod(9,0);
ERROR: division by zero
postgres=#
postgres=# select mod(9,-4);
mod
-----
1
(1 row)
postgres=# select mod(-9,4);
mod
-----
-1
(1 row)
postgres=#
PI
其函数语法格式如下：
postgres=# \df+ pi
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | pi | double precision | | func | immutable | safe | uxdb | invoker | | internal | dpi | PI
(1 row)
postgres=#
函数功能：π常数
postgres=# select pi();
pi
-------------------
3.141592653589793
(1 row)
postgres=#
POW/POWER
其函数语法格式如下：
postgres=# \df+ POW
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+------------------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+----------------
pg_catalog | pow | double precision | double precision, double precision | func | immutable | safe | uxdb | invoker | | internal | dpow | exponentiation
pg_catalog | pow | numeric | numeric, numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_power | exponentiation
(2 rows)
postgres=#
postgres=# \df+ power
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+------------------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+----------------
pg_catalog | power | double precision | double precision, double precision | func | immutable | safe | uxdb | invoker | | internal | dpow | exponentiation
pg_catalog | power | numeric | numeric, numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_power | exponentiation
(2 rows)
postgres=#
函数功能：求a的b次幂
postgres=# select power(9.0, 3.0);
power
----------------------
729.0000000000000000
(1 row)
postgres=# select power(1, 0);
power
-------
1
(1 row)
postgres=# select power(2, -1);
power
-------
0.5
(1 row)
postgres=#
RADIANS
其函数语法格式如下：
postgres=# \df+ RADIANS
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | radians | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | radians | degrees to radians
(1 row)
postgres=#
函数功能：把角度转为弧度
postgres=# select RADIANS(180);
radians
-------------------
3.141592653589793
(1 row)
postgres=#
RANDOM
其函数语法格式如下：
postgres=# \df+ setseed
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+------------+-------+----------+-------------------+----------+-------------+-----------------
pg_catalog | setseed | void | double precision | func | volatile | restricted | uxdb | invoker | | internal | setseed | set random seed
(1 row)
postgres=# select setseed(0);
setseed
---------
(1 row)
postgres=# \df+ random
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+------------+-------+----------+-------------------+----------+-------------+--------------
pg_catalog | random | double precision | | func | volatile | restricted | uxdb | invoker | | internal | drandom | random value
(1 row)
postgres=#
函数功能：setseed 为后续的random()调用设置种子（值为于 -1.0 和 1.0 之间，包括边界值）；random() 范围 0.0 <= x < 1.0 中的随机值
postgres=# select random();
random
-----------------------
3.907985046680551e-14
(1 row)
postgres=# select setseed(1);
setseed
---------
(1 row)
postgres=# select random();
random
--------------------
0.4999104186659835
(1 row)
postgres=# select random();
random
-------------------
0.770017612227381
(1 row)
postgres=# select setseed(-1);
setseed
---------
(1 row)
postgres=# select random();
random
--------------------
0.5000895813340946
(1 row)
postgres=# select random();
random
---------------------
0.23195317712191965
(1 row)
postgres=#
注：random()函数使用了一个简单的线性共轭算法。 它的速度很快，但不适合于密码学应用；关于更安全的替代方案，请参阅 pgcrypto模块。 如果setseed()被调用，那么当前会话中的后续random()调用的结果可以通过使用相同的参数重新发布setseed()来重复
ROUND
其函数语法格式如下：
postgres=# \df+ ROUND
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------+----------------------------------
pg_catalog | round | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dround | round to nearest integer
pg_catalog | round | numeric | numeric | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.round($1,0) | value rounded to 'scale' of zero
pg_catalog | round | numeric | numeric, integer | func | immutable | safe | uxdb | invoker | | internal | numeric_round | value rounded to 'scale'
(3 rows)
postgres=#
函数功能：四舍五入为最接近的整数
postgres=# select round(42.4);
round
-------
42
(1 row)
postgres=# select round(42.49);
round
-------
42
(1 row)
postgres=# select round(42.9);
round
-------
43
(1 row)
postgres=# select round(-42.9);
round
-------
-43
(1 row)
postgres=# select round(-42.1);
round
-------
-42
(1 row)
postgres=#
四舍五入为最接近的数据(保留n为有效数字)
postgres=# select round(42.4382, 2);
round
-------
42.44
(1 row)
postgres=# select round(42.4382, 3);
round
--------
42.438
(1 row)
postgres=# select round(42.4382, 0);
round
-------
42
(1 row)
postgres=# select round(42.5382, 0);
round
-------
43
(1 row)
postgres=#
SCALE
其函数语法格式如下：
postgres=# \df+ scale
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+-------------------------------------------------
pg_catalog | scale | integer | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_scale | number of decimal digits in the fractional part
(1 row)
postgres=#
函数功能：参数的精度（小数点后的位数）
postgres=# select scale(8.41);
scale
-------
2
(1 row)
postgres=# select scale(8.410);
scale
-------
3
(1 row)
postgres=# select scale(8.0000);
scale
-------
4
(1 row)
postgres=# select scale(8);
scale
-------
0
(1 row)
postgres=#
SIGN
其函数语法格式如下：
postgres=# \df+ sign
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+---------------
pg_catalog | sign | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dsign | sign of value
pg_catalog | sign | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_sign | sign of value
(2 rows)
postgres=#
函数功能：参数的符号（-1, 0, +1）
postgres=# select sign(-8.4);
sign
------
-1
(1 row)
postgres=# select sign(1.6);
sign
------
1
(1 row)
postgres=# select sign(0);
sign
------
0
(1 row)
postgres=# select sign(-8.4097);
sign
------
-1
(1 row)
postgres=#
SIN
其函数语法格式如下：
postgres=# \df+ sin
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | sin | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dsin | sine
(1 row)
postgres=# \df+ sind
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------
pg_catalog | sind | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dsind | sine, degrees
(1 row)
postgres=#
函数功能：正弦
postgres=# select sin(0);
sin
-----
0
(1 row)
postgres=# select sind(90);
sind
------
1
(1 row)
postgres=# select sin(PI());
sin
------------------------
1.2246467991473532e-16
(1 row)
postgres=# select sin(PI()/2);
sin
-----
1
(1 row)
postgres=# select sind(180);
sind
------
0
(1 row)
postgres=# select tan(PI()/2) * cot(PI()/2);
?column?
----------
1
(1 row)
postgres=#
SQRT
其函数语法格式如下：
postgres=# \df+ sqrt
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+-------------
pg_catalog | sqrt | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dsqrt | square root
pg_catalog | sqrt | numeric | numeric | func | immutable | safe | uxdb | invoker | | internal | numeric_sqrt | square root
(2 rows)
postgres=#
函数功能：求平方根
postgres=# select sqrt(2.0);
sqrt
-------------------
1.414213562373095
(1 row)
postgres=# select sqrt(1024);
sqrt
------
32
(1 row)
postgres=# select sqrt(-1024);
ERROR: cannot take square root of a negative number
postgres=# select sqrt(0);
sqrt
------
0
(1 row)
postgres=#
TAN
其函数语法格式如下：
postgres=# \df+ tan
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | tan | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dtan | tangent
(1 row)
postgres=# \df+ tand
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | tand | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dtand | tangent, degrees
(1 row)
postgres=#
函数功能：正切
postgres=# select tan(0);
tan
-----
0
(1 row)
postgres=# select tand(45);
tand
------
1
(1 row)
postgres=# select tan(PI()/4);
tan
--------------------
0.9999999999999999
(1 row)
postgres=#
TRUNC
其函数语法格式如下：
postgres=# \df+ TRUNC
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------+------------------------------------
pg_catalog | trunc | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dtrunc | truncate to integer
pg_catalog | trunc | macaddr | macaddr | func | immutable | safe | uxdb | invoker | | internal | macaddr_trunc | MACADDR manufacturer fields
pg_catalog | trunc | macaddr8 | macaddr8 | func | immutable | safe | uxdb | invoker | | internal | macaddr8_trunc | MACADDR8 manufacturer fields
pg_catalog | trunc | numeric | numeric | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.trunc($1,0) | value truncated to 'scale' of zero
pg_catalog | trunc | numeric | numeric, integer | func | immutable | safe | uxdb | invoker | | internal | numeric_trunc | value truncated to 'scale'
(5 rows)
postgres=#
函数功能：截断（向零靠近）
postgres=# select trunc(42.8);
trunc
-------
42
(1 row)
postgres=# select trunc(42.1);
trunc
-------
42
(1 row)
postgres=# select trunc(42.1024);
trunc
-------
42
(1 row)
postgres=#
截断为s位小数位置的数字：
postgres=# select trunc(42.4382, 2);
trunc
-------
42.43
(1 row)
postgres=# select trunc(42.4382, 1);
trunc
-------
42.4
(1 row)
postgres=# select trunc(42.4382, 0);
trunc
-------
42
(1 row)
postgres=#
WIDTH_BUCKET
其函数语法格式如下：
postgres=# \df+ WIDTH_BUCKET
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+----------------------+----------------------------------------------------------------------
pg_catalog | width_bucket | integer | anyelement, anyarray | func | immutable | safe | uxdb | invoker | | internal | width_bucket_array | bucket number of operand given a sorted array of bucket lower bounds
pg_catalog | width_bucket | integer | double precision, double precision, double precision, integer | func | immutable | safe | uxdb | invoker | | internal | width_bucket_float8 | bucket number of operand in equal-width histogram
pg_catalog | width_bucket | integer | numeric, numeric, numeric, integer | func | immutable | safe | uxdb | invoker | | internal | width_bucket_numeric | bucket number of operand in equal-width histogram
(3 rows)
postgres=#
函数功能1：返回一个桶号，这个桶是在给定数组中operand 将被分配的桶，该数组列出了桶的下界。对于一个低于第一个下界的输入返回 0。thresholds 数组必须被排好序， 最小的排在最前面，否则将会得到意想不到的结果
# width_bucket(operand anyelement, thresholds anyarray)
postgres=# select width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]);
width_bucket
--------------
2
(1 row)
postgres=#
函数功能2：返回一个桶号，这个桶是在一个柱状图中operand将被分配的那个桶，该柱状图有count个散布在范围b1到b2上的等宽桶。对于超过该范围的输入，将返回0或者count+1
# width_bucket(op dp, b1 dp, b2 dp, count int)
postgres=# select width_bucket(5.35, 0.024, 10.06, 5);
width_bucket
--------------
3
(1 row)
postgres=#
函数功能3：返回一个桶号，这个桶是在一个柱状图中operand将被分配的那个桶，该柱状图有count个散布在范围b1到b2上的等宽桶。对于超过该范围的输入，将返回0或者count+1
# width_bucket(op numeric, b1 numeric, b2 numeric, count int)
postgres=# select width_bucket(6, 0, 10, 5);
width_bucket
--------------
4
(1 row)
postgres=#
注：下面所有这些双曲函数接收参数，并返回类型为double precision的值
SINH
其函数语法格式如下：
postgres=# \df+ sinh
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-----------------
pg_catalog | sinh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dsinh | hyperbolic sine
(1 row)
postgres=#
函数功能：SINH返回双曲正弦 SINH(n)
postgres=# select sinh(0);
sinh
------
0
(1 row)
postgres=# select sinh(-1)+sinh(1);
?column?
----------
0
(1 row)
postgres=#
COSH
其函数语法格式如下：
postgres=# \df+ cosh
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------------
pg_catalog | cosh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dcosh | hyperbolic cosine
(1 row)
postgres=#
函数功能：COSH函数返回双曲余弦 COSH(n)
postgres=# select cosh(0);
cosh
------
1
(1 row)
postgres=# select cosh(1)-cosh(-1);
?column?
----------
0
(1 row)
postgres=#
TANH
其图像如下：
其函数语法格式如下：
postgres=# \df+ tanh
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | tanh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dtanh | hyperbolic tangent
(1 row)
postgres=#
函数功能：TANH返回n的双曲正切
postgres=# SELECT TANH(0);
tanh
------
0
(1 row)
postgres=# SELECT TANH(1);
tanh
--------------------
0.7615941559557649
(1 row)
postgres=#
ASINH
其函数语法格式如下：
postgres=# \df+ ASINH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------------------
pg_catalog | asinh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dasinh | inverse hyperbolic sine
(1 row)
postgres=#
函数功能：反双曲正弦
postgres=# select asinh(0);
asinh
-------
0
(1 row)
postgres=# select asinh(-1)+asinh(1);
?column?
----------
0
(1 row)
postgres=#
ACOSH
其函数语法格式如下：
postgres=# \df+ ACOSH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------
pg_catalog | acosh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | dacosh | inverse hyperbolic cosine
(1 row)
postgres=#
函数功能：反双曲余弦
postgres=# select acosh(1);
acosh
-------
0
(1 row)
postgres=#
ATANH
其函数语法格式如下：
postgres=# \df+ ATANH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------------
pg_catalog | atanh | double precision | double precision | func | immutable | safe | uxdb | invoker | | internal | datanh | inverse hyperbolic tangent
(1 row)
postgres=#
函数功能：反双曲切线
postgres=# select ATANH(0);
atanh
-------
0
(1 row)
postgres=# select ATANH(-0.5)+ATANH(0.5);
?column?
----------
0
(1 row)
postgres=#
字符串函数
ASCII
其函数语法格式如下：
postgres=# \df+ ascii
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------------
pg_catalog | ascii | integer | text | func | immutable | safe | uxdb | invoker | | internal | ascii | convert first char to int4
(1 row)
postgres=#
函数功能：参数第一个字符的ASCII代码。对于UTF8返回该字符的Unicode代码点。对于其他多字节编码，该参数必须是一个ASCII字符
postgres=# select ascii('A');
ascii
-------
65
(1 row)
postgres=# select ascii('a');
ascii
-------
97
(1 row)
postgres=# select ascii('apple');
ascii
-------
97
(1 row)
postgres=#
BIT_LENGTH
其函数语法格式如下：
postgres=# \df+ BIT_LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+----------------------------------------+----------------
pg_catalog | bit_length | integer | bit | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.length($1) | length in bits
pg_catalog | bit_length | integer | bytea | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.octet_length($1) * 8 | length in bits
pg_catalog | bit_length | integer | text | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.octet_length($1) * 8 | length in bits
(3 rows)
postgres=#
函数功能：串中的位数 bit
postgres=# select bit_length('jose');
bit_length
------------
32
(1 row)
postgres=# select bit_length('j');
bit_length
------------
8
(1 row)
postgres=# select bit_length(1::text);
bit_length
------------
8
(1 row)
postgres=#
BTRIM
其函数语法格式如下：
# btrim(string text [, characters text])
postgres=# \df+ BTRIM
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------------------------------
pg_catalog | btrim | bytea | bytea, bytea | func | immutable | safe | uxdb | invoker | | internal | byteatrim | trim both ends of string
pg_catalog | btrim | text | text | func | immutable | safe | uxdb | invoker | | internal | btrim1 | trim spaces from both ends of string
pg_catalog | btrim | text | text, text | func | immutable | safe | uxdb | invoker | | internal | btrim | trim selected characters from both ends of string
(3 rows)
postgres=#
函数功能：从string的开头或结尾删除最长的只包含characters（默认是一个空格）的串
postgres=# select btrim('xyxtrimyyx', 'xyz');
btrim
-------
trim
(1 row)
postgres=# select btrim('xyxtrimyyx');
btrim
------------
xyxtrimyyx
(1 row)
postgres=# select btrim(' x yxtr i myyx');
btrim
-----------------
x yxtr i myyx
(1 row)
postgres=# select btrim(' xyxtrimyyx ');
btrim
------------
xyxtrimyyx
(1 row)
postgres=#
CHAR_LENGTH
其函数语法格式如下：
postgres=# \df+ character_length
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | character_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | character_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | character length
(2 rows)
postgres=# \df+ char_length
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | char_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | char_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | character length
(2 rows)
postgres=#
函数功能：串中字符数
postgres=# select char_length('jose');
char_length
-------------
4
(1 row)
postgres=# select character_length('');
character_length
------------------
0
(1 row)
postgres=#
CHR
其函数语法格式如下：
postgres=# \df+ chr
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------
pg_catalog | chr | text | integer | func | immutable | safe | uxdb | invoker | | internal | chr | convert int4 to char
(1 row)
postgres=#
函数功能：给定代码的字符。对于UTF8该参数被视作一个Unicode代码点。对于其他多字节编码该参数必须指定一个ASCII字符。NULL (0) 字符不被允许，因为文本数据类型不能存储这种字节
postgres=# select chr(65);
chr
-----
A
(1 row)
postgres=# select chr(97);
chr
-----
a
(1 row)
postgres=# select chr(0);
ERROR: null character not permitted
postgres=#
CONCAT
其函数语法格式如下：
# concat(str "any" [, str "any" [, ...] ])
postgres=# \df+ concat
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------
pg_catalog | concat | text | VARIADIC "any" | func | stable | safe | uxdb | invoker | | internal | text_concat | concatenate values
(1 row)
postgres=#
函数功能：串接所有参数的文本表示
postgres=# select concat('abcde', 2, NULL, 22);
concat
----------
abcde222
(1 row)
postgres=# select 'abcde'||2||NULL||22 as concat;
concat
--------
(1 row)
postgres=# select 'abcde'||2||22 as concat;
concat
----------
abcde222
(1 row)
postgres=#
CONCAT_WS
其函数语法格式如下：
# concat_ws(sep text, str "any" [, str "any" [, ...] ])
postgres=# \df+ CONCAT_WS
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+----------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+------------------------------------
pg_catalog | concat_ws | text | text, VARIADIC "any" | func | stable | safe | uxdb | invoker | | internal | text_concat_ws | concatenate values with separators
(1 row)
postgres=#
函数功能：将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略
postgres=# select concat_ws(',', 'abcde', 2, NULL, 22);
concat_ws
------------
abcde,2,22
(1 row)
postgres=# select concat_ws('/', 'abcde', 2, NULL, 22);
concat_ws
------------
abcde/2/22
(1 row)
postgres=# select concat_ws('_', 'abcde', 2, NULL, 22);
concat_ws
------------
abcde_2_22
(1 row)
postgres=#
CONVERT
其函数语法格式如下：
# convert(string bytea, src_encoding name, dest_encoding name)
postgres=# \df+ convert
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------------------------------
pg_catalog | convert | bytea | bytea, name, name | func | stable | safe | uxdb | invoker | | internal | pg_convert | convert string with specified encoding names
(1 row)
postgres=#
函数功能：将字符串转换为dest_encoding。原始编码由src_encoding指定。string在这个编码中必须可用。转换可以使用CREATE CONVERSION定义；也有一些预定义的转换
postgres=# select convert('HelloWorld', 'UTF8', 'SQL_ASCII');
convert
------------------------
\x48656c6c6f576f726c64
(1 row)
postgres=# select convert_from('48656c6c6f576f726c64','UTF8')
postgres-# ;
convert_from
----------------------
48656c6c6f576f726c64
(1 row)
postgres=# select convert_from('\x48656c6c6f576f726c64','UTF8');
convert_from
--------------
HelloWorld
(1 row)
postgres=# select convert('HelloWorld', 'GB18030', 'GBK');
2021-01-13 17:38:23.102 CST [44328] ERROR: default conversion function for encoding "GB18030" to "GBK" does not exist
2021-01-13 17:38:23.102 CST [44328] STATEMENT: select convert('HelloWorld', 'GB18030', 'GBK');
ERROR: default conversion function for encoding "GB18030" to "GBK" does not exist
postgres=#
CONVERT_FROM
其函数语法格式如下：
# convert_from(string bytea, src_encoding name)
postgres=# \df+ convert_from
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------+----------------------------------------------------
pg_catalog | convert_from | text | bytea, name | func | stable | safe | uxdb | invoker | | internal | pg_convert_from | convert string with specified source encoding name
(1 row)
postgres=#
函数功能：将字符串转换为数据库编码。原始编码由src_encoding指定。string在这个编码中必须可用
postgres=# select convert('HelloWorld', 'UTF8', 'SQL_ASCII');
convert
------------------------
\x48656c6c6f576f726c64
(1 row)
postgres=# select convert_from('48656c6c6f576f726c64','UTF8')
postgres-# ;
convert_from
----------------------
48656c6c6f576f726c64
(1 row)
postgres=# select convert_from('\x48656c6c6f576f726c64','UTF8');
convert_from
--------------
HelloWorld
(1 row)
postgres=#
CONVERT_TO
其函数语法格式如下：
# convert_to(string text, dest_encoding name)
postgres=# \df+ CONVERT_TO
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+---------------------------------------------------------
pg_catalog | convert_to | bytea | text, name | func | stable | safe | uxdb | invoker | | internal | pg_convert_to | convert string with specified destination encoding name
(1 row)
postgres=#
函数功能：将字符串转换为dest_encoding
postgres=# select convert_to('HelloWorld', 'UTF8');
convert_to
------------------------
\x48656c6c6f576f726c64
(1 row)
postgres=#
DECODE
其函数语法格式如下：
# decode(string text, format text)
postgres=# \df+ DECODE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+----------------------------------------------------
pg_catalog | decode | bytea | text, text | func | immutable | safe | uxdb | invoker | | internal | binary_decode | convert ascii-encoded text string into bytea value
(1 row)
postgres=#
函数功能：从string中的文本表达解码二进制数据。format的选项和encode中的一样
postgres=# select decode('MTIzAAE=', 'base64');
decode
--------------
\x3132330001
(1 row)
postgres=#
ENCODE
其函数语法格式如下：
# encode(data bytea, format text)
postgres=# \df+ ENCODE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+------------------------------------------------------
pg_catalog | encode | text | bytea, text | func | immutable | safe | uxdb | invoker | | internal | binary_encode | convert bytea value into some ascii-only text string
(1 row)
postgres=#
函数功能：将二进制数据编码成一个文本表达。支持的格式有：base64、hex、escape。escape将零字节和高位组字节转换为八进制序列（\nnn）和双写的反斜线
postgres=# select encode('123\000\001', 'base64');
encode
----------
MTIzAAE=
(1 row)
postgres=# select encode('123\000\001', 'escape');
encode
-------------
123\000\x01
(1 row)
postgres=#
INITCAP
其函数语法格式如下：
# initcap(string)
postgres=# \df+ INITCAP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------
pg_catalog | initcap | text | text | func | immutable | safe | uxdb | invoker | | internal | initcap | capitalize each word
(1 row)
postgres=#
函数功能：将每一个词的第一个字母转换为大写形式并把剩下的字母转换为小写形式。词是由非字母数字字符分隔的字母数字字符的序列
postgres=# select initcap('hi THOMAS');
initcap
-----------
Hi Thomas
(1 row)
postgres=# select initcap('1hi 2tHOMAS');
initcap
-------------
1hi 2thomas
(1 row)
postgres=# select initcap('1hi tHOMAS');
initcap
------------
1hi Thomas
(1 row)
postgres=#
LEFT
其函数语法格式如下：
# left(str text, n int)
postgres=# \df+ left
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------------
pg_catalog | left | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | text_left | extract the first n characters
(1 row)
postgres=#
函数功能：返回字符串中的前n个字符。当n为负时，将返回除了最后|n|个字符之外的所有字符
postgres=# select left('abcde', 2);
left
------
ab
(1 row)
postgres=# select left('abcde', 0);
left
------
(1 row)
postgres=# select left('abcde', -2);
left
------
abc
(1 row)
postgres=#
MD5
其函数语法格式如下：
# md5(string)
postgres=# \df+ md5
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | md5 | text | bytea | func | immutable | safe | uxdb | invoker | | internal | md5_bytea | MD5 hash
pg_catalog | md5 | text | text | func | immutable | safe | uxdb | invoker | | internal | md5_text | MD5 hash
(2 rows)
postgres=#
函数功能：计算string的 MD5 哈希，返回十六进制的结果
postgres=# select md5('abc');
md5
----------------------------------
900150983cd24fb0d6963f7d28e17f72
(1 row)
postgres=# select md5('helloworld');
md5
----------------------------------
fc5e038d38a57032085441e7fe7010b0
(1 row)
postgres=#
LENGTH/CHAR_LENGTH/CHARACTER_LENGTH
其函数语法格式如下：
# length(string)
# length(string bytea, encoding name)
postgres=# \df+ length
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------+----------------------------------------
pg_catalog | length | integer | bit | func | immutable | safe | uxdb | invoker | | internal | bitlength | bitstring length
pg_catalog | length | integer | bytea | func | immutable | safe | uxdb | invoker | | internal | byteaoctetlen | octet length
pg_catalog | length | integer | bytea, name | func | stable | safe | uxdb | invoker | | internal | length_in_encoding | length of string in specified encoding
pg_catalog | length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | length | double precision | lseg | func | immutable | safe | uxdb | invoker | | internal | lseg_length | distance between endpoints
pg_catalog | length | double precision | path | func | immutable | safe | uxdb | invoker | | internal | path_length | sum of path segments
pg_catalog | length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | length
pg_catalog | length | integer | tsvector | func | immutable | safe | uxdb | invoker | | internal | tsvector_length | number of lexemes
(8 rows)
postgres=# \df+ CHARACTER_LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | character_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | character_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | character length
(2 rows)
postgres=# \df+ CHAR_LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------
pg_catalog | char_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | char_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | character length
(2 rows)
postgres=#
函数功能：
# string中的字符数
postgres=# select length('jose');
length
--------
4
(1 row)
# string在给定编码中的字符数。string必须在这个编码中有效
postgres=# select length('jose', 'UTF8');
length
--------
4
(1 row)
postgres=# select CHAR_LENGTH('hello');
char_length
-------------
5
(1 row)
postgres=#
LOWER
其函数语法格式如下：
postgres=# \df+ lower
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------
pg_catalog | lower | anyelement | anyrange | func | immutable | safe | uxdb | invoker | | internal | range_lower | lower bound of range
pg_catalog | lower | text | text | func | immutable | safe | uxdb | invoker | | internal | lower | lowercase
(2 rows)
postgres=#
函数功能：将字符串转换为小写形式
postgres=# select lower('TOM');
lower
-------
tom
(1 row)
postgres=# select lower('TOMeM');
lower
-------
tomem
(1 row)
postgres=#
LPAD
其函数语法格式如下：
# lpad(string text, length int [, fill text])
postgres=# \df+ lpad
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------------+---------------------------
pg_catalog | lpad | text | text, integer | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.lpad($1, $2, ' ') | left-pad string to length
pg_catalog | lpad | text | text, integer, text | func | immutable | safe | uxdb | invoker | | internal | lpad | left-pad string to length
(2 rows)
postgres=#
函数功能：将string通过前置字符fill（默认是一个空格）填充到长度length。如果string已经长于length，则它被（从右边）截断
postgres=# select lpad('hi', 5, 'xy');
lpad
-------
xyxhi
(1 row)
postgres=# select lpad('hi', 6, 'xy');
lpad
--------
xyxyhi
(1 row)
postgres=# select lpad('hi', 4, 'xy');
lpad
------
xyhi
(1 row)
postgres=# select lpad('hi', 3, 'xy');
lpad
------
xhi
(1 row)
postgres=# select lpad('hi', 6);
lpad
--------
hi
(1 row)
postgres=# select lpad('hi', 6, '*');
lpad
--------
****hi
(1 row)
postgres=#
LTRIM
其函数语法格式如下：
# ltrim(string text [, characters text])
postgres=# \df+ LTRIM
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------------------------------
pg_catalog | ltrim | text | text | func | immutable | safe | uxdb | invoker | | internal | ltrim1 | trim spaces from left end of string
pg_catalog | ltrim | text | text, text | func | immutable | safe | uxdb | invoker | | internal | ltrim | trim selected characters from left end of string
(2 rows)
postgres=#
函数功能：从string的开头删除最长的只包含characters（默认是一个空格）的串
postgres=# select ltrim('zzzytest', 'xyz');
ltrim
-------
test
(1 row)
postgres=# select ltrim('pzzzytest', 'xyz');
ltrim
-----------
pzzzytest
(1 row)
postgres=# select ltrim('xpzzzytest', 'xyz');
ltrim
-----------
pzzzytest
(1 row)
postgres=#
OCTET_LENGTH
其函数语法格式如下：
postgres=# \df+ OCTET_LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+--------------
pg_catalog | octet_length | integer | bit | func | immutable | safe | uxdb | invoker | | internal | bitoctetlength | octet length
pg_catalog | octet_length | integer | bytea | func | immutable | safe | uxdb | invoker | | internal | byteaoctetlen | octet length
pg_catalog | octet_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharoctetlen | octet length
pg_catalog | octet_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textoctetlen | octet length
(4 rows)
postgres=#
函数功能：串中的字节数
postgres=# select octet_length('jose');
octet_length
--------------
4
(1 row)
postgres=#
OVERLAY
其函数语法格式如下：
# overlay(string placing string from int [for int])
postgres=# \df+ OVERLAY
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+--------------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------+---------------------------------
pg_catalog | overlay | bit | bit, bit, integer | func | immutable | safe | uxdb | invoker | | internal | bitoverlay_no_len | substitute portion of bitstring
pg_catalog | overlay | bit | bit, bit, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bitoverlay | substitute portion of bitstring
pg_catalog | overlay | bytea | bytea, bytea, integer | func | immutable | safe | uxdb | invoker | | internal | byteaoverlay_no_len | substitute portion of string
pg_catalog | overlay | bytea | bytea, bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | byteaoverlay | substitute portion of string
pg_catalog | overlay | text | text, text, integer | func | immutable | safe | uxdb | invoker | | internal | textoverlay_no_len | substitute portion of string
pg_catalog | overlay | text | text, text, integer, integer | func | immutable | safe | uxdb | invoker | | internal | textoverlay | substitute portion of string
(6 rows)
postgres=#
函数功能：替换子串
postgres=# select overlay('Txxxxas' placing 'hom' from 2 for 4);
overlay
---------
Thomas
(1 row)
postgres=# select overlay('Txxxxas' placing 'hom' from 2 for 5);
overlay
---------
Thoms
(1 row)
postgres=# select overlay('Txxxxas' placing 'hom' from 2);
overlay
---------
Thomxas
(1 row)
postgres=# select overlay('Txxxxas' placing 'homjjhhjg' from 2);
overlay
------------
Thomjjhhjg
(1 row)
postgres=#
PARSE_IDENT
其函数语法格式如下：
# parse_ident(qualified_identifier text [, strictmode boolean DEFAULT true ] )
postgres=# \df+ PARSE_IDENT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------------+------------------+---------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------------------------------------
pg_catalog | parse_ident | text[] | str text, strict boolean DEFAULT true | func | immutable | safe | uxdb | invoker | | internal | parse_ident | parse qualified identifier to array of identifiers
(1 row)
postgres=#
函数功能：把qualified_identifier分成一个标识符数组，移除单个标识符上的任何引号。默认情况下，最后一个标识符后面的多余字符会被当做错误。但是如果第二个参数为false，那么这一类多余的字符会被忽略（这种行为对于解析函数之类的对象名称有用）。注意这个函数不会截断超长标识符。如果想要进行截断，可以把结果转换成name[]。
postgres=# select parse_ident('"SomeSchema".someTable');
parse_ident
------------------------
{SomeSchema,sometable}
(1 row)
postgres=# select parse_ident('SomeSchema.someTable');
parse_ident
------------------------
{someschema,sometable}
(1 row)
postgres=# select parse_ident('"SomeSchema"."someTable"');
parse_ident
------------------------
{SomeSchema,someTable}
(1 row)
postgres=# select parse_ident('"SomeSchema"."someTable"',false);
parse_ident
------------------------
{SomeSchema,someTable}
(1 row)
postgres=# select parse_ident('"SomeSchema"."someTable"hhh',false);
parse_ident
------------------------
{SomeSchema,someTable}
(1 row)
postgres=# select parse_ident('"SomeSchema"."someTable"hhh');
2021-01-16 15:24:27.582 CST [44328] ERROR: string is not a valid identifier: ""SomeSchema"."someTable"hhh"
2021-01-16 15:24:27.582 CST [44328] STATEMENT: select parse_ident('"SomeSchema"."someTable"hhh');
ERROR: string is not a valid identifier: ""SomeSchema"."someTable"hhh"
postgres=#
PG_CLIENT_ENCODING
其函数语法格式如下：
# pg_client_encoding()
postgres=# \df+ pg_client_encoding
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------+-----------------------------------
pg_catalog | pg_client_encoding | name | | func | stable | safe | uxdb | invoker | | internal | pg_client_encoding | encoding name of current database
(1 row)
postgres=#
函数功能：当前的客户端编码名字
postgres=# select pg_client_encoding();
pg_client_encoding
--------------------
UTF8
(1 row)
postgres=#
QUOTE_IDENT
其函数语法格式如下：
# quote_ident(string text)
postgres=# \df+ QUOTE_IDENT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------------------------------------
pg_catalog | quote_ident | text | text | func | immutable | safe | uxdb | invoker | | internal | quote_ident | quote an identifier for usage in a querystring
(1 row)
postgres=#
函数功能：将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个标识符。只有需要时才会加上引号（即，如果字符串包含非标识符字符或可能是大小写折叠的）。嵌入的引号会被正确地双写
postgres=# select quote_ident('Foo bar');
quote_ident
-------------
"Foo bar"
(1 row)
postgres=# select quote_ident('"Foo bar"');
quote_ident
---------------
"""Foo bar"""
(1 row)
postgres=#
QUOTE_LITERAL
其函数语法格式如下：
# quote_literal(string text)
# quote_literal(value anyelement)
postgres=# \df+ QUOTE_LITERAL
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------------------------------------+-----------------------------------------------
pg_catalog | quote_literal | text | anyelement | func | stable | safe | uxdb | invoker | | sql | select pg_catalog.quote_literal($1::pg_catalog.text) | quote a data value for usage in a querystring
pg_catalog | quote_literal | text | text | func | immutable | safe | uxdb | invoker | | internal | quote_literal | quote a literal for usage in a querystring
(2 rows)
postgres=#
函数功能1：将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字。嵌入的引号会被正确地双写。注意quote_literal对空输入返回空；如果参数可能为空，quote_nullable通常更合适
postgres=# select quote_literal(E'O\'Reilly');
quote_literal
---------------
'O''Reilly'
(1 row)
postgres=#
函数功能2：强迫给定值为文本并且接着将它用引号包围作为一个文本。嵌入的单引号和反斜线被正确的双写
postgres=# select quote_literal(42.5);
quote_literal
---------------
'42.5'
(1 row)
postgres=# select quote_literal('"""');
quote_literal
---------------
'"""'
(1 row)
postgres=# select quote_literal('"');
quote_literal
---------------
'"'
(1 row)
postgres=#
QUOTE_NULLABLE
其函数语法格式如下：
# quote_nullable(string text)
# quote_nullable(value anyelement)
postgres=# \df+ QUOTE_NULLABLE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------------------------------+-------------------------------------------------------------
pg_catalog | quote_nullable | text | anyelement | func | stable | safe | uxdb | invoker | | sql | select pg_catalog.quote_nullable($1::pg_catalog.text) | quote a possibly-null data value for usage in a querystring
pg_catalog | quote_nullable | text | text | func | immutable | safe | uxdb | invoker | | internal | quote_nullable | quote a possibly-null literal for usage in a querystring
(2 rows)
postgres=#
函数功能：
- 将给定字符串返回成合适的引用形式，使它可以在一个SQL语句字符串中被用作一个字符串文字；或者，如果参数为空，返回NULL。嵌入的引号会被正确地双写
- 强迫给定值为文本并且接着将它用引号包围作为一个文本；或者，如果参数为空，返回NULL。嵌入的单引号和反斜线被正确的双写
postgres=# select quote_nullable(NULL);
quote_nullable
----------------
NULL
(1 row)
postgres=# select quote_nullable(42.5);
quote_nullable
----------------
'42.5'
(1 row)
postgres=# select quote_nullable(E'O\'Reilly');
quote_nullable
----------------
'O''Reilly'
(1 row)
postgres=#
REGEXP_MATCH
其函数语法格式如下：
# regexp_match(string text, pattern text [, flags text])
postgres=# \df+ REGEXP_MATCH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------+-----------------------------
pg_catalog | regexp_match | text[] | text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_match_no_flags | find first match for regexp
pg_catalog | regexp_match | text[] | text, text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_match | find first match for regexp
(2 rows)
postgres=#
函数功能：返回一个POSIX正则表达式与string的第一个匹配得到的子串
postgres=# select regexp_match('foobarbequebaz', '(bar)(beque)');
regexp_match
--------------
{bar,beque}
(1 row)
postgres=# select regexp_match('foobarbequebaz', '(bar)');
regexp_match
--------------
{bar}
(1 row)
postgres=# select regexp_match('foobarbequebaz', '(barm)');
regexp_match
--------------
(1 row)
postgres=# select regexp_match('foobarbequebaz', '(ba)');
regexp_match
--------------
{ba}
(1 row)
postgres=# select regexp_match('foobarbequebaz', '(beque)(bar)');
regexp_match
--------------
(1 row)
postgres=#
REGEXP_MATCHES
其函数语法格式如下：
# regexp_matches(string text, pattern text [, flags text])
postgres=# \df+ regexp_matches
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------+---------------------------
pg_catalog | regexp_matches | SETOF text[] | text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_matches_no_flags | find match(es) for regexp
pg_catalog | regexp_matches | SETOF text[] | text, text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_matches | find match(es) for regexp
(2 rows)
postgres=#
函数功能：返回一个POSIX正则表达式与string匹配得到的子串
postgres=# select regexp_matches('foobarbequebaz', 'ba.', 'g');
regexp_matches
----------------
{bar}
{baz}
(2 rows)
postgres=# select regexp_matches('foobarbequebaz', 'ba.');
regexp_matches
----------------
{bar}
(1 row)
postgres=#
REGEXP_REPLACE
其函数语法格式如下：
# regexp_replace(string text, pattern text, replacement text [, flags text])
postgres=# \df+ REGEXP_REPLACE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------------+------------------+------------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------+---------------------------
pg_catalog | regexp_replace | text | text, text, text | func | immutable | safe | uxdb | invoker | | internal | textregexreplace_noopt | replace text using regexp
pg_catalog | regexp_replace | text | text, text, text, text | func | immutable | safe | uxdb | invoker | | internal | textregexreplace | replace text using regexp
(2 rows)
postgres=#
函数功能：替换匹配一个POSIX正则表达式的子串
postgres=# select regexp_replace('Thomas', '.[mN]a.', 'M');
regexp_replace
----------------
ThM
(1 row)
postgres=# select regexp_replace('Thomas', '.[MN]a.', 'M');
regexp_replace
----------------
Thomas
(1 row)
postgres=# select regexp_replace('Thomas', '.[mN].', 'M');
regexp_replace
----------------
ThMs
(1 row)
postgres=#
REGEXP_SPLIT_TO_ARRAY
其函数语法格式如下：
# regexp_split_to_array(string text, pattern text [, flags text ])
postgres=# \df+ REGEXP_SPLIT_TO_ARRAY
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------------------+-------------------------
pg_catalog | regexp_split_to_array | text[] | text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_split_to_array_no_flags | split string by pattern
pg_catalog | regexp_split_to_array | text[] | text, text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_split_to_array | split string by pattern
(2 rows)
postgres=#
函数功能：使用一个POSIX正则表达式作为分隔符划分string
postgres=# select regexp_split_to_array('hello world', '\s+');
regexp_split_to_array
-----------------------
{hello,world}
(1 row)
postgres=#
REGEXP_SPLIT_TO_TABLE
其函数语法格式如下：
# regexp_split_to_table(string text, pattern text [, flags text])
postgres=# \df+ REGEXP_SPLIT_TO_TABLE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------------------+-------------------------
pg_catalog | regexp_split_to_table | SETOF text | text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_split_to_table_no_flags | split string by pattern
pg_catalog | regexp_split_to_table | SETOF text | text, text, text | func | immutable | safe | uxdb | invoker | | internal | regexp_split_to_table | split string by pattern
(2 rows)
postgres=#
函数功能：使用一个POSIX正则表达式作为分隔符划分string
postgres=# select regexp_split_to_table('hello world', '\s+');
regexp_split_to_table
-----------------------
hello
world
(2 rows)
postgres=#
REPEAT
其函数语法格式如下：
# repeat(string text, number int)
postgres=# \df+ REPEAT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------
pg_catalog | repeat | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | repeat | replicate string n times
(1 row)
postgres=#
函数功能：重复string指定的number次
postgres=# select repeat('Pg', 4);
repeat
----------
PgPgPgPg
(1 row)
postgres=#
REPLACE
其函数语法格式如下：
# replace(string text, from text, to text)
postgres=# \df+ REPLACE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+-----------------------------------------------------------------
pg_catalog | replace | text | text, text, text | func | immutable | safe | uxdb | invoker | | internal | replace_text | replace all occurrences in string of old_substr with new_substr
(1 row)
postgres=#
函数功能：将string中出现的所有子串from替换为子串to
postgres=# select replace('abcdefabcdef', 'cd', 'XX');
replace
--------------
abXXefabXXef
(1 row)
postgres=#
REVERSE
其函数语法格式如下：
# reverse(str)
postgres=# \df+ reverse
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+--------------
pg_catalog | reverse | text | text | func | immutable | safe | uxdb | invoker | | internal | text_reverse | reverse text
(1 row)
postgres=#
函数功能：返回反转的字符串
postgres=# select reverse('abcde');
reverse
---------
edcba
(1 row)
postgres=#
RIGHT
其函数语法格式如下：
# right(str text, n int)
postgres=# \df+ right
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------------------------
pg_catalog | right | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | text_right | extract the last n characters
(1 row)
postgres=#
函数功能：返回字符串中的最后n个字符。如果n为负，返回除最前面的|n|个字符外的所有字符
postgres=# select right('abcde', 2);
right
-------
de
(1 row)
postgres=# select right('abcde', -2);
right
-------
cde
(1 row)
postgres=#
SPLIT_PART
其函数语法格式如下：
# split_part(string text, delimiter text, field int)
postgres=# \df+ split_part
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------------------------------------
pg_catalog | split_part | text | text, text, integer | func | immutable | safe | uxdb | invoker | | internal | split_text | split string by field_sep and return field_num
(1 row)
postgres=#
函数功能：按delimiter划分string并返回给定域（从1开始计算）
postgres=# select split_part('abc~@~def~@~ghi', '~@~', 2);
split_part
------------
def
(1 row)
postgres=# select split_part('abc~@~def~@~ghi', '~@~', 1);
split_part
------------
abc
(1 row)
postgres=#
STRPOS
其函数语法格式如下：
# strpos(string, substring)
postgres=# \df+ STRPOS
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-----------------------
pg_catalog | strpos | integer | text, text | func | immutable | safe | uxdb | invoker | | internal | textpos | position of substring
(1 row)
postgres=#
函数功能：指定子串的位置（和position(substring in string)相同，但是注意相反的参数顺序）
postgres=# select strpos('high', 'ig');
strpos
--------
2
(1 row)
postgres=# select strpos('high', 'im');
strpos
--------
0
(1 row)
postgres=#
POSITION
其函数语法格式如下：
# position(substring in string)
postgres=# \df+ position
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------
pg_catalog | position | integer | bit, bit | func | immutable | safe | uxdb | invoker | | internal | bitposition | position of sub-bitstring
pg_catalog | position | integer | bytea, bytea | func | immutable | safe | uxdb | invoker | | internal | byteapos | position of substring
pg_catalog | position | integer | text, text | func | immutable | safe | uxdb | invoker | | internal | textpos | position of substring
(3 rows)
postgres=#
函数功能：定位指定子串
# 序列从1开始
postgres=# select position('om' in 'Thomas');
position
----------
3
(1 row)
postgres=# select position('im' in 'Thomas');
position
----------
0
(1 row)
postgres=#
STARTS_WITH
其函数语法格式如下：
# starts_with(string, prefix)
postgres=# \df+ STARTS_WITH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+------------------+-------------------------------
pg_catalog | starts_with | boolean | text, text | func | immutable | safe | uxdb | invoker | | internal | text_starts_with | implementation of ^@ operator
(1 row)
postgres=#
函数功能：如果string以prefix开始则返回真
postgres=# select starts_with('alphabet', 'alph');
starts_with
-------------
t
(1 row)
postgres=#
TO_ASCII
其函数语法格式如下：
# to_ascii(string text [, encoding text])
postgres=# \df+ TO_ASCII
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+------------------+--------------------------------------------
pg_catalog | to_ascii | text | text | func | immutable | safe | uxdb | invoker | | internal | to_ascii_default | encode text from DB encoding to ASCII text
pg_catalog | to_ascii | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | to_ascii_enc | encode text from encoding to ASCII text
pg_catalog | to_ascii | text | text, name | func | immutable | safe | uxdb | invoker | | internal | to_ascii_encname | encode text from encoding to ASCII text
(3 rows)
postgres=#
函数功能：将string从另一个编码转换到ASCII（只支持从LATIN1、LATIN2、LATIN9和WIN1250编码的转换）
TO_HEX
其函数语法格式如下：
# to_hex(number int or bigint)
postgres=# \df+ to_hex
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------------
pg_catalog | to_hex | text | bigint | func | immutable | safe | uxdb | invoker | | internal | to_hex64 | convert int8 number to hex
pg_catalog | to_hex | text | integer | func | immutable | safe | uxdb | invoker | | internal | to_hex32 | convert int4 number to hex
(2 rows)
postgres=#
函数功能：将number转换到它等效的十六进制表示
postgres=# select to_hex(10);
to_hex
--------
a
(1 row)
postgres=#
RPAD
其函数语法格式如下：
# rpad(string text, length int [, fill text])
postgres=# \df+ rpad
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------------------------------+----------------------------
pg_catalog | rpad | text | text, integer | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.rpad($1, $2, ' ') | right-pad string to length
pg_catalog | rpad | text | text, integer, text | func | immutable | safe | uxdb | invoker | | internal | rpad | right-pad string to length
(2 rows)
postgres=#
函数功能：将string通过增加字符fill（默认为一个空格）填充到长度length。如果string已经长于length则它会被截断
postgres=# select rpad('hi', 5, 'xy');
rpad
-------
hixyx
(1 row)
postgres=# select rpad('hi', 4, 'xy');
rpad
------
hixy
(1 row)
postgres=# select rpad('hi', 6, 'xy');
rpad
--------
hixyxy
(1 row)
postgres=# select rpad('hi', 7, 'xy');
rpad
---------
hixyxyx
(1 row)
postgres=# select rpad('hi', 3, 'xy');
rpad
------
hix
(1 row)
postgres=#
RTRIM
其函数语法格式如下：
# rtrim(string text [, characters text])
postgres=# \df+ rtrim
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------------------------------
pg_catalog | rtrim | text | text | func | immutable | safe | uxdb | invoker | | internal | rtrim1 | trim spaces from right end of string
pg_catalog | rtrim | text | text, text | func | immutable | safe | uxdb | invoker | | internal | rtrim | trim selected characters from right end of string
(2 rows)
postgres=#
函数功能：从string的结尾删除最长的只包含characters（默认是一个空格）的串
postgres=# select rtrim('testxxzx', 'xyz');
rtrim
-------
test
(1 row)
postgres=#
SUBSTRING
其函数语法格式如下：
postgres=# \df+ substring
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+-------------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------------------------------------------------------+----------------------------------------------
pg_catalog | substring | bit | bit, integer | func | immutable | safe | uxdb | invoker | | internal | bitsubstr_no_len | extract portion of bitstring
pg_catalog | substring | bit | bit, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bitsubstr | extract portion of bitstring
pg_catalog | substring | bytea | bytea, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr_no_len | extract portion of string
pg_catalog | substring | bytea | bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr | extract portion of string
pg_catalog | substring | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr_no_len | extract portion of string
pg_catalog | substring | text | text, integer, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr | extract portion of string
pg_catalog | substring | text | text, text | func | immutable | safe | uxdb | invoker | | internal | textregexsubstr | extract text matching regular expression
pg_catalog | substring | text | text, text, text | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.substring($1, pg_catalog.similar_to_escape($2, $3)) | extract text matching SQL regular expression
(8 rows)
postgres=#
函数功能1：提取子串
# substring(string [from int] [for int])
postgres=# select substring('Thomas' from 2 for 3);
substring
-----------
hom
(1 row)
postgres=#
函数功能2：提取匹配POSIX正则表达式的子串
# substring(string from pattern)
postgres=# select substring('Thomas' from '...$');
substring
-----------
mas
(1 row)
postgres=# select substring('Thomas' from '..$');
substring
-----------
as
(1 row)
postgres=# select substring('Thomas' from '....$');
substring
-----------
omas
(1 row)
postgres=#
函数功能3：提取匹配SQL正则表达式的子串
# substring(string from pattern for escape)
postgres=# select substring('Thomas' from '%#"o_a#"_' for '#');
substring
-----------
oma
(1 row)
postgres=#
函数功能：提取子串（与substring(string from from for count)相同）
postgres=# select substr('alphabet', 3, 2);
substr
--------
ph
(1 row)
postgres=# select substr('alphabet', 3, 10);
substr
--------
phabet
(1 row)
postgres=#
SUBSTR
其函数语法格式如下：
# substr(string, from [, count])
postgres=# \df+ substr
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+-------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------+---------------------------
pg_catalog | substr | bytea | bytea, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr_no_len | extract portion of string
pg_catalog | substr | bytea | bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr | extract portion of string
pg_catalog | substr | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr_no_len | extract portion of string
pg_catalog | substr | text | text, integer, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr | extract portion of string
(4 rows)
postgres=#
TRANSLATE
其函数语法格式如下：
# translate(string text, from text, to text)
postgres=# \df+ translate
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------------------------
pg_catalog | translate | text | text, text, text | func | immutable | safe | uxdb | invoker | | internal | translate | map a set of characters appearing in string
(1 row)
postgres=#
函数功能：string中任何匹配from集合中一个字符的字符会被替换成to集合中的相应字符。如果from比to长，from中的额外字符会被删除
postgres=# select translate('12345', '143', 'ax');
translate
-----------
a2x5
(1 row)
postgres=#
TRIM
其函数语法格式如下：
trim([leading | trailing | both] [characters] from string)
trim([leading | trailing | both] [from] string [, characters] )
函数功能1：从string的开头、结尾或者两端（both是默认值）移除只包含characters（默认是一个空格）中字符的最长字符串
postgres=# select trim(both 'xyz' from 'yxTomxx');
btrim
-------
Tom
(1 row)
postgres=#
函数功能2：trim()的非标准版本
postgres=# select trim(both from 'xTomxx', 'x');
btrim
-------
Tom
(1 row)
postgres=#
UPPER
其函数语法格式如下：
postgres=# \df+ upper
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------------
pg_catalog | upper | anyelement | anyrange | func | immutable | safe | uxdb | invoker | | internal | range_upper | upper bound of range
pg_catalog | upper | text | text | func | immutable | safe | uxdb | invoker | | internal | upper | uppercase
(2 rows)
postgres=#
函数功能：将字符串转换成大写形式
postgres=# select upper('tom');
upper
-------
TOM
(1 row)
postgres=# select upper('tomN1');
upper
-------
TOMN1
(1 row)
postgres=# select upper('231');
upper
-------
231
(1 row)
postgres=#
FORMAT
函数format根据一个格式字符串产生格式化的输出，其形式类似于 C 函数sprintf，其语法格式如下：
format(formatstr text [, formatarg "any" [, ...] ])
- formatstr是一个格式字符串，它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中，除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色，它定义后续的函数参数如何被格式化及插入到结果中
- 每一个formatarg参数会被根据其数据类型的常规输出规则转换为文本，并接着根据格式说明符被格式化和插入到结果字符串中
而上面所说的 格式说明符是由一个%字符开始并且有这样的形式：
%[position][flags][width]type
解释一下上面这几个部分：
-
position（可选）
一个形式为n$的字符串，其中n是要打印的参数的索引。索引 1 表示formatstr之后的第一个参数。如果position被忽略，默认会使用序列中的下一个参数 -
flags（可选）
控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号（-），它将导致格式说明符的输出会被左对齐（left-justified）。除非width域也被指定，否者这个域不会产生任何效果 -
width（可选）
指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部（取决于-标志）用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断，但是会被简单地忽略。宽度可以使用下列形式之一指定：一个正整数；一个星号（*）表示使用下一个函数参数作为宽度；或者一个形式为*n$的字符串表示使用第n个函数参数作为宽度。 如果宽度来自于一个函数参数，则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值，结果会在长度为abs(width)的域中被左对齐（如果-标志被指定）。 -
type（必需）
格式转换的类型，用于产生格式说明符的输出。支持下面的类型：
- s将参数值格式化为一个简单字符串。一个控制被视为一个空字符串
- I将参数值视作 SQL 标识符，并在必要时用双写引号包围它。如果参数为空，将会是一个错误（等效于quote_ident）
- L将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串NULL（等效于quote_nullable）
除了以上所述的格式说明符之外，要输出一个文字形式的%字符，可以使用特殊序列%%，如下为使用实例：
postgres=# \df+ format
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+----------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+---------------------
pg_catalog | format | text | text | func | stable | safe | uxdb | invoker | | internal | text_format_nv | format text message
pg_catalog | format | text | text, VARIADIC "any" | func | stable | safe | uxdb | invoker | | internal | text_format | format text message
(2 rows)
postgres=# SELECT format('Hello %s', 'World');;
format
-------------
Hello World
(1 row)
postgres=# SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
format
----------------------------
Testing one, two, three, %
(1 row)
postgres=# SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
format
-------------------------------------------
INSERT INTO "Foo bar" VALUES('O''Reilly')
(1 row)
postgres=# SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
format
----------------------------------------------------
INSERT INTO locations VALUES(E'C:\\Program Files')
(1 row)
postgres=#
postgres=# SELECT format('|%10s|', 'foo');
format
--------------
| foo|
(1 row)
postgres=# SELECT format('|%-10s|', 'foo');
format
--------------
|foo |
(1 row)
postgres=# SELECT format('|%*s|', 10, 'foo');
format
--------------
| foo|
(1 row)
postgres=# SELECT format('|%-*s|', 10, 'foo');
format
--------------
|foo |
(1 row)
postgres=# SELECT format('|%-*s|', -10, 'foo');
format
--------------
|foo |
(1 row)
postgres=#
postgres=# SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
format
-------------------------
Testing three, two, one
(1 row)
postgres=# SELECT format('|%*2$s|', 'foo', 10, 'bar');
format
--------------
| bar|
(1 row)
postgres=# SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
format
--------------
| foo|
(1 row)
postgres=# SELECT format('|%1$*2$s%3$s|', 'foo', 10, 'bar');
format
-----------------
| foobar|
(1 row)
postgres=#
# 不同于标准的 C 函数sprintf,format函数允许将带有或者不带有position域的格式说明符被混在同一个格式字符串中
# 一个不带有position域的格式说明符总是使用最后一个被消耗的参数的下一个参数
# 另外，format函数不要求所有函数参数都被用在格式字符串中
postgres=# SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
format
---------------------------
Testing three, two, three
(1 row)
postgres=#
二进制串函数
OCTET_LENGTH
其函数语法格式如下：
# octet_length(string)
postgres=# \df+ OCTET_LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+--------------
pg_catalog | octet_length | integer | bit | func | immutable | safe | uxdb | invoker | | internal | bitoctetlength | octet length
pg_catalog | octet_length | integer | bytea | func | immutable | safe | uxdb | invoker | | internal | byteaoctetlen | octet length
pg_catalog | octet_length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharoctetlen | octet length
pg_catalog | octet_length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textoctetlen | octet length
(4 rows)
postgres=#
函数功能：二进制串中的字节数
postgres=# select octet_length('jo\000se'::bytea);
octet_length
--------------
5
(1 row)
postgres=# select octet_length('jo\000se'::text);
octet_length
--------------
8
(1 row)
postgres=#
OVERLAY
其函数语法格式如下：
# overlay(string placing string from int [for int])
postgres=# \df+ OVERLAY
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+--------------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------+---------------------------------
pg_catalog | overlay | bit | bit, bit, integer | func | immutable | safe | uxdb | invoker | | internal | bitoverlay_no_len | substitute portion of bitstring
pg_catalog | overlay | bit | bit, bit, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bitoverlay | substitute portion of bitstring
pg_catalog | overlay | bytea | bytea, bytea, integer | func | immutable | safe | uxdb | invoker | | internal | byteaoverlay_no_len | substitute portion of string
pg_catalog | overlay | bytea | bytea, bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | byteaoverlay | substitute portion of string
pg_catalog | overlay | text | text, text, integer | func | immutable | safe | uxdb | invoker | | internal | textoverlay_no_len | substitute portion of string
pg_catalog | overlay | text | text, text, integer, integer | func | immutable | safe | uxdb | invoker | | internal | textoverlay | substitute portion of string
(6 rows)
postgres=#
函数功能：替换子串
postgres=# select overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3);
overlay
----------------
\x5402036d6173
(1 row)
postgres=# SET bytea_output = 'escape';
SET
postgres=# select overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3);
overlay
--------------
T\002\003mas
(1 row)
postgres=#
POSITION
其函数语法格式如下：
# position(substring in string)
postgres=# \df+ POSITION
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------
pg_catalog | position | integer | bit, bit | func | immutable | safe | uxdb | invoker | | internal | bitposition | position of sub-bitstring
pg_catalog | position | integer | bytea, bytea | func | immutable | safe | uxdb | invoker | | internal | byteapos | position of substring
pg_catalog | position | integer | text, text | func | immutable | safe | uxdb | invoker | | internal | textpos | position of substring
(3 rows)
postgres=#
函数功能：指定子串的位置
postgres=# select position('\000om'::bytea in 'Th\000omas'::bytea);
position
----------
3
(1 row)
postgres=# select position('\000omb'::bytea in 'Th\000omas'::bytea);
position
----------
0
(1 row)
postgres=#
SUBSTRING
其函数语法格式如下：
# substring(string [from int] [for int])
postgres=# \df+ SUBSTRING
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+-------------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------------------------------------------------------+----------------------------------------------
pg_catalog | substring | bit | bit, integer | func | immutable | safe | uxdb | invoker | | internal | bitsubstr_no_len | extract portion of bitstring
pg_catalog | substring | bit | bit, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bitsubstr | extract portion of bitstring
pg_catalog | substring | bytea | bytea, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr_no_len | extract portion of string
pg_catalog | substring | bytea | bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bytea_substr | extract portion of string
pg_catalog | substring | text | text, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr_no_len | extract portion of string
pg_catalog | substring | text | text, integer, integer | func | immutable | safe | uxdb | invoker | | internal | text_substr | extract portion of string
pg_catalog | substring | text | text, text | func | immutable | safe | uxdb | invoker | | internal | textregexsubstr | extract text matching regular expression
pg_catalog | substring | text | text, text, text | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.substring($1, pg_catalog.similar_to_escape($2, $3)) | extract text matching SQL regular expression
(8 rows)
postgres=#
函数功能：提取子串
postgres=# select substring('Th\000omas'::bytea from 2 for 3);
substring
-----------
h\000o
(1 row)
postgres=# select substring('Th\000omas'::text from 2 for 3);
substring
-----------
h\0
(1 row)
postgres=#
TRIM
其函数语法格式如下：
# trim([both] bytes from string)
函数功能：从string的开头或结尾删除只包含出现在bytes中字节的最长串
postgres=# select trim('\000\001'::bytea from '\000Tom\001'::bytea);
btrim
-------
Tom
(1 row)
postgres=# select trim('\000\002'::bytea from '\000Tom\001'::bytea);
btrim
---------
Tom\001
(1 row)
postgres=# select trim('abc'::text from 'aTom\001cb');
btrim
---------
Tom\001
(1 row)
postgres=#
BTRIM
其函数语法格式如下：
# btrim(string bytea, bytes bytea)
postgres=# \df+ BTRIM
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+---------------------------------------------------
pg_catalog | btrim | bytea | bytea, bytea | func | immutable | safe | uxdb | invoker | | internal | byteatrim | trim both ends of string
pg_catalog | btrim | text | text | func | immutable | safe | uxdb | invoker | | internal | btrim1 | trim spaces from both ends of string
pg_catalog | btrim | text | text, text | func | immutable | safe | uxdb | invoker | | internal | btrim | trim selected characters from both ends of string
(3 rows)
postgres=#
函数功能：从string的开头或结尾删除只由出现在bytes中字节组成的最长串
postgres=# select btrim('\000trim\001'::bytea, '\000\001'::bytea);
btrim
-------
trim
(1 row)
postgres=#
DECODE
其函数语法格式如下：
# decode(string text, format text)
postgres=# \df+ DECODE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+----------------------------------------------------
pg_catalog | decode | bytea | text, text | func | immutable | safe | uxdb | invoker | | internal | binary_decode | convert ascii-encoded text string into bytea value
(1 row)
postgres=#
函数功能：从string中的文本表示解码二进制数据。format的参数和在encode中一样
postgres=# select decode('123\000456', 'escape');
decode
------------
123\000456
(1 row)
postgres=#
ENCODE
其函数语法格式如下：
# encode(data bytea, format text)
postgres=# \df+ ENCODE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+------------------------------------------------------
pg_catalog | encode | text | bytea, text | func | immutable | safe | uxdb | invoker | | internal | binary_encode | convert bytea value into some ascii-only text string
(1 row)
postgres=#
函数功能：将二进制数据编码为一个文本表示。支持的格式有：base64、hex、escape。其中escape将零字节和高位组字节转换为八进制序列（\nnn）和双反斜线
postgres=# select encode('123\000456'::bytea, 'escape');
encode
------------
123\000456
(1 row)
postgres=# select encode('123\000456'::bytea, 'base64');
encode
--------------
MTIzADQ1Ng==
(1 row)
postgres=# select encode('123\000456'::bytea, 'hex');
encode
----------------
31323300343536
(1 row)
postgres=#
GET_BIT
其函数语法格式如下：
# get_bit(string, offset)
postgres=# \df+ GET_BIT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | get_bit | integer | bit, integer | func | immutable | safe | uxdb | invoker | | internal | bitgetbit | get bit
pg_catalog | get_bit | integer | bytea, bigint | func | immutable | safe | uxdb | invoker | | internal | byteaGetBit | get bit
(2 rows)
postgres=#
函数功能：从串中抽取位
postgres=# select get_bit('Th\000omas'::bytea, 45);
get_bit
---------
1
(1 row)
postgres=#
解释一下：(阅读源码byteaGetBit函数)
1、
'Th\000omas'::bytea长度为7 共56字节 为0-55
2、45 经过与8的 相除和取余 都是5
3、取得 下标为5的字符 就是a 其二进制为 01100001
4、经过1的左移 5位，和 a 的二进制相&
5、返回值为1
# m ASCII 为109;二进制 01101101
postgres=# select get_bit('Th\000omas'::bytea, 6);
get_bit
---------
1
(1 row)
postgres=#
GET_BYTE
其函数语法格式如下：
# get_byte(string, offset)
postgres=# \df+ GET_BYTE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+-------------
pg_catalog | get_byte | integer | bytea, integer | func | immutable | safe | uxdb | invoker | | internal | byteaGetByte | get byte
(1 row)
postgres=#
函数功能：从串中抽取字节
postgres=# select get_byte('Th\000omas'::bytea, 4);
get_byte
----------
109
(1 row)
postgres=# select get_byte('Th\000omas'::bytea, 5);
get_byte
----------
97
(1 row)
postgres=#
LENGTH
其函数语法格式如下：
# length(string)
postgres=# \df+ LENGTH
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------+----------------------------------------
pg_catalog | length | integer | bit | func | immutable | safe | uxdb | invoker | | internal | bitlength | bitstring length
pg_catalog | length | integer | bytea | func | immutable | safe | uxdb | invoker | | internal | byteaoctetlen | octet length
pg_catalog | length | integer | bytea, name | func | stable | safe | uxdb | invoker | | internal | length_in_encoding | length of string in specified encoding
pg_catalog | length | integer | character | func | immutable | safe | uxdb | invoker | | internal | bpcharlen | character length
pg_catalog | length | double precision | lseg | func | immutable | safe | uxdb | invoker | | internal | lseg_length | distance between endpoints
pg_catalog | length | double precision | path | func | immutable | safe | uxdb | invoker | | internal | path_length | sum of path segments
pg_catalog | length | integer | text | func | immutable | safe | uxdb | invoker | | internal | textlen | length
pg_catalog | length | integer | tsvector | func | immutable | safe | uxdb | invoker | | internal | tsvector_length | number of lexemes
(8 rows)
postgres=#
函数功能：二进制串的长度
postgres=# select length('jo\000se'::bytea);
length
--------
5
(1 row)
postgres=# select length('jo\000se'::text);
length
--------
8
(1 row)
postgres=#
MD5
其函数语法格式如下：
# md5(string)
postgres=# \df+ md5
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | md5 | text | bytea | func | immutable | safe | uxdb | invoker | | internal | md5_bytea | MD5 hash
pg_catalog | md5 | text | text | func | immutable | safe | uxdb | invoker | | internal | md5_text | MD5 hash
(2 rows)
postgres=#
函数功能：计算string的MD5哈希码，以十六进制形式返回结果
postgres=# select md5('Th\000omas'::bytea);
md5
----------------------------------
8ab2d3c9689aaf18b4958c334c82d8b1
(1 row)
postgres=#
注：注意由于历史原因，函数md5返回的是一个十六进制编码的text值，而SHA-2函数返回类型bytea。可以使用函数encode和decode在两者之间转换，例如encode(sha256(‘abc’), ‘hex’)可以得到一个十六进制编码的文本表示
postgres=# SET bytea_output = 'hex';
SET
postgres=# select sha224('abc');
sha224
------------------------------------------------------------
\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
(1 row)
postgres=# select md5('Th\000omas'::bytea);
md5
----------------------------------
8ab2d3c9689aaf18b4958c334c82d8b1
(1 row)
postgres=# select md5('Th\000omas');
md5
----------------------------------
41bcc4d3e5d424e2177485e2c0c80698
(1 row)
postgres=# select encode(sha256('abc'), 'hex');
encode
------------------------------------------------------------------
ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
(1 row)
postgres=# select encode(sha224('abc'), 'hex');
encode
----------------------------------------------------------
23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
(1 row)
postgres=#
SET_BIT
其函数语法格式如下：
# set_bit(string, offset, newvalue)
postgres=# \df+ SET_BIT
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
pg_catalog | set_bit | bit | bit, integer, integer | func | immutable | safe | uxdb | invoker | | internal | bitsetbit | set bit
pg_catalog | set_bit | bytea | bytea, bigint, integer | func | immutable | safe | uxdb | invoker | | internal | byteaSetBit | set bit
(2 rows)
postgres=#
函数功能：设置串中的位
postgres=# select set_bit('Th\000omas'::bytea, 45, 0);
set_bit
------------
Th\000omAs
(1 row)
postgres=#
SET_BYTE
其函数语法格式如下：
# set_byte(string, offset, newvalue)
postgres=# \df+ SET_BYTE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+-------------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+-------------
pg_catalog | set_byte | bytea | bytea, integer, integer | func | immutable | safe | uxdb | invoker | | internal | byteaSetByte | set byte
(1 row)
postgres=#
函数功能：设置串中的字节
postgres=# select set_byte('Th\000omas'::bytea, 4, 97);
set_byte
------------
Th\000oaas
(1 row)
postgres=#
SHA224
其函数语法格式如下：
# sha224(bytea)
# sha256(bytea)
# sha384(bytea)
# sha512(bytea)
postgres=# \df+ SHA224
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------+--------------
pg_catalog | sha224 | bytea | bytea | func | immutable | safe | uxdb | invoker | | internal | sha224_bytea | SHA-224 hash
(1 row)
postgres=#
函数功能：SHA-224哈希
postgres=# select sha224('abc');
sha224
------------------------------------------------------------------------------------
#\011}"4\005\330"\206B\244w\275\242U\263*\255\274\344\275\240\263\367\343l\235\247
(1 row)
postgres=# SET bytea_output = 'hex';
SET
postgres=# select sha224('abc');
sha224
------------------------------------------------------------
\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
(1 row)
postgres=#
位串操作符示例如下：
# 连接
postgres=# select B'10001' || B'011' as bit;
bit
----------
10001011
(1 row)
# 按位与
postgres=# select B'10001' & B'01101' as bit;
bit
-------
00001
(1 row)
# 按位或
postgres=# select B'10001' | B'01101' as bit;
bit
-------
11101
(1 row)
# 按位异或
postgres=# select B'10001' # B'01101' as bit;
bit
-------
11100
(1 row)
# 按位求反
postgres=# select ~ B'10001' as bit;
bit
-------
01110
(1 row)
# 按位左移
postgres=# select B'10001' << 3;
?column?
----------
01000
(1 row)
# 按位右移
postgres=# select B'10001' >> 2 as bit;
bit
-------
00100
(1 row)
postgres=#
1、下面的SQL标准函数除了可以用于字符串之外，也可以用于位串：
length、 bit_length、 octet_length、 position、 substring、 overlay
2、下面的函数除了可以用于二进制串之外，也可以用于位串：get_bit、 set_bit。 当使用于一个位串时，这些函数将串的第一（最左）位计数为位 0
另外，我们可以在整数和bit之间来回转换。示例如下：
postgres=# select 44::bit(10);
bit
------------
0000101100
(1 row)
postgres=# select 44::bit(3);
bit
-----
100
(1 row)
postgres=# select 44::bit(5);
bit
-------
01100
(1 row)
postgres=# select cast(-44 as bit(12));
bit
--------------
111111010100
(1 row)
postgres=# select '1110'::bit(4)::integer;
int4
------
14
(1 row)
postgres=# select '1111'::bit(4)::integer;
int4
------
15
(1 row)
postgres=#
注：
postgres=# select 4::bit(3);
bit
-----
100
(1 row)
postgres=# select 4::bit(1);
bit
-----
0
(1 row)
postgres=# select 4::bit(5);
bit
-------
00100
(1 row)
postgres=#
1、如果只是转换为“bit”，意思是转换成bit(1)，因此只会转换整数的最低有效位
2、把一个整数转换成bit(n)将拷贝整数的最右边的n位。 把一个整数转换成比整数本身长的位串，就会在最左边扩展符号
时间函数
所有下文描述的接受time或timestamp输入的函数和操作符实际上都有两种变体：
1、 一种接收
time with time zone或timestamp with time zone
2、另外一种接受time without time zone或者timestamp without time zone
3、为了简化，这些变种没有被独立地展示
4、+和*操作符都是可交换的操作符对（例如：date + integer 和 integer + date） 我们只显示其中一个
日期/时间操作符 示例如下：
postgres=# select date '2001-09-28' + integer '7' as date;
date
------------
2001-10-05
(1 row)
postgres=# select date '2001-09-28' + interval '1 hour' as date;
date
---------------------
2001-09-28 01:00:00
(1 row)
postgres=# select date '2001-09-28' + time '03:00' as date;
date
---------------------
2001-09-28 03:00:00
(1 row)
postgres=# select interval '1 day' + interval '1 hour' as time;
time
----------------
1 day 01:00:00
(1 row)
postgres=# select timestamp '2001-09-28 01:00' + interval '23 hours' as time;
time
---------------------
2001-09-29 00:00:00
(1 row)
postgres=# select time '01:00' + interval '3 hours' as time;
time
----------
04:00:00
(1 row)
postgres=#
postgres=# select - interval '23 hours' as time;
time
-----------
-23:00:00
(1 row)
postgres=# select date '2001-10-01' - date '2001-09-28' as date;
date
------
3
(1 row)
postgres=# select date '2001-10-01' - integer '7' as date;
date
------------
2001-09-24
(1 row)
postgres=# select date '2001-09-28' - interval '1 hour' as date;
date
---------------------
2001-09-27 23:00:00
(1 row)
postgres=# select time '05:00' - time '03:00' as time;
time
----------
02:00:00
(1 row)
postgres=# select time '05:00' - interval '2 hours' as time;
time
----------
03:00:00
(1 row)
postgres=# select timestamp '2001-09-28 23:00' - interval '23 hours' as time;
time
---------------------
2001-09-28 00:00:00
(1 row)
postgres=# select interval '1 day' - interval '1 hour' as time;
time
-----------------
1 day -01:00:00
(1 row)
postgres=# select timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' as time;
time
----------------
1 day 15:00:00
(1 row)
postgres=#
postgres=# select 900 * interval '1 second' as time;
time
----------
00:15:00
(1 row)
postgres=# select 21 * interval '1 day' as time;
time
---------
21 days
(1 row)
postgres=# select double precision '3.5' * interval '1 hour' as time;
time
----------
03:30:00
(1 row)
postgres=# select interval '1 hour' / double precision '1.5' as time;
time
----------
00:40:00
(1 row)
postgres=#
AGE
其函数语法格式如下：
# age(timestamp, timestamp)
# age(timestamp)
postgres=# \df+ age
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+------------------+----------------------------------------------------------+------+------------+------------+-------+----------+-------------------+----------+------------------------------------------------------------------------------+---------------------------------------------------------------------
pg_catalog | age | interval | timestamp without time zone | func | stable | safe | uxdb | invoker | | sql | select pg_catalog.age(cast(current_date as timestamp without time zone), $1) | date difference from today preserving months and years
pg_catalog | age | interval | timestamp without time zone, timestamp without time zone | func | immutable | safe | uxdb | invoker | | internal | timestamp_age | date difference preserving months and years
pg_catalog | age | interval | timestamp with time zone | func | stable | safe | uxdb | invoker | | sql | select pg_catalog.age(cast(current_date as timestamp with time zone), $1) | date difference from today preserving months and years
pg_catalog | age | interval | timestamp with time zone, timestamp with time zone | func | immutable | safe | uxdb | invoker | | internal | timestamptz_age | date difference preserving months and years
pg_catalog | age | integer | xid | func | stable | restricted | uxdb | invoker | | internal | xid_age | age of a transaction ID, in transactions before current transaction
(5 rows)
postgres=#
函数功能1：减去参数，生成一个使用年、月（而不是只用日）的“符号化”的结果
postgres=# select age(timestamp '2001-04-10', timestamp '1957-06-13');
age
-------------------------
43 years 9 mons 27 days
(1 row)
postgres=#
函数功能2：从current_date（在午夜）减去
postgres=# select age(timestamp '1957-06-13');
age
------------------------
63 years 7 mons 7 days
(1 row)
postgres=#
CLOCK_TIMESTAMP
其函数语法格式如下：
# clock_timestamp()
postgres=# \df+ CLOCK_TIMESTAMP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------------+--------------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------+--------------------
pg_catalog | clock_timestamp | timestamp with time zone | | func | volatile | safe | uxdb | invoker | | internal | clock_timestamp | current clock time
(1 row)
postgres=#
函数功能：当前日期和时间（在语句执行期间变化）
postgres=# select clock_timestamp() ;
clock_timestamp
-------------------------------
2021-01-17 17:06:49.983823+08
(1 row)
postgres=#
postgres=# select clock_timestamp() ;
clock_timestamp
-------------------------------
2021-01-17 17:07:06.095322+08
(1 row)
postgres=#
CURRENT_DATE
其函数语法格式如下：
# current_date
函数功能：当前日期
postgres=# select current_date;
current_date
--------------
2021-01-17
(1 row)
postgres=#
CURRENT_TIME
其函数语法格式如下：
# current_time
函数功能：当前时间（一天中的时间）
postgres=# select current_time;
current_time
--------------------
17:13:58.985423+08
(1 row)
postgres=#
CURRENT_TIMESTAMP
其函数语法格式如下：
# current_timestamp
函数功能：当前日期和时间（当前事务开始时）
postgres=# select current_timestamp ;
current_timestamp
-------------------------------
2021-01-17 17:16:52.978209+08
(1 row)
postgres=#
DATE_PART
其函数语法格式如下：
# date_part(text, timestamp)
# date_part(text, interval)
postgres=# \df+ DATE_PART
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+-----------------------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------------------------------------------------------------+---------------------------------------------
pg_catalog | date_part | double precision | text, date | func | immutable | safe | uxdb | invoker | | sql | select pg_catalog.date_part($1, cast($2 as timestamp without time zone)) | extract field from date
pg_catalog | date_part | double precision | text, interval | func | immutable | safe | uxdb | invoker | | internal | interval_part | extract field from interval
pg_catalog | date_part | double precision | text, timestamp without time zone | func | immutable | safe | uxdb | invoker | | internal | timestamp_part | extract field from timestamp
pg_catalog | date_part | double precision | text, timestamp with time zone | func | stable | safe | uxdb | invoker | | internal | timestamptz_part | extract field from timestamp with time zone
pg_catalog | date_part | double precision | text, time without time zone | func | immutable | safe | uxdb | invoker | | internal | time_part | extract field from time
pg_catalog | date_part | double precision | text, time with time zone | func | immutable | safe | uxdb | invoker | | internal | timetz_part | extract field from time with time zone
(6 rows)
postgres=#
函数功能：获得子域
postgres=# select date_part('hour', timestamp '2001-02-16 20:38:40');
date_part
-----------
20
(1 row)
postgres=# select date_part('month', interval '2 years 3 months');
date_part
-----------
3
(1 row)
postgres=#
DATE_TRUNC
其函数语法格式如下：
# date_trunc(text, timestamp)
# date_trunc(text, timestamp with time zone, text)
# date_trunc(text, interval)
postgres=# \df+ DATE_TRUNC
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------+-----------------------------+--------------------------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------+-----------------------------------------------------------------------------
pg_catalog | date_trunc | interval | text, interval | func | immutable | safe | uxdb | invoker | | internal | interval_trunc | truncate interval to specified units
pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone | func | immutable | safe | uxdb | invoker | | internal | timestamp_trunc | truncate timestamp to specified units
pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone | func | stable | safe | uxdb | invoker | | internal | timestamptz_trunc | truncate timestamp with time zone to specified units
pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone, text | func | stable | safe | uxdb | invoker | | internal | timestamptz_trunc_zone | truncate timestamp with time zone to specified units in specified time zone
(4 rows)
postgres=#
函数功能：
# 截断到指定精度
postgres=# select date_trunc('hour', timestamp '2001-02-16 20:38:40');
date_trunc
---------------------
2001-02-16 20:00:00
(1 row)
postgres=#
# 在指定的时区截断到指定的精度
postgres=# select date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney');
date_trunc
------------------------
2001-02-16 21:00:00+08
(1 row)
postgres=#
# 截断到指定精度
postgres=# select date_trunc('hour', interval '2 days 3 hours 40 minutes');
date_trunc
-----------------
2 days 03:00:00
(1 row)
postgres=#
EXTRACT
其函数语法格式如下：
# extract(field from timestamp)
# extract(field from interval)
函数功能：获得子域
postgres=# select extract(hour from timestamp '2001-02-16 20:38:40');
date_part
-----------
20
(1 row)
postgres=# select extract(month from interval '2 years 3 months');
date_part
-----------
3
(1 row)
postgres=#
ISFINITE
其函数语法格式如下：
# isfinite(date)
# isfinite(timestamp)
# isfinite(interval)
postgres=# \df+ ISFINITE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------+------------------+-----------------------------+------+------------+----------+-------+----------+-------------------+----------+------------------+-------------------
pg_catalog | isfinite | boolean | date | func | immutable | safe | uxdb | invoker | | internal | date_finite | finite date?
pg_catalog | isfinite | boolean | interval | func | immutable | safe | uxdb | invoker | | internal | interval_finite | finite interval?
pg_catalog | isfinite | boolean | timestamp without time zone | func | immutable | safe | uxdb | invoker | | internal | timestamp_finite | finite timestamp?
pg_catalog | isfinite | boolean | timestamp with time zone | func | immutable | safe | uxdb | invoker | | internal | timestamp_finite | finite timestamp?
(4 rows)
postgres=#
函数功能：测试有限日期/有限时间戳/有限间隔
postgres=# select isfinite(date '2001-02-16');
isfinite
----------
t
(1 row)
postgres=# select isfinite(timestamp '2001-02-16 21:28:30');
isfinite
----------
t
(1 row)
postgres=# select isfinite(interval '4 hours');
isfinite
----------
t
(1 row)
postgres=#
TIMESTAMP
其函数语法格式如下：
postgres=# \df+ TIMESTAMP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+-----------------------------+--------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------+-----------------------------------------------
pg_catalog | timestamp | timestamp without time zone | date | func | immutable | safe | uxdb | invoker | | internal | date_timestamp | convert date to timestamp
pg_catalog | timestamp | timestamp without time zone | date, time without time zone | func | immutable | safe | uxdb | invoker | | internal | datetime_timestamp | convert date and time to timestamp
pg_catalog | timestamp | timestamp without time zone | timestamp without time zone, integer | func | immutable | safe | uxdb | invoker | | internal | timestamp_scale | adjust timestamp precision
pg_catalog | timestamp | timestamp without time zone | timestamp with time zone | func | stable | safe | uxdb | invoker | | internal | timestamptz_timestamp | convert timestamp with time zone to timestamp
(4 rows)
postgres=#
函数功能：把日期或日期时间等数据转化为时间戳
postgres=# select "timestamp" ('2021-01-22');
timestamp
---------------------
2021-01-22 00:00:00
(1 row)
postgres=# select now();
now
------------------------------
2021-01-22 11:29:56.24118+08
(1 row)
postgres=# select "timestamp" (now());
timestamp
----------------------------
2021-01-22 11:30:13.369368
(1 row)
postgres=# select "timestamp" (make_timestamp(2013, 7, 15, 8, 15, 23.59899978775));
timestamp
-------------------------
2013-07-15 08:15:23.599
(1 row)
postgres=# select "timestamp" (make_timestamp(2013, 7, 15, 8, 15, 23.59899978775),5);
timestamp
-------------------------
2013-07-15 08:15:23.599
(1 row)
postgres=# select "timestamp" (make_timestamp(2013, 7, 15, 8, 15, 23.59899978775),2);
timestamp
-----------------------
2013-07-15 08:15:23.6
(1 row)
postgres=# select "timestamp" (make_timestamptz(2013, 7, 15, 8, 15, 23.5));
timestamp
-----------------------
2013-07-15 08:15:23.5
(1 row)
postgres=#
这里的timestamp 是一个数据类型，需要加上""，如下：
postgres=# select "timestamp" (now());
timestamp
----------------------------
2021-01-22 11:34:37.252394
(1 row)
postgres=# select timestamp (now());
2021-01-22 11:34:42.972 CST [7040] ERROR: syntax error at or near "now" at character 19
2021-01-22 11:34:42.972 CST [7040] STATEMENT: select timestamp (now());
ERROR: syntax error at or near "now"
LINE 1: select timestamp (now());
^
postgres=#
postgres=# select pg_catalog.timestamp (now());
timestamp
----------------------------
2021-01-22 11:35:48.403458
(1 row)
postgres=# select now();
now
------------------------------
2021-01-22 11:36:55.79611+08
(1 row)
postgres=#
JUSTIFY_DAYS
其函数语法格式如下：
# justify_days(interval)
postgres=# \df+ justify_days
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------+------------------------------------------------
pg_catalog | justify_days | interval | interval | func | immutable | safe | uxdb | invoker | | internal | interval_justify_days | promote groups of 30 days to numbers of months
(1 row)
postgres=#
函数功能：调整间隔这样30天时间周期可以表示为月
postgres=# select justify_days(interval '35 days');
justify_days
--------------
1 mon 5 days
(1 row)
postgres=#
JUSTIFY_HOURS
其函数语法格式如下：
# justify_hours(interval)
postgres=# \df+ JUSTIFY_HOURS
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------+-----------------------------------------------
pg_catalog | justify_hours | interval | interval | func | immutable | safe | uxdb | invoker | | internal | interval_justify_hours | promote groups of 24 hours to numbers of days
(1 row)
postgres=#
函数功能：调整间隔这样24小时时间周期可以表示为日
postgres=# select justify_hours(interval '27 hours');
justify_hours
----------------
1 day 03:00:00
(1 row)
postgres=#
JUSTIFY_INTERVAL
其函数语法格式如下：
# justify_interval(interval)
postgres=# \df+ JUSTIFY_INTERVAL
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------------+--------------------------------------------------------------------------------------------------
pg_catalog | justify_interval | interval | interval | func | immutable | safe | uxdb | invoker | | internal | interval_justify_interval | promote groups of 24 hours to numbers of days and promote groups of 30 days to numbers of months
(1 row)
postgres=#
函数功能：使用justify_days和justify_hours调整间隔，使用额外的符号调整
postgres=# select justify_interval(interval '1 mon -1 hour');
justify_interval
------------------
29 days 23:00:00
(1 row)
postgres=#
LOCALTIME
其函数语法格式如下：
# localtime
函数功能：当前时间（一天中的时间）
postgres=# select localtime ;
localtime
-----------------
09:40:57.828851
(1 row)
postgres=#
LOCALTIMESTAMP
其函数语法格式如下：
# localtimestamp
函数功能：当前日期和时间（当前事务的开始）
postgres=# select localtimestamp ;
localtimestamp
----------------------------
2021-01-22 09:50:54.055013
(1 row)
postgres=# select localtimestamp ;
localtimestamp
---------------------------
2021-01-22 09:51:39.76843
(1 row)
postgres=#
MAKE_DATE
其函数语法格式如下：
# make_date(year int, month int, day int)
postgres=# \df+ MAKE_DATE
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------
pg_catalog | make_date | date | year integer, month integer, day integer | func | immutable | safe | uxdb | invoker | | internal | make_date | construct date
(1 row)
postgres=#
函数功能：从年、月、日域创建日期
postgres=# select make_date(2013, 7, 15);
make_date
------------
2013-07-15
(1 row)
postgres=# select make_date(2013, 7, 32);
2021-01-22 09:55:34.401 CST [7040] ERROR: date field value out of range: 2013-07-32
2021-01-22 09:55:34.401 CST [7040] STATEMENT: select make_date(2013, 7, 32);
ERROR: date field value out of range: 2013-07-32
postgres=#
MAKE_INTERVAL
其函数语法格式如下：
# make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)
postgres=# \df+ MAKE_INTERVAL
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+--------------------
pg_catalog | make_interval | interval | years integer DEFAULT 0, months integer DEFAULT 0, weeks integer DEFAULT 0, days integer DEFAULT 0, hours integer DEFAULT 0, mins integer DEFAULT 0, secs double precision DEFAULT 0.0 | func | immutable | safe | uxdb | invoker | | internal | make_interval | construct interval
(1 row)
postgres=#
函数功能：从年、月、周、日、时、分、秒域创建 interval
postgres=# select make_interval(days => 10);
make_interval
---------------
10 days
(1 row)
postgres=# select make_interval(years => 10);
make_interval
---------------
10 years
(1 row)
postgres=#
MAKE_TIME
其函数语法格式如下：
# make_time(hour int, min int, sec double precision)
postgres=# \df+ MAKE_TIME
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------------+-------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+----------------
pg_catalog | make_time | time without time zone | hour integer, min integer, sec double precision | func | immutable | safe | uxdb | invoker | | internal | make_time | construct time
(1 row)
postgres=#
函数功能：从时、分、秒域创建时间
postgres=# select make_time(8, 15, 23.5);
make_time
------------
08:15:23.5
(1 row)
postgres=#
MAKE_TIMESTAMP
其函数语法格式如下：
# make_timestamp(year int, month int, day int, hour int, min int, sec double precision)
postgres=# \df+ MAKE_TIMESTAMP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+----------------+-----------------------------+--------------------------------------------------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+----------------+---------------------
pg_catalog | make_timestamp | timestamp without time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision | func | immutable | safe | uxdb | invoker | | internal | make_timestamp | construct timestamp
(1 row)
postgres=#
函数功能：从年、月、日、时、分、秒域创建时间戳
postgres=# select make_timestamp(2013, 7, 15, 8, 15, 23.5);
make_timestamp
-----------------------
2013-07-15 08:15:23.5
(1 row)
postgres=#
MAKE_TIMESTAMPTZ
其函数语法格式如下：
# make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])
postgres=# \df+ MAKE_TIMESTAMPTZ
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------------------+--------------------------+-----------------------------------------------------------------------------------------------------------+------+------------+----------+-------+----------+-------------------+----------+------------------------------+------------------------------------
pg_catalog | make_timestamptz | timestamp with time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision | func | stable | safe | uxdb | invoker | | internal | make_timestamptz | construct timestamp with time zone
pg_catalog | make_timestamptz | timestamp with time zone | year integer, month integer, mday integer, hour integer, min integer, sec double precision, timezone text | func | stable | safe | uxdb | invoker | | internal | make_timestamptz_at_timezone | construct timestamp with time zone
(2 rows)
postgres=#
函数功能：从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指定timezone， 则使用当前时区
postgres=# select make_timestamptz(2013, 7, 15, 8, 15, 23.5);
make_timestamptz
--------------------------
2013-07-15 08:15:23.5+08
(1 row)
postgres=#
NOW
其函数语法格式如下：
# now()
postgres=# \df+ NOW
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+------+--------------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------
pg_catalog | now | timestamp with time zone | | func | stable | safe | uxdb | invoker | | internal | now | current transaction time
(1 row)
postgres=#
函数功能：当前日期和时间（当前事务的开始）
postgres=# select now();
now
-------------------------------
2021-01-22 10:22:25.734601+08
(1 row)
postgres=#
STATEMENT_TIMESTAMP
其函数语法格式如下：
# statement_timestamp()
postgres=# \df+ statement_timestamp
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------------------+--------------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------+------------------------
pg_catalog | statement_timestamp | timestamp with time zone | | func | stable | safe | uxdb | invoker | | internal | statement_timestamp | current statement time
(1 row)
postgres=#
函数功能：当前日期和时间（当前事务的开始）
postgres=# select statement_timestamp() ;
statement_timestamp
-------------------------------
2021-01-22 10:25:26.808869+08
(1 row)
postgres=#
TIMEOFDAY
其函数语法格式如下：
# timeofday()
postgres=# \df+ TIMEOFDAY
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------------------------------------
pg_catalog | timeofday | text | | func | volatile | safe | uxdb | invoker | | internal | timeofday | current date and time - increments during transactions
(1 row)
postgres=#
函数功能：当前日期和时间（像clock_timestamp，但是作为一个text字符串）
postgres=# select timeofday();
timeofday
-------------------------------------
Fri Jan 22 10:28:18.904842 2021 CST
(1 row)
postgres=#
TRANSACTION_TIMESTAMP
其函数语法格式如下：
# transaction_timestamp()
postgres=# \df+ TRANSACTION_TIMESTAMP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+-----------------------+--------------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+--------------------------
pg_catalog | transaction_timestamp | timestamp with time zone | | func | stable | safe | uxdb | invoker | | internal | now | current transaction time
(1 row)
postgres=#
函数功能：当前日期和时间（当前事务的开始）
postgres=# select transaction_timestamp() ;
transaction_timestamp
-------------------------------
2021-01-22 10:31:56.601246+08
(1 row)
postgres=# begin ;
BEGIN
postgres=*# select transaction_timestamp() ;
transaction_timestamp
-------------------------------
2021-01-22 10:32:05.514722+08
(1 row)
postgres=*# select transaction_timestamp() ;
transaction_timestamp
-------------------------------
2021-01-22 10:32:05.514722+08
(1 row)
postgres=*# end;
COMMIT
postgres=#
TO_TIMESTAMP
其函数语法格式如下：
# to_timestamp(double precision)
postgres=# \df+ TO_TIMESTAMP
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+--------------+--------------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+--------------------+------------------------------------------
pg_catalog | to_timestamp | timestamp with time zone | double precision | func | immutable | safe | uxdb | invoker | | internal | float8_timestamptz | convert UNIX epoch to timestamptz
pg_catalog | to_timestamp | timestamp with time zone | text, text | func | stable | safe | uxdb | invoker | | internal | to_timestamp | convert text to timestamp with time zone
(2 rows)
postgres=#
函数功能：把 Unix 时间（从 1970-01-01 00:00:00+00 开始的秒）转换成 timestamp
postgres=# select to_timestamp(1284352323);
to_timestamp
------------------------
2010-09-13 12:32:03+08
(1 row)
postgres=#
增加一个内置函数
需求如下：
输入两个
INTEGER的参数，返回一个表示两数相加及其结果的等式(TEXT)
第一步 ：实现函数
add_str
// postgres\src\backend\utils\fmgr\funcapi.c
Datum
add_str(PG_FUNCTION_ARGS) {
int arg_1, arg_2;
char buf[128] = {0x00};
char *result = NULL;
if (PG_ARGISNULL(0) || PG_ARGISNULL(1)) {
ereport(ERROR,
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
errmsg("cannot specify NULL as the arguement.")));
}
arg_1 = PG_GETARG_INT32(0);
arg_2 = PG_GETARG_INT32(1);
snprintf(buf, 128, "%d + %d = %d", arg_1, arg_2, (arg_1 + arg_2));
result = pstrdup(buf);
PG_RETURN_TEXT_P(cstring_to_text(result));
}
这里需要注意的是，与用C语言编写UDF(User-defined Functions)一样，编写的函数必须是一个fmgr-compatible的函数。即参数必须是PG_FUNCTION_ARGS, 返回值必须是Datum。
这是由于PG中所有的SQL函数(含内置函数以及UDF)都被一个通用模块模块管理，该模块通过自己的一套机制去定位并执行SQL文中指定的SQL函数(SQL文中的函数调用机制)。上述函数可以实现在PG中的任何一个代码文件中(当然也可以新建一个源文件定于)。不过需要注意的是，必须确保这个源文件包含了"funcapi.h"这个头文件，于是这里选择 postgres\src\backend\utils\fmgr\funcapi.c
此外，这个例子只是为了示意，所以做了一个最简单的逻辑。在实际开发中，对于SQL函数，通常有一类需求是希望函数返回一个结果集。对于返回结果集的SQL函数，PG中称之为SRF(Set Returning Functions)。SRF的实现有一个固定范式，详细可以参见代码中的src/include/funcapi.h的注释，内有关于这个范式的详细说明。
第二步 ：函数
add_str声明
PostgreSQL中对于内置函数的声明有一个约定俗成的共通位置，即源码中的src/include/utils/builtins.h。通常都是在该头文件中将实现的内置函数声明为一个extern函数以确保其对其他源码文件可见。不过，这也只是一个惯例而已，事实上，内置函数的声明位置并不一定限定与此。即：只要保证这个声明能让整个fmgr机制看到即可。
函数声明本身全无特别，在本例中，声明就只是下面简单的一句话：
// postgres\src\include\utils\builtins.h
extern Datum add_str(PG_FUNCTION_ARGS);
第三步 ：函数
add_str注册
对于内置函数而言，光有实现和声明是不够的。与其他的数据库对象相仿，内置函数的元信息必须写入PostgreSQL的数据字典中。而且由于系统表是数据库实例生来就有的对象，并没有一个CREATE文能帮它把元信息写入数据字典，因此这个步骤必须在源码中事先完成。这就是所谓的注册。与UDF一样，内置函数的元数据也是保存在系统表pg_proc中的。而pg_proc系统表在PostgreSQL的数据库模板中初始状态下所具有的元数据元组是在postgres\src\include\catalog\pg_proc.dat中注册的。每个元组的注册格式如下:
{ oid => '2089', descr => 'convert int4 number to hex',
proname => 'to_hex', prorettype => 'text', proargtypes => 'int4',
prosrc => 'to_hex32' },
{ oid => '2090', descr => 'convert int8 number to hex',
proname => 'to_hex', prorettype => 'text', proargtypes => 'int8',
prosrc => 'to_hex64' },
这就是to_hex函数的两种实现注册，由于本例的add_str()函数比较简单，其注册如下：
// postgres\src\include\catalog\pg_proc.dat
{ oid => '5946', descr => 'returns an equation representing the sum of two numbers and the result',
proname => 'add_str', prorettype => 'text', proargtypes => 'int4 int4',
prosrc => 'add_str' },
注：在这里需要注意的地方有：
1、OID必须保证全局唯一 这里使用的就是5946
2、PostgreSQL版本间 关于系统表略有差异 如下
# DATA(insert OID = 元组的唯一OID ( 内置函数名 属性1 属性2…… ));
# DESCR(内置函数的描述信息(使用半角双引号引起来));
第四步 ：函数
add_str测试
重新编译之后，下面是测试结果：
postgres=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)
postgres=#
postgres=# \d
Did not find any relations.
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
postgres=# select add_str(1,1);
add_str
-----------
1 + 1 = 2
(1 row)
postgres=# \df+ add_str
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
------------+---------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+------------------------------------------------------------------------
ux_catalog | add_str | text | integer, integer | func | immutable | safe | uxdb | invoker | | internal | add_str | returns an equation representing the sum of two numbers and the result
(1 row)
postgres=# \sf add_str
CREATE OR REPLACE FUNCTION ux_catalog.add_str(integer, integer)
RETURNS text
LANGUAGE internal
IMMUTABLE PARALLEL SAFE STRICT
AS $function$add_str$function$
postgres=#