PostgreSQL之函数和操作符

一、逻辑操作符:

    常用的逻辑操作符有:ANDORNOT。其语义与其它编程语言中的逻辑操作符完全相同。

二、比较操作符:

    下面是PostgreSQL中提供的比较操作符列表:

操作符描述
<小于
>大于
<=小于或等于
>=大于或等于
=等于
!=不等于

    比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符,且返回boolean类型。除了比较操作符以外,我们还可以使用BETWEEN语句,如:
    a BETWEEN x AND y 等效于 a >= x AND a <= y    
    a NOT BETWEEN x AND y 等效于 a < x OR a > y

三、 数学函数和操作符:

    下面是PostgreSQL中提供的数学操作符列表:

操作符描述例子结果
+2 + 35
-2 - 3-1
*2 * 36
/4 / 22
%5 % 41
^2.0 ^ 3.08
|/平方根|/ 25.05
||/立方根||/ 27.03
!阶乘5 !120
!!阶乘!! 5120
@绝对值@ -5.05
&按位AND91 & 1511
|按位OR32 | 335
#按位XOR17 # 520
~按位NOT~1-2
<<按位左移1 << 416
>>按位右移8 >> 22

    按位操作符只能用于整数类型,而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying,

    下面是PostgreSQL中提供的数学函数列表,需要说明的是,这些函数中有许多都存在多种形式,区别只是参数类型不同。除非特别指明,任何特定形式的函数都返回和它的参数相同的数据类型。

函数返回类型
描述例子
结果
abs(x) 绝对值abs(-17.4)17.4
cbrt(double) 立方根cbrt(27.0)3
ceil(double/numeric) 不小于参数的最小的整数ceil(-42.8)-42
degrees(double)
 把弧度转为角度degrees(0.5)28.6478897565412
exp(double/numeric) 自然指数exp(1.0)2.71828182845905
floor(double/numeric) 不大于参数的最大整数floor(-42.8)-43
ln(double/numeric) 自然对数ln(2.0)0.693147180559945
log(double/numeric) 10为底的对数log(100.0)2
log(b numeric,x numeric) numeric指定底数的对数log(2.0, 64.0)6.0000000000
mod(y, x) 取余数mod(9,4)1
pi()
double"π"常量pi()
3.14159265358979
power(a double, b double)double求a的b次幂power(9.0, 3.0)729
power(a numeric, b numeric)numeric求a的b次幂power(9.0, 3.0)729
radians(double)double把角度转为弧度radians(45.0)0.785398163397448
random()double0.0到1.0之间的随机数值random() 
round(double/numeric) 圆整为最接近的整数round(42.4)42
round(v numeric, s int)numeric圆整为s位小数数字round(42.438,2)42.44
sign(double/numeric) 参数的符号(-1,0,+1)
sign(-8.4)-1
sqrt(double/numeric) 平方根sqrt(2.0)1.4142135623731
trunc(double/numeric) 截断(向零靠近)trunc(42.8)42
trunc(v numeric, s int)numeric截断为s小数位置的数字trunc(42.438,2)42.43

    三角函数列表:

函数描述
acos(x)反余弦
asin(x)反正弦
atan(x)反正切
atan2(x, y)正切 y/x 的反函数
cos(x)余弦
cot(x)余切
sin(x)正弦
tan(x)正切


四、字符串函数和操作符:

    下面是PostgreSQL中提供的字符串操作符列表:

