PostgreSQL Tutorial (VII): Detailed explanation of functions and operators (3)

9. Sequence operation function:

Sequence objects (also called sequence generators) are special single-row tables created with CREATE SEQUENCE. A sequence object is usually used to generate unique identifiers for rows or tables. The following sequence function provides a simple and concurrent read-safe method for us to obtain the latest sequence value from the sequence object.

 

function	Return type	describe
nextval(regclass)	bigint	Increment the sequence object to its next value and return that value. This action is automatically completed. Even if multiple sessions run nextval concurrently, each process will safely receive a unique sequence value.
currval(regclass)	bigint	Returns the value of the sequence caught by nextval in the current session. (If nextval has never been called on this sequence in this session, an error will be reported.) Please note that because this function returns a session-range value and can also give a predictable result, it can be used to determine whether other sessions have executed nextval.
lastval()	bigint	Returns the value returned by the last nextval in the current session. This function is equivalent to currval, except that it does not use the sequence name as a parameter, it grabs the sequence used by nextval in the current session for the most recent time. If nextval has not been called yet, calling lastval will report an error.
setval(regclass, bigint)	bigint	Resets the counter value of the sequence object. Set the last_value field of the sequence to the specified value and set its is_called field to true, indicating that the next nextval will increment the sequence before returning the value.
setval(regclass, bigint, boolean)	bigint	Resets the counter value of the sequence object. The function is equivalent to the setval function above, except that is_called can be set to true or false. If it is set to false, the next nextval will return the value, and the subsequent nextval will start incrementing the sequence.

For the regclass parameter, you only need to enclose the sequence name in single quotes, so it looks like a text constant. To achieve the same compatibility as with normal SQL objects, this string will be converted to lowercase unless the sequence name is enclosed in double quotes, such as:
 

   
10. Conditional expression:

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

condition is an expression that returns boolean. If true, the result of the CASE expression is a result that meets the criteria. If the result is false, then the subsequent 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, such as:
 
Note: CASE expressions do not calculate any subexpressions 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 NULL values ​​with default values ​​when retrieving data for display purposes.
 

Like CASE expressions, COALESCE will not calculate parameters that do not need to be used to judge the result. That is, the parameters to the right of the first non-null parameter will not be calculated.
   
    3. NULLIF：
NULLIF returns NULL if and only if value1 and value2 are equal. Otherwise it returns value1.
 
4. GREATEST and LEAST:
The GREATEST and LEAST functions select the largest or smallest numerical value from a list of arbitrary numeric expressions. The NULL values ​​in the list will be ignored. The result will be NULL only when the result of all expressions is NULL.
 

11. Array functions and operators:

1. List of operators for arrays provided in PostgreSQL:

Operator	describe	example	result
=	equal	ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]	t
<>	Not equal to	ARRAY[1,2,3] <> ARRAY[1,2,4]	t
<	Less than	ARRAY[1,2,3] < ARRAY[1,2,4]	t
>	Greater than	ARRAY[1,4,3] > ARRAY[1,2,4]	t
<=	Less than or equal to	ARRAY[1,2,3] <= ARRAY[1,2,3]	t
>=	Greater than or equal to	ARRAY[1,4,3] >= ARRAY[1,4,3]	t
||	Array and array connection	ARRAY[1,2,3] || ARRAY[4,5,6]	{1,2,3,4,5,6}
||	Array and array connection	ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]	{{1,2,3},{4,5,6},{7,8,9}}
||	Elements and arrays are connected	3 || ARRAY[4,5,6]	{3,4,5,6}
||	Elements and arrays are connected	ARRAY[4,5,6] || 7	{4,5,6,7}

2. A list of functions provided in PostgreSQL for arrays:

