Session Information Functions
SYS_CONTEXT()
Description: Returns the value of the parameter associated with the context namespace at the current time. This function has the same function as USERENV() when the first input parameter is userenv.
Currently, the following parameters can be used as the second input parameter.
'current_edition_name', 'current_schema', 'current_sql', 'current_user', 'current_userid', 'database_role', 'ip_address', 'is_apply_server', 'isdba', 'nls_currency', 'nls_date_format', 'nls_date_language', 'server_host', 'service_name', 'session_user', 'session_userid', 'db_name', 'db_unique_name', 'instance', 'instance_name', 'current_edition_id', 'current_schemaid', 'lang', 'language', and 'sid'
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT SYS_CONTEXT('userenv','NLS_CURRENCY'); sys_context ------------- $ (1 row) SELECT SYS_CONTEXT('userenv','NLS_DATE_FORMAT'); sys_context --------------- ISO, MDY (1 row) SELECT SYS_CONTEXT('userenv','NLS_DATE_LANGUAGE'); sys_context ------------- en_US.UTF-8 (1 row) |
USERENV()
Description: Returns the value of the parameter associated with the context namespace at the current time. This function has the same function as SYS_CONTEXT() when the first input parameter is userenv.
Currently, the following input parameters are supported:
'current_edition_name', 'current_schema', 'current_sql', 'current_user', 'current_userid', 'database_role', 'ip_address', 'is_apply_server', 'isdba', 'nls_currency', 'nls_date_format', 'nls_date_language', 'server_host', 'service_name', 'session_user', 'session_userid', 'db_name', 'db_unique_name', 'instance', 'instance_name', 'current_edition_id', 'current_schemaid', 'lang', 'language', and 'sid'
Currently, the following input parameters are not supported:
'action', 'is_application_root', 'is_application_pdb', 'audited_cursorid', 'authenticated_identity', 'authentication_data', 'authentication_method', 'cdb_domain', 'cdb_name', 'client_identifier', 'con_id', 'con_name', 'current_sql_length', 'db_domain', 'db_supplemental_log_level', 'dblink_info', 'drain_status', 'entryid', 'enterprise_identity', 'fg_job_id', 'global_uid', 'identification_type', 'is_dg_rolling_upgrade', 'ldap_server_type', 'module', 'network_protocol', 'nls_calendar', 'nls_sort', 'nls_territory', 'oracle_home', 'os_user', 'platform_slash', 'policy_invoker', 'proxy_enterprise_identity', 'proxy_user', 'proxy_userid', 'scheduler_job', 'session_edition_id', 'session_edition_name', 'sessionid', 'statementid', 'terminal', 'unified_audit_sessionid', 'session_default_collation', 'client_info', 'bg_job_id', 'client_program_name', 'current_bind', 'global_context_memory', 'host', and 'current_sqln'
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT USERENV('sid'); userenv --------- 955 (1 row) SELECT USERENV('instance'); userenv --------- 14524 (1 row) SELECT USERENV('action'); userenv --------- (1 row) SELECT USERENV('actijj'); ERROR: unrecognized configuration parameter "actijj" CONTEXT: PL/pgSQL function userenv(text) line 114 at RETURN referenced column: userenv |
If the input parameter is not supported, NULL is returned. If the input parameter is invalid, an error is reported.
current_catalog
Description: Name of the current database (called "catalog" in the SQL standard)
Return type: name
Example:
1 2 3 4 5 |
testdb=# SELECT current_catalog; current_database ------------------ testdb (1 row) |
current_database()
Description: Name of the current database
Return type: name
Example:
1 2 3 4 5 |
testdb=# SELECT current_database(); current_database ------------------ testdb (1 row) |
current_query()
Description: Text of the currently executing query committed by the client (which might contain more than one statement)
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_query(); current_query ------------------------- SELECT current_query(); (1 row) |
current_schema[()]
Description: Name of the current schema
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_schema(); current_schema ---------------- public (1 row) |
Note: current_schema returns the first valid schema name in the search path. (If the search path is empty or contains no valid schema name, NULL is returned.) This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.
current_schemas(Boolean)
Description: Name of a schema in the search path
Return type: name[]
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_schemas(true); current_schemas --------------------- {pg_catalog,public} (1 row) |
Note:
current_schemas(Boolean) returns an array of the names of all schemas in the search path. The Boolean option specifies whether implicitly included system schemas such as pg_catalog are included in the returned search path.
The search path can be altered at the run time. The command is as follows:
1
|
SET search_path TO schema [, schema, ...] |
database()
Description: Returns the name of the current schema.
Parameter: none
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT database(); database ---------- public (1 row) |
This function takes effect only when sql_compatibility is set to 'B', b_format_version is set to '5.7', and b_format_dev_version is set to 's1'.
current_user
Description: Username of current execution context
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT current_user; current_user -------------- omm (1 row) |
Note: current_user is the user identifier used for permission check. Normally it is equal to the session user, but it can be changed by using SET ROLE. It also changes during the execution of functions with the SECURITY DEFINER attribute.
definer_current_user
Description: Username of current execution context
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT definer_current_user(); definer_current_user ---------------------- omm (1 row) |
pg_current_sessionid()
Description: Session ID in the current execution environment.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_current_sessionid(); pg_current_sessionid ---------------------------- 1579228402.140190434944768 (1 row) |
Note: pg_current_sessionid() is used to obtain the session ID in the current execution environment. The structure of the value is Timestamp.Session ID. When enable_thread_pool is set to on, the actual session ID is used. When enable_thread_pool is set to off, the session ID is the thread ID.
pg_current_sessid
Description: Session ID in the current execution environment.
Return type: text
Example:
gaussdb=# select pg_current_sessid();
pg_current_sessid
-------------------
140308875015936
(1 row)
Note: In thread pool mode, the session ID of the current session is obtained. In non-thread pool mode, the backend thread ID of the current session is obtained.
pg_current_userid
Description: Current user ID
Return type: text
gaussdb=# SELECT pg_current_userid();
pg_current_userid
-------------------
10
(1 row)
working_version_num()
Description: version number. It returns a version number related to system compatibility.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT working_version_num(); working_version_num --------------------- 92231 (1 row) |
tablespace_oid_name(oid)
Description: Queries the tablespace name based on the tablespace OID.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT tablespace_oid_name(1663); tablespace_oid_name --------------------- pg_default (1 row) |
inet_client_addr()
Description: Remote connection address. inet_client_addr returns the IP address of the current client.
It is available only in remote connection mode.
Return type: inet
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_client_addr(); inet_client_addr ------------------ 10.10.0.50 (1 row) |
inet_client_port()
Description: Remote connection port. inet_client_port returns the port number of the current client.
This function is valid only in remote connection mode.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_client_port(); inet_client_port ------------------ 33143 (1 row) |
inet_server_addr()
Description: Local connection address. inet_server_addr returns the IP address on which the server accepts the current connection.
It is available only in remote connection mode.
Return type: inet
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_server_addr(); inet_server_addr ------------------ 10.10.0.13 (1 row) |
inet_server_port()
Description: Local connection port. inet_server_port returns the number of the port receiving the current connection. All these functions return NULL if the current connection is via a UDS.
This function is valid only in remote connection mode.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT inet_server_port(); inet_server_port ------------------ 8000 (1 row) |
pg_backend_pid()
Description: Thread ID of the worker thread connected to the current session.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_backend_pid(); pg_backend_pid ----------------- 140229352617744 (1 row) |
pg_conf_load_time()
Description: Configures load time. pg_conf_load_time returns the timestamp when the server configuration files were last loaded.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_conf_load_time(); pg_conf_load_time ------------------------------ 2017-09-01 16:05:23.89868+08 (1 row) |
pg_my_temp_schema()
Description: OID of the temporary schema of a session. The value is 0 if the OID does not exist.
Return type: oid
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_my_temp_schema(); pg_my_temp_schema ------------------- 0 (1 row) |
Note: pg_my_temp_schema returns the OID of the current session's temporary schema, or 0 if it has no temporary schemas (because no temporary tables are created). pg_is_other_temp_schema returns true if the given OID is the OID of another session's temporary schema.
pg_is_other_temp_schema(oid)
Description: Specifies whether the schema is the temporary schema of another session.
Return type: Boolean
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_is_other_temp_schema(25356); pg_is_other_temp_schema ------------------------- f (1 row) |
pg_listening_channels()
Description: Name of the channel that the session is currently listening to.
Return type: setof text
Example:
1 2 3 4 |
gaussdb=# SELECT pg_listening_channels(); pg_listening_channels ----------------------- (0 rows) |
Note: pg_listening_channels returns a set of names of channels that the current session is currently listening to.
pg_postmaster_start_time()
Description: Server start time. pg_postmaster_start_time returns the timestamp with time zone when the server is started.
Return type: timestamp with time zone
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_postmaster_start_time(); pg_postmaster_start_time ------------------------------ 2017-08-30 16:02:54.99854+08 (1 row) |
pg_get_ruledef(rule_oid)
Description: Obtains the CREATE RULE command for a rule.
Return type: text
Example:
gaussdb=# SELECT * FROM pg_get_ruledef(24828);
pg_get_ruledef
-------------------------------------------------------------------
CREATE RULE t1_ins AS ON INSERT TO t1 DO INSTEAD INSERT INTO t2 (id) VALUES (new.id);
(1 row)
sessionid2pid()
Description: Obtains PID information from a session ID (for example, the sessid column in gs_session_stat).
Return type: int8
Example:
1 2 3 4 5 6 |
gaussdb=# SELECT sessionid2pid(sessid::cstring) FROM gs_session_stat LIMIT 2; sessionid2pid ----------------- 139973107902208 139973107902208 (2 rows) |
session_context( 'namespace' , 'parameter')
Description: Obtains and returns the parameter values of a specified namespace.
Return type: VARCHAR
Example:
1 2 3 4 5 |
gaussdb=# SELECT session_context('USERENV', 'CURRENT_SCHEMA'); session_context ------------------ public (1 row) |
Note: Currently, the current_user, current_schema, client_info, ip_address, sessionid, and sid parameters are supported.
pg_trigger_depth()
Description: Nesting level of triggers.
Return type: int
Example:
1 2 3 4 5 |
gaussdb=# SELECT pg_trigger_depth(); pg_trigger_depth ------------------ 0 (1 row) |
session_user
Description: Session username.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT session_user; session_user -------------- omm (1 row) |
Note: session_user usually specifies the initial user connected to the current database, but system administrators can change this setting by using SET SESSION AUTHORIZATION.
user
Description: Equivalent to current_user.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT user; current_user -------------- omm (1 row) |
getpgusername()
Description: Obtains the database username.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT getpgusername(); getpgusername --------------- GaussDB_userna (1 row) |
getdatabaseencoding()
Description: Obtains the database encoding mode.
Return type: name
Example:
1 2 3 4 5 |
gaussdb=# SELECT getdatabaseencoding(); getdatabaseencoding --------------------- SQL_ASCII (1 row) |
version()
Description: version information. version returns a string describing a server's version.
Return type: text
Example:
gaussdb=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------------------------------------------------------
gaussdb (GaussDB Kernel XXX.X.XXX build fab4f5ea) compiled at 2021-10-24 11:58:22 commit 3086 last mr 6592 release
(1 row)
opengauss_version()
Description: openGauss version information.
Return type: text
The following is an example. Replace x.x.x in the query result with the actual value.
1 2 3 4 5 |
gaussdb=# SELECT opengauss_version(); opengauss_version ------------------- x.x.x (1 row) |
gs_deployment()
Description: Information about the deployment mode of the current system.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT gs_deployment(); gs_deployment --------------------- BusinessCentralized (1 row) |
get_hostname()
Description: Returns the host name of the current node.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_hostname(); get_hostname -------------- linux-user (1 row) |
get_nodename()
Description: Returns the name of the current node.
Return type: text
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_nodename(); get_nodename -------------- datanode1 (1 row) |
get_nodeinfo(text)
Description: Returns the value of the corresponding node information based on the search attribute. Currently, the search attributes include node_name and node_type.
Return type: text
Example:
1 2 3 4 5 6 7 8 9 10 |
gaussdb=# SELECT get_nodeinfo('node_type'); get_nodeinfo -------------- CEN_DN (1 row) gaussdb=# SELECT get_nodeinfo('node_name'); get_nodeinfo -------------- datanode1 (1 row) |
get_schema_oid(cstring)
Description: Returns the OID of the queried schema.
Return type: OID
Example:
1 2 3 4 5 |
gaussdb=# SELECT get_schema_oid('public'); get_schema_oid ---------------- 2200 (1 row) |
get_client_info()
Description: Returns client information. In the multi-tenancy scenario, the global information is returned if this function is called in a non-PDB, and only the information about a local PDB is returned if this function is called in a PDB.
Return type: record
pv_session_memory_detail()
Description: Collects session memory usage by the MemoryContext node. In the multi-tenancy scenario, the global statistics are returned if this function is called in a non-PDB, and only the statistics about a local PDB are returned if this function is called in a PDB.
Return type: record
|
Name |
Type |
Description |
|---|---|---|
|
sessid |
text |
|
|
threadid |
bigint |
Thread ID bound to a session. |
|
contextname |
text |
Name of the memory context. |
|
level |
smallint |
Hierarchy of the memory context |
|
parent |
text |
Name of the parent memory context |
|
totalsize |
bigint |
Total size of the memory context, in bytes |
|
freesize |
bigint |
Total size of released memory in the current memory context, in bytes |
|
usedsize |
bigint |
Size of used memory in the memory context, in bytes. For TempSmallContextGroup, this parameter specifies the number of collected memory contexts. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