函数返回类型描述例子结果
string || stringtext字串连接'Post' || 'greSQL'PostgreSQL
bit_length(string)int字串里二进制位的个数bit_length('jose')32
char_length(string)int字串中的字符个数char_length('jose')4
convert(string using conversion_name)text使用指定的转换名字改变编码。convert('PostgreSQL' using iso_8859_1_to_utf8)'PostgreSQL'
lower(string)text把字串转化为小写lower('TOM')tom
octet_length(string)int字串中的字节数octet_length('jose')4
overlay(string placing string from int [for int])text替换子字串overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas
position(substring in string)int指定的子字串的位置position('om' in 'Thomas')3
substring(string [from int] [for int])text抽取子字串substring('Thomas' from 2 for 3)hom
substring(string from pattern)text抽取匹配 POSIX 正则表达式的子字串substring('Thomas' from '...$')mas
substring(string from pattern for escape)text抽取匹配SQL正则表达式的子字串substring('Thomas' from '%#"o_a#"_' for '#')oma
trim([leading | trailing | both] [characters] from string)text从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串trim(both 'x' from 'xTomxx')Tom
upper(string)text把字串转化为大写。upper('tom')TOM
ascii(text)int参数第一个字符的ASCII码ascii('x')120
btrim(string text [, characters text])text从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串btrim('xyxtrimyyx','xy')trim
chr(int)text给出ASCII码的字符chr(65)A
convert(string text, [src_encoding name,] dest_encoding name)text把字串转换为dest_encodingconvert( 'text_in_utf8', 'UTF8', 'LATIN1')以ISO 8859-1编码表示的text_in_utf8
initcap(text)text把每个单词的第一个子母转为大写,其它的保留小写。单词是一系列字母数字组成的字符,用非字母数字分隔。initcap('hi thomas')Hi Thomas
length(string text)intstring中字符的数目length('jose')4
lpad(string text, length int [, fill text])text通过填充字符fill(缺省时为空白),把string填充为长度length。 如果string已经比length长则将其截断(在右边)。lpad('hi', 5, 'xy')xyxhi
ltrim(string text [, characters text])text从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。ltrim('zzzytrim','xyz')trim
md5(string text)text计算给出string的MD5散列,以十六进制返回结果。md5('abc') 
repeat(string text, number int)text重复string number次。repeat('Pg', 4)PgPgPgPg
replace(string text, from text, to text)text把字串string里出现地所有子字串from替换成子字串to。replace('abcdefabcdef', 'cd', 'XX')abXXefabXXef
rpad(string text, length int [, fill text])text通过填充字符fill(缺省时为空白),把string填充为长度length。如果string已经比length长则将其截断。rpad('hi', 5, 'xy')hixyx
rtrim(string text [, character text])text从字串string的结尾删除只包含character(缺省是个空白)的最长的字 rtrim('trimxxxx','x')trim
split_part(string text, delimiter text, field int)text根据delimiter分隔string返回生成的第field个子字串(1 Base)。split_part('abc~@~def~@~ghi', '~@~', 2)def
strpos(string, substring)text声明的子字串的位置。strpos('high','ig')2
substr(string, from [, count])text抽取子字串。substr('alphabet', 3, 2)ph
to_ascii(text [, encoding])text把text从其它编码转换为ASCII。to_ascii('Karel')Karel
to_hex(number int/bigint)text把number转换成其对应地十六进制表现形式。to_hex(9223372036854775807)7fffffffffffffff
translate(string text, from text, to text)text把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。translate('12345', '14', 'ax')a23x5


五、位串函数和操作符:

    对于类型bit和bit varying,除了常用的比较操作符之外,还可以使用以下列表中由PostgreSQL提供的位串函数和操作符,其中&、|和#的位串操作数必须等长。在移位的时候,保留原始的位串的的长度。

操作符描述例子结果
||连接B'10001' || B'011'10001011
&按位ANDB'10001' & B'01101'00001
|按位ORB'10001' | B'01101'11101
#按位XORB'10001' # B'01101'11100
~按位NOT~ B'10001'01110
<<按位左移B'10001' << 301000
>>按位右移B'10001' >> 200100

    除了以上列表中提及的操作符之外,位串还可以使用字符串函数:length, bit_length, octet_length, position, substring。此外,我们还可以在整数和bit之间来回转换,如:
    MyTest=# SELECT 44::bit(10);
        bit
    ------------
     0000101100
    (1 row)
    MyTest=# SELECT 44::bit(3);
     bit
    -----
     100
    (1 row)
    MyTest=# SELECT cast(-44 as bit(12));
         bit
    --------------
     111111010100
    (1 row)
    MyTest=# SELECT '1110'::bit(4)::integer;
     int4
    ------

       14