function	Return type	describe	example	result
array_cat(anyarray, anyarray)	anyarray	Concatenate two arrays	array_cat(ARRAY[1,2,3], ARRAY[4,5])	{1,2,3,4,5}
array_append(anyarray, anyelement)	anyarray	Append an element to the end of an array	array_append(ARRAY[1,2], 3)	{1,2,3}
array_prepend(anyelement, anyarray)	anyarray	Append an element to the beginning of an array	array_prepend(1, ARRAY[2,3])	{1,2,3}
array_dims(anyarray)	text	Returns a text representation of the dimension of an array	array_dims(ARRAY[[1,2,3], [4,5,6]])	[1:2][1:3]
array_lower(anyarray, int)	int	Returns the lower bound of the specified array dimension	array_lower(array_prepend(0, ARRAY[1,2,3]), 1)	0
array_upper(anyarray, int)	int	Returns the upper bound of the specified array dimension	array_upper(ARRAY[1,2,3,4], 1)	4
array_to_string(anyarray, text)	text	Connect array elements using provided delimiters	array_to_string(ARRAY[1, 2, 3], '~^~')	1~^~2~^~3
string_to_array(text, text)	text[]	Use the specified separator to split the string into array elements	string_to_array('xx~^~yy~^~zz', '~^~')	{xx,yy,zz}

12. System information function:

1. A list of database-related functions provided in PostgreSQL:

 

name	Return type	describe
current_database()	name	The name of the current database
current_schema()	name	The name of the current mode
current_schemas(boolean)	name[]	Pattern name in search path
current_user	name	Username under the current execution environment
inet_client_addr()	inet	The remote address of the connection
inet_client_port()	int	Connected remote port
inet_server_addr()	inet	Local address of the connection
inet_server_port()	int	The local port to connect
session_user	name	Session username
pg_postmaster_start_time()	timestamp	Postmaster start time
user	name	current_user
version()	text	PostgreSQL version information

2. Functions that allow users to query object access rights in the program:

name	describe	Available Permissions
has_table_privilege(user,table,privilege)	Does the user have permission to access the table	SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_table_privilege(table,privilege)	Whether the current user has permission to access the table	SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER
has_database_privilege(user,database,privilege)	Does the user have permission to access the database	CREATE/TEMPORARY
has_database_privilege(database,privilege)	Whether the current user has permission to access the database	CREATE/TEMPORARY
has_function_privilege(user,function,privilege)	Does the user have permission to access the function	EXECUTE
has_function_privilege(function,privilege)	Whether the current user has permission to access the function	EXECUTE
has_language_privilege(user,language,privilege)	Does the user have permission to access the language	USAGE
has_language_privilege(language,privilege)	Does the current user have permission to access the language	USAGE
has_schema_privilege(user,schema,privilege)	Does the user have permission to access the mode	CREAT/USAGE
has_schema_privilege(schema,privilege)	Whether the current user has access mode permissions	CREAT/USAGE
has_tablespace_privilege(user,tablespace,privilege)	Does the user have permission to access the tablespace	CREATE
has_tablespace_privilege(tablespace,privilege)	Whether the current user has permission to access the tablespace	CREATE

Note: All the above functions return boolean type. To evaluate whether a user holds the authorization option on permissions, append WITH GRANT OPTION to the permission keyword; for example 'UPDATE WITH GRANT OPTION'.
3. Pattern visibility query function:
Functions that determine whether an object is visible in the current mode search path. If the pattern in which a table is located is in the search path and no table with the same name appears earlier in the search path, then the table is visually visible. It is equivalent to the table being referenced without explicit schema modification.

 

name	describe	Application Type
pg_table_is_visible(table_oid)	Is the table/view visible in the search path	regclass
pg_type_is_visible(type_oid)	Is this class/view visible in the search path	regtype
pg_function_is_visible(function_oid)	Is this function visible in the search path	regprocedure
pg_operator_is_visible(operator_oid)	Is this operator visible in the search path	regoperator
pg_opclass_is_visible(opclass_oid)	Is this operator table visible in the search path	regclass
pg_conversion_is_visible(conversion_oid)	Is the conversion visible in the search path	regoperator

Note: All the above functions return the boolean type, and all of these functions require the object OID identification as the object to be checked.
 

4. System table information function:

name	Return type	describe
format_type(type_oid,typemod)	text	Get the SQL name of a data type
pg_get_viewdef(view_oid)	text	Get CREATE VIEW command for view
pg_get_viewdef(view_oid,pretty_bool)	text	Get CREATE VIEW command for view
pg_get_ruledef(rule_oid)	text	Get the CREATE RULE command for the rule
pg_get_ruledef(rule_oid,pretty_bool)	text	Get the CREATE RULE command for the rule
pg_get_indexdef(index_oid)	text	Get the CREATE INDEX command for index
pg_get_indexdef(index_oid,column_no,pretty_bool)	text	Get the CREATE INDEX command for index. If column_no is not zero, it only gets the definition of one index field.
pg_get_triggerdef(trigger_oid)	text	Get CREATE [CONSTRAINT] TRIGGER for the trigger
pg_get_constraintdef(constraint_oid)	text	Get a definition of a constraint
pg_get_constraintdef(constraint_oid,pretty_bool)	text	Get a definition of a constraint
pg_get_expr(expr_text,relation_oid)	text	Decompile the internal form of an expression, assuming that any Vars in it reference the relationship indicated by the second parameter
pg_get_expr(expr_text,relation_oid, pretty_bool)	text	Decompile the internal form of an expression, assuming that any Vars in it reference the relationship indicated by the second parameter
pg_get_userbyid(roleid)	name	Get the role name of the given ID
pg_get_serial_sequence(table_name,column_name)	text	Get the sequence name used by a serial or bigserial field
pg_tablespace_databases(tablespace_oid)	setof oid	Gets a collection of OIDs of a set of databases that have objects in a specified tablespace (OID representation)

Most of these functions have two variants, one of which can choose a "beautiful print" of the result. The beautiful print format is easier to read, but the default format is more likely to be interpreted in the same way by future PostgreSQL versions; if it is used for dumps, avoid using beautiful prints as much as possible. Passing false to the beautiful print parameter is exactly the same as the result generated by the variant without this parameter.

13. System management functions:

1. Functions that query and modify runtime configuration parameters:

 

name	Return type	describe
current_setting(setting_name)	text	The value currently set
set_config(setting_name,new_value,is_local)	text	Set parameters and return new values

Current_setting is used to obtain the current value set by setting_name in query form. It is equivalent to the SQL command SHOW. for example:
 

set_config sets the parameter setting_name to new_value. If is_local is set to true, the new value will only be applied to the current transaction. If you want the new value to be applied to the current session, then you should use false. It is equivalent to SQL command SET. for example:
 
2. Database object size function:

 

name	Return type	describe
pg_tablespace_size(oid)	bigint	Specify the disk space used by the tablespace represented by the OID
pg_tablespace_size(name)	bigint	Disk space used by the tablespace with the specified name
pg_database_size(oid)	bigint	Specify the disk space used by the database represented by the OID
pg_database_size(name)	bigint	The disk space used by the database with the specified name
pg_relation_size(oid)	bigint	Specify the disk space used by the table or index represented by the OID
pg_relation_size(text)	bigint	Specifies the disk space used by the table or index with the name. This name can be modified with a pattern name
pg_total_relation_size(oid)	bigint	Specifies the disk space used by the table represented by the OID, including index and compressed data
pg_total_relation_size(text)	bigint	All disk space used by the table with a specified name, including indexes and compressed data. Table names can be modified with pattern names.
pg_size_pretty(bigint)	text	Convert byte-calculated sizes into a human-readable unit of size

3. Database object location function:

name	Return type	describe
pg_relation_filenode(relationregclass)	oid	Gets the file node number of the specified object (usually the oid value of the object).
pg_relation_filepath(relationregclass)	text	Gets the full pathname of the specified object.

All information provided in this blog is from the official PostgreSQL documentation. The main purpose of writing this blog is to facilitate future review. This statement is hereby declared.