PostgreSQL tutorial of vii: functions and operators in detail (3)

Sequence operation function:

      sequence objects (also known as sequence generators) are special single-row tables created with CREATE SEQUENCE. A sequence object is often used to generate unique identifiers for rows or tables. The following sequence function provides a simple and safe way to get the latest sequence value from the sequence object.

 

函数	返回类型	描述
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可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。

  for the regclass parameter, simply enclose the sequence name in single quotes, so it looks like a text constant. To achieve the same compatibility as with normal SQL objects, the string is converted to lowercase, unless the sequence name is enclosed in double quotes, such as
 

    nextval('foo')     -- Operating sequence number foo

    nextval('FOO')    -- Operating sequence number foo

    nextval('"Foo"')   -- Operating sequence number Foo

    SELECT setval('foo', 42);    -- The next time nextval Will return 43

    SELECT setval('foo', 42, true);    

    SELECT setval('foo', 42, false);   -- The next time nextval Will return 42

     
10. Conditional expression:

      1 The       SQL CASE expression is a general conditional expression similar to the if/else statement in other languages.
 

    CASE WHEN condition THEN result

        [WHEN ...]

        [ELSE result]

    END

 

      condition is an expression that returns boolean. If true, the result of the CASE expression is qualified result. If the result is false, the following WHEN clause is searched in the same way. If no WHEN condition is true, the result of the case expression is the value in the ELSE clause. If the ELSE clause is omitted and there is no matching condition, the result is NULL, e.g.,
 

    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)

 

Note: the CASE expression does not evaluate any subesxpressions that are not required for the judgment result.
     
      2. COALESCE:

      COALESCE returns the value of its first non-NULL parameter. It is often used to replace the NULL value with the default value when retrieving data for display purposes.
 

    COALESCE(value[, ...])

 

      and CASE expressions, COALESCE will not evaluate arguments that are not needed to determine the result. That is, the parameter to the right of the first nonnull parameter is not evaluated.
     
      3. NULLIF:
Es13en     returns NULL if and only if value1 and value2 are equal. Otherwise it returns value1.
 

    NULLIF(value1, value2)

    MyTest=> SELECT NULLIF('abc','abc');

     nullif

    --------

    

    (1 row)    

    MyTest=> SELECT NULLIF('abcd','abc');

     nullif

    --------

     abcd

    (1 row)

      4. GREATEST and LEAST:
The       GREATEST and LEAST functions select the largest or smallest value from an arbitrary list of numeric expressions. The NULL value in the list is ignored. The result is NULL only if the result of all expressions is 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)

Array functions and operators:

      1. List of operators for arrays provided in 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. List of functions for arrays provided in 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}

xii. System information function:

      1. List of database-related functions available in PostgreSQL:

 

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

      2. Function that allows the user to query object access in a program:

名字	描述	可用权限
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

Note: the above functions all return boolean type. To evaluate whether a user has an option to assign permissions, append WITH GRANT OPTION to the permission key; 'UPDATE WITH GRANT OPTION'.
3. Mode visibility query function:
Functions that determine whether an object is visible in the current pattern search path. A table is said to be visible if the schema of the table is in the search path, and no table with the same name appears earlier in the search path. It is equivalent to a table that can be referenced without explicit schema modifications.

 

名字	描述	应用类型
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

  note: all of the above functions return boolean type, all of which require the OID identity as an object to check.
 

    postgres=# SELECT pg_table_is_visible('testtable'::regclass);

     pg_table_is_visible

    ---------------------

     t

    (1 row)

4. System table information function:

名字	返回类型	描述
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的集合

Most of these functions come in two variants, one of which allows you to choose a "nice print" of the result. The nicely printed format is easier to read, but the default format is more likely to be interpreted in the same way by future versions of PostgreSQL; If you are using a dump, avoid using fancy printing if possible. Passing false to the nice print parameter produces exactly the same result as the variant without it.

System management functions:

1. Function to query and modify runtime configuration parameters:

 

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

  current_setting is used to get the current value of the setting_name setting in the form of a query. It is equivalent to the SQL command SHOW. For example:
 

    MyTest=# SELECT current_setting('datestyle');

     current_setting

    -----------------

     ISO, YMD

    (1 row)

 

    set_config sets the parameter setting_name to new_value. If is_local is set to true, the new value will apply only to the current transaction. If you want the new values to apply to the current session, you should use false. It is equivalent to the SQL command SET. For example:
 

    MyTest=# SELECT set_config('log_statement_stats','off', false);

     set_config

    ------------

     off

    (1 row)

    

      2. Database object size function:

 

名字	返回类型	描述
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. Database object location function:  

名字	返回类型	描述
pg_relation_filenode(relationregclass)	oid	获取指定对象的文件节点编号(通常为对象的oid值)。
pg_relation_filepath(relationregclass)	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)   

All information provided in       blog is from the PostgreSQL official documentation. The main purpose of this blog post is for easy reference in the future.