六、模式匹配:

    PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式。
    1. LIKE:
    string LIKE pattern [ ESCAPE escape-character ]
    string NOT LIKE pattern [ ESCAPE escape-character ]
    每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里,那么LIKE表达式返回真。和我们想象的一样,如果LIKE返回真,那么NOT LIKE表达式返回假,反之亦然。在pattern里的下划线(_)代表匹配任何单个字符,而一个百分号(%)匹配任何零或更多字符,如:
    'abc' LIKE 'abc'     true
    'abc' LIKE 'a%'     true
    'abc' LIKE '_b_'    true
    'abc' LIKE 'c'        false  

    要匹配文本的下划线或者百分号,而不是匹配其它字符,在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜杠,但是你可以用ESCAPE子句指定一个。要匹配转义字符本身,写两个转义字符。我们也可以通过写成ESCAPE ''的方式有效地关闭转义机制,此时,我们就不能关闭下划线和百分号的特殊含义了。
    关键字ILIKE可以用于替换LIKE,令该匹配就当前的区域设置是大小写无关的。这个特性不是SQL标准,是PostgreSQL的扩展。操作符~~等效于LIKE, 而~~*对应ILIKE。还有!~~!~~*操作符分别代表NOT LIKENOT ILIKE。所有这些操作符都是PostgreSQL特有的。

    2. SIMILAR TO正则表达式:
    SIMILAR TO根据模式是否匹配给定的字符串而返回真或者假。
    string SIMILAR TO pattern [ESCAPE escape-character]
    string NOT SIMILAR TO pattern [ESCAPE escape-character]
    它和LIKE非常类似,支持LIKE的通配符('_''%')且保持其原意。除此之外,SIMILAR TO还支持一些自己独有的元字符,如:    
    1). | 标识选择(两个候选之一)。
    2). * 表示重复前面的项零次或更多次。
    3). + 表示重复前面的项一次或更多次。
    4). 可以使用圆括弧()把项组合成一个逻辑项。
    5). 一个方括弧表达式[...]声明一个字符表,就像POSIX正则表达式一样。
    见如下示例:
    'abc' SIMILAR TO 'abc'           true
    'abc' SIMILAR TO 'a'              false
    'abc' SIMILAR TO '%(b|d)%'  true
    'abc' SIMILAR TO '(b|c)%'     false

    带三个参数的substring,substring(string from pattern for escape-character),提供了一个从字串中抽取一个匹配SQL正则表达式模式的子字串的函数。和SIMILAR TO一样,声明的模式必须匹配整个数据串,否则函数失效并返回NULL。为了标识在成功的时候应该返回的模式部分,模式必须出现后跟双引号(")的两个转义字符。匹配这两个标记之间的模式的字串将被返回,如:
    MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#'); --这里#是转义符,双引号内的模式是返回部分。
     substring
    -----------
     oob
    (1 row)
    MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#');  --foobar不能完全匹配后面的模式,因此返回NULL。
     substring
    -----------

    (1 row)

七、数据类型格式化函数:

    PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的调用习惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板。

函数返回类型描述例子
to_char(timestamp, text)text把时间戳转换成字串to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)text把时间间隔转为字串to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)text把整数转换成字串to_char(125, '999')
to_char(double precision, text)text把实数/双精度数转换成字串to_char(125.8::real, '999D9')
to_char(numeric, text)text把numeric转换成字串to_char(-125.8, '999D99S')
to_date(text, text)date把字串转换成日期to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)timestamp把字串转换成时间戳to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(double)timestamp把UNIX纪元转换成时间戳to_timestamp(200120400)
to_number(text, text)numeric把字串转换成numericto_number('12,454.8-', '99G999D9S')

     1. 用于日期/时间格式化的模式:

模式描述
HH一天的小时数(01-12)
HH12一天的小时数(01-12)
HH24一天的小时数(00-23)
MI分钟(00-59)
SS秒(00-59)
MS毫秒(000-999)
US微秒(000000-999999)
AM正午标识(大写)
Y,YYY带逗号的年(4和更多位)
YYYY年(4和更多位)
YYY年的后三位
YY年的后两位
Y年的最后一位
MONTH全长大写月份名(空白填充为9字符)
Month全长混合大小写月份名(空白填充为9字符)
month全长小写月份名(空白填充为9字符)
MON大写缩写月份名(3字符)
Mon缩写混合大小写月份名(3字符)
mon小写缩写月份名(3字符)
MM月份号(01-12)
DAY全长大写日期名(空白填充为9字符)
Day全长混合大小写日期名(空白填充为9字符)
day全长小写日期名(空白填充为9字符)
DY缩写大写日期名(3字符)
Dy缩写混合大小写日期名(3字符)
dy缩写小写日期名(3字符)
DDD一年里的日子(001-366)
DD一个月里的日子(01-31)
D一周里的日子(1-7;周日是1)
W一个月里的周数(1-5)(第一周从该月第一天开始)
WW一年里的周数(1-53)(第一周从该年的第一天开始)

     2. 用于数值格式化的模板模式:

模式描述
9带有指定数值位数的值
0带前导零的值
.(句点)小数点
,(逗号)分组(千)分隔符
PR尖括号内负值
S带符号的数值
L货币符号
D小数点
G分组分隔符
MI在指明的位置的负号(如果数字 < 0)
PL在指明的位置的正号(如果数字 > 0)
SG在指明的位置的正/负号


八、时间/日期函数和操作符:

    1. 下面是PostgreSQL中支持的时间/日期操作符的列表:

操作符例子结果
+date '2001-09-28' + integer '7'date '2001-10-05'
+date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00'
+date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00'
+interval '1 day' + interval '1 hour'interval '1 day 01:00'
+timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00'
+time '01:00' + interval '3 hours'time '04:00'
-- interval '23 hours'interval '-23:00'
-date '2001-10-01' - date '2001-09-28'integer '3'
-date '2001-10-01' - integer '7'date '2001-09-24'
-date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00'
-time '05:00' - time '03:00'interval '02:00'
-time '05:00' - interval '2 hours'time '03:00'
-timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00'
-interval '1 day' - interval '1 hour'interval '23:00'
-timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00'
*interval '1 hour' * double precision '3.5'interval '03:30'
/interval '1 hour' / double precision '1.5'interval '00:40'

    2. 日期/时间函数:

函数返回类型描述例子结果
age(timestamp, timestamp)interval减去参数,生成一个使用年、月的"符号化"的结果age('2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)interval从current_date减去得到的数值age(timestamp '1957-06-13')43 years 8 mons 3 days
current_datedate今天的日期  
current_timetime现在的时间  
current_timestamptimestamp日期和时间  
date_part(text, timestamp)double获取子域(等效于extract)date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double获取子域(等效于extract)date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp截断成指定的精度date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00+00
extract(field from timestamp)double获取子域extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double获取子域extract(month from interval '2 years 3 months')3
localtimetime今日的时间  
localtimestamptimestamp日期和时间  
now()timestamp当前的日期和时间(等效于 current_timestamp)  
timeofday()text当前日期和时间  

    3. EXTRACT,date_part函数支持的field:

描述例子结果
CENTURY世纪EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');20
DAY(月分)里的日期域(1-31)EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');16
DECADE年份域除以10EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');200
DOW每周的星期号(0-6;星期天是0) (仅用于timestamp)EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');5
DOY一年的第几天(1 -365/366) (仅用于 timestamp)EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');47
HOUR小时域(0-23)EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');20
MICROSECONDS秒域,包括小数部分,乘以 1,000,000。EXTRACT(MICROSECONDS from TIME '17:12:28.5');28500000
MILLENNIUM千年EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');3
MILLISECONDS秒域,包括小数部分,乘以 1000。EXTRACT(MILLISECONDS from TIME '17:12:28.5');28500
MINUTE分钟域(0-59)EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');38
MONTH对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11)EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');2
QUARTER该天所在的该年的季度(1-4)(仅用于 timestamp)EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');1
SECOND秒域,包括小数部分(0-59[1])EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');40
WEEK该天在所在的年份里是第几周。EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');7
YEAR年份域EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');2001

    4. 当前日期/时间:
    我们可以使用下面的函数获取当前的日期和/或时间∶
    CURRENT_DATE
    CURRENT_TIME
    CURRENT_TIMESTAMP
    CURRENT_TIME (precision)
    CURRENT_TIMESTAMP (precision)
    LOCALTIME
    LOCALTIMESTAMP
    LOCALTIME (precision)
    LOCALTIMESTAMP (precision)

九、序列操作函数:

    序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。

函数返回类型描述
nextval(regclass)bigint递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值。
currval(regclass)bigint在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval,那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值,而且也能给出一个可预计的结果,因此可以用于判断其它会话是否执行过nextval。
lastval()bigint返回当前会话里最近一次nextval返回的数值。这个函数等效于currval,只是它不用序列名为参数,它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval,那么调用lastval将会报错。
setval(regclass, bigint)bigint重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true,表示下一次nextval将在返回数值之前递增该序列。
setval(regclass, bigint, boolean)bigint重置序列对象的计数器数值。功能等同于上面的setval函数,只是is_called可以设置为truefalse。如果将其设置为false,那么下一次nextval将返回该数值,随后的nextval才开始递增该序列。

    对于regclass参数,仅需用单引号括住序列名即可,因此它看上去就像文本常量。为了达到和处理普通SQL对象一样的兼容性,这个字串将被转换成小写,除非该序列名是用双引号括起,如:
    nextval('foo')     --操作序列号foo
    nextval('FOO')    --操作序列号foo
    nextval('"Foo"')   --操作序列号Foo
    SELECT setval('foo', 42);    --下次nextval将返回43
    SELECT setval('foo', 42, true);   

    SELECT setval('foo', 42, false);   --下次nextval将返回42

   
十、条件表达式:

    1. CASE:
    SQL CASE表达式是一种通用的条件表达式,类似于其它语言中的if/else语句。
    CASE WHEN condition THEN result
        [WHEN ...]
        [ELSE result]
    END
    condition是一个返回boolean的表达式。如果为真,那么CASE表达式的结果就是符合条件的result。如果结果为假,那么以相同方式搜寻随后的WHEN子句。如果没有WHEN condition为真,那么case表达式的结果就是在ELSE子句里的值。如果省略了ELSE子句而且没有匹配的条件,结果为NULL,如:
    MyTest=> SELECT * FROM testtable;
     i
    ---
     1
     2
     3
    (3 rows)
    MyTest=> SELECT i, CASE WHEN i=1 THEN 'one'
    MyTest->                         WHEN i=2 THEN 'two'
    MyTest->                         ELSE 'other'
    MyTest->                END
    MyTest-> FROM testtable;

     i | case
    ---+-------
     1 | one
     2 | two
     3 | other
    (3 rows)
    注:CASE表达式并不计算任何对于判断结果并不需要的子表达式。
    
    2. COALESCE:
    COALESCE返回它的第一个非NULL的参数的值。它常用于在为显示目的检索数据时用缺省值替换NULL值。
    COALESCE(value[, ...])
    和CASE表达式一样,COALESCE将不会计算不需要用来判断结果的参数。也就是说,在第一个非空参数右边的参数不会被计算。
    
    3. NULLIF:
    当且仅当value1和value2相等时,NULLIF才返回NULL。否则它返回value1。
    NULLIF(value1, value2)
    MyTest=> SELECT NULLIF('abc','abc');
     nullif
    --------
    
    (1 row)   
    MyTest=> SELECT NULLIF('abcd','abc');
     nullif
    --------
     abcd
    (1 row)

    4. GREATEST和LEAST:
    GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。列表中的NULL数值将被忽略。只有所有表达式的结果都是NULL的时候,结果才会是NULL。
    GREATEST(value [, ...])
    LEAST(value [, ...])
    MyTest=> SELECT GREATEST(1,3,5);
     greatest
    ----------
            5
    (1 row) 
    MyTest=> SELECT LEAST(1,3,5,NULL);
     least
    -------
         1
    (1 row)
    
十一、数组函数和操作符:

    1. PostgreSQL中提供的用于数组的操作符列表:

操作符描述例子结果
=等于ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t
<>不等于ARRAY[1,2,3] <> ARRAY[1,2,4]t
<小于ARRAY[1,2,3] < ARRAY[1,2,4]t
>大于ARRAY[1,4,3] > ARRAY[1,2,4]t
<=小于或等于ARRAY[1,2,3] <= ARRAY[1,2,3]t
>=大于或等于ARRAY[1,4,3] >= ARRAY[1,4,3]t
||数组与数组连接ARRAY[1,2,3] || ARRAY[4,5,6]{1,2,3,4,5,6}
||数组与数组连接ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]{{1,2,3},{4,5,6},{7,8,9}}
||元素与数组连接3 || ARRAY[4,5,6]{3,4,5,6}
||元素与数组连接ARRAY[4,5,6] || 7{4,5,6,7}

    2. PostgreSQL中提供的用于数组的函数列表:

函数返回类型描述例子结果
array_cat(anyarray, anyarray)anyarray连接两个数组array_cat(ARRAY[1,2,3], ARRAY[4,5]){1,2,3,4,5}
array_append(anyarray, anyelement)anyarray向一个数组末尾附加一个元素array_append(ARRAY[1,2], 3){1,2,3}
array_prepend(anyelement, anyarray)anyarray向一个数组开头附加一个元素array_prepend(1, ARRAY[2,3]){1,2,3}
array_dims(anyarray)text返回一个数组维数的文本表示array_dims(ARRAY[[1,2,3], [4,5,6]])[1:2][1:3]
array_lower(anyarray, int)int返回指定的数组维数的下界array_lower(array_prepend(0, ARRAY[1,2,3]), 1)0
array_upper(anyarray, int)int返回指定数组维数的上界array_upper(ARRAY[1,2,3,4], 1)4
array_to_string(anyarray, text)text使用提供的分隔符连接数组元素array_to_string(ARRAY[1, 2, 3], '~^~')1~^~2~^~3
string_to_array(text, text)text[]使用指定的分隔符把字串拆分成数组元素string_to_array('xx~^~yy~^~zz', '~^~'){xx,yy,zz}

十二、系统信息函数:

    1. PostgreSQL中提供的和数据库相关的函数列表:

名字返回类型描述
current_database()name当前数据库的名字
current_schema()name当前模式的名字
current_schemas(boolean)name[]在搜索路径中的模式名字
current_username目前执行环境下的用户名
inet_client_addr()inet连接的远端地址
inet_client_port()int连接的远端端口
inet_server_addr()inet连接的本地地址
inet_server_port()int连接的本地端口
session_username会话用户名
pg_postmaster_start_time()timestamppostmaster启动的时间
usernamecurrent_user
version()textPostgreSQL版本信息

    2. 允许用户在程序里查询对象访问权限的函数:

名字描述可用权限
has_table_privilege(user,table,privilege)用户是否有访问表的权限SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_table_privilege(table,privilege)当前用户是否有访问表的权限SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_database_privilege(user,database,privilege)用户是否有访问数据库的权限CREATE/TEMPORARY
has_database_privilege(database,privilege)当前用户是否有访问数据库的权限CREATE/TEMPORARY
has_function_privilege(user,function,privilege)用户是否有访问函数的权限EXECUTE
has_function_privilege(function,privilege)当前用户是否有访问函数的权限EXECUTE
has_language_privilege(user,language,privilege)用户是否有访问语言的权限USAGE
has_language_privilege(language,privilege)当前用户是否有访问语言的权限USAGE
has_schema_privilege(user,schema,privilege)用户是否有访问模式的权限CREAT/USAGE
has_schema_privilege(schema,privilege)当前用户是否有访问模式的权限CREAT/USAGE
has_tablespace_privilege(user,tablespace,privilege)用户是否有访问表空间的权限CREATE
has_tablespace_privilege(tablespace,privilege)当前用户是否有访问表空间的权限CREATE

    注:以上函数均返回boolean类型。要评估一个用户是否在权限上持有赋权选项,给权限键字附加 WITH GRANT OPTION;比如 'UPDATE WITH GRANT OPTION'。
    3. 模式可视性查询函数:
    那些判断一个对象是否在当前模式搜索路径中可见的函数。 如果一个表所在的模式在搜索路径中,并且没有同名的表出现在搜索路径的更早的地方,那么就说这个表视可见的。 它等效于表可以不带明确模式修饰进行引用。

名字描述应用类型
pg_table_is_visible(table_oid)该表/视图是否在搜索路径中可见regclass
pg_type_is_visible(type_oid)该类/视图型是否在搜索路径中可见regtype
pg_function_is_visible(function_oid)该函数是否在搜索路径中可见regprocedure
pg_operator_is_visible(operator_oid)该操作符是否在搜索路径中可见regoperator
pg_opclass_is_visible(opclass_oid)该操作符表是否在搜索路径中可见regclass
pg_conversion_is_visible(conversion_oid)转换是否在搜索路径中可见regoperator

    注:以上函数均返回boolean类型,所有这些函数都需要对象 OID 标识作为检查的对象。
    postgres=# SELECT pg_table_is_visible('testtable'::regclass);

     pg_table_is_visible
    ---------------------
     t
    (1 row)

    4. 系统表信息函数:

名字返回类型描述
format_type(type_oid,typemod)text获取一个数据类型的SQL名称
pg_get_viewdef(view_oid)text为视图获取CREATE VIEW命令
pg_get_viewdef(view_oid,pretty_bool)text为视图获取CREATE VIEW命令
pg_get_ruledef(rule_oid)text为规则获取CREATE RULE命令
pg_get_ruledef(rule_oid,pretty_bool)text为规则获取CREATE RULE命令
pg_get_indexdef(index_oid)text为索引获取CREATE INDEX命令
pg_get_indexdef(index_oid,column_no,pretty_bool)text为索引获取CREATE INDEX命令, 如果column_no不为零,则是只获取一个索引字段的定义
pg_get_triggerdef(trigger_oid)text为触发器获取CREATE [CONSTRAINT] TRIGGER
pg_get_constraintdef(constraint_oid)text获取一个约束的定义
pg_get_constraintdef(constraint_oid,pretty_bool)text获取一个约束的定义
pg_get_expr(expr_text,relation_oid)text反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系
pg_get_expr(expr_text,relation_oid, pretty_bool)text反编译一个表达式的内部形式,假设其中的任何Vars都引用第二个参数指出的关系
pg_get_userbyid(roleid)name获取给出的ID的角色名
pg_get_serial_sequence(table_name,column_name)text获取一个serial或者bigserial字段使用的序列名字
pg_tablespace_databases(tablespace_oid)setof oid获取在指定表空间(OID表示)中拥有对象的一套数据库的OID的集合

    这些函数大多数都有两个变种,其中一个可以选择对结果的"漂亮的打印"。 漂亮打印的格式更容易读,但是缺省的格式更有可能被将来的PostgreSQL版本用同样的方法解释;如果是用于转储,那么尽可能避免使用漂亮打印。 给漂亮打印参数传递false生成的结果和那个没有这个参数的变种生成的结果完全一样。

、系统管理函数:

    1. 查询以及修改运行时配置参数的函数:

名字返回类型描述
current_setting(setting_name)text当前设置的值
set_config(setting_name,new_value,is_local)text设置参数并返回新值

    current_setting用于以查询形式获取setting_name设置的当前数值。它和SQL命令SHOW是等效的。 比如:
    MyTest=# SELECT current_setting('datestyle');
     current_setting
    -----------------
     ISO, YMD
    (1 row)
    set_config将参数setting_name设置为new_value。如果is_local设置为true,那么新数值将只应用于当前事务。如果你希望新的数值应用于当前会话,那么应该使用false。它等效于SQL命令SET。比如:
    MyTest=# SELECT set_config('log_statement_stats','off', false);
     set_config
    ------------
     off
    (1 row)
   
    2. 数据库对象尺寸函数:

名字返回类型描述
pg_tablespace_size(oid)bigint指定OID代表的表空间使用的磁盘空间
pg_tablespace_size(name)bigint指定名字的表空间使用的磁盘空间
pg_database_size(oid)bigint指定OID代表的数据库使用的磁盘空间
pg_database_size(name)bigint指定名称的数据库使用的磁盘空间
pg_relation_size(oid)bigint指定OID代表的表或者索引所使用的磁盘空间
pg_relation_size(text)bigint指定名称的表或者索引使用的磁盘空间。这个名字可以用模式名修饰
pg_total_relation_size(oid)bigint指定OID代表的表使用的磁盘空间,包括索引和压缩数据
pg_total_relation_size(text)bigint指定名字的表所使用的全部磁盘空间,包括索引和压缩数据。表名字可以用模式名修饰。
pg_size_pretty(bigint)text把字节计算的尺寸转换成一个人类易读的尺寸单位

    3. 数据库对象位置函数: 

名字返回类型描述
pg_relation_filenode(relation regclass)oid获取指定对象的文件节点编号(通常为对象的oid值)。
pg_relation_filepath(relation regclass)text获取指定对象的完整路径名。

    mydatabase=# select pg_relation_filenode('testtable');
     pg_relation_filenode
    ----------------------
                    17877
    (1 row)   
    mydatabase=# select pg_relation_filepath('testtable');
                 pg_relation_filepath
    ----------------------------------------------
     pg_tblspc/17633/PG_9.1_201105231/17636/17877
    (1 row)    


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值