PostgreSQL: System Information Functions

Introduction

Here we have discussed several functions that extract session and system information. In addition to the functions listed in this section, there are a number of functions related to the statistics system that also provides system information.

current_catalog

current_catalog returns the name of current database (called "catalog" in the SQL standard)

Syntax:

current_catalog

Return Type

name

Example

postgres=# SELECT current_catalog;
 current_database
------------------
postgres
(1 row)

current_database() function

The current_database() function returns the name of current database.

Syntax:

current_database()

Return Type

name

Example

postgres=# SELECT current_database();
current_database
------------------
postgres
(1 row)

current_query() function

The current_query() functions returns the text of the currently executing query, as submitted by the client (might contain more than one statement)

Syntax:

current_query()

Return Type

text

Example

postgres=# SELECT current_query();
current_query
-------------------------
SELECT current_query();
(1 row)

current_schema() function

The current_schema()\ function returns the name of current schema.

Syntax:

current_schema()

Return Type

name

Example

postgres=# SELECT current_schema();

current_schema

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

public

(1 row)

current_schemas(boolean) function

The current_schemas() function returns names of schemas in search path, optionally including implicit schemas

Syntax :

current_schemas(boolean)

Return type

name[ ]

Example

postgres=# SELECT current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)

current_user function

current_user returns user name of current execution context

Syntax:

current_user

Return type

name

Example

postgres=# SELECT current_user;
current_user
--------------
postgres
(1 row)

inet_client_addr() function

The inet_client_addr() function returns the address of the remote connection.

Syntax:

inet_client_addr()

Return type

inet

Example

postgres=# SELECT inet_client_addr();
inet_client_addr
------------------
::1
(1 row)

inet_server_port() function

The inet_server_port() function returns the port of the local connection.

Syntax:

inet_server_port()

Return type

int

Example

postgres=# SELECT inet_server_port();
inet_server_port
------------------
5432
(1 row)

pg_backend_pid() function

The pg_backend_pid() function returns the process ID of the server process attached to the current session.

Syntax:

pg_backend_pid()

Return type

int

Example

postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
952
(1 row)

pg_conf_load_time() function

The pg_conf_load_time() function returns the configuration load time .

Syntax/:

pg_conf_load_time()

Return type

timestamp with time zone

Example

postgres=# SELECT pg_conf_load_time();
pg_conf_load_time
----------------------------
 2014-12-05 12:09:54.569-08
(1 row)

pg_is_other_temp_schema(oid) function

The pg_is_other_temp_schema(oid) function is used to test a schema is another session's temporary schema?

Syntax:

pg_is_other_temp_schema(oid)

Return type

boolean

Example

postgres=# SELECT pg_is_other_temp_schema(0);
pg_is_other_temp_schema
-------------------------
 f
(1 row)

pg_listening_channels() function

The pg_listening_channels() function returns the channel names that the session is currently listening on

Syntax:

pg_listening_channels()

Return type

setof text

Example

postgres=# SELECT pg_listening_channels();
pg_listening_channels
-----------------------
(0 rows)

pg_my_temp_schema()function

OID of session's temporary schema, or 0 if none

Syntax:

SELECT pg_my_temp_schema()

Return type

oid

Example

postgres=# SELECT pg_my_temp_schema();
pg_my_temp_schema
-------------------
                 0
(1 row)

pg_postmaster_start_time() function

The pg_postmaster_start_time() function returns server start time.

Syntax:

pg_postmaster_start_time()

Return type

timestamp with time zone

Example

postgres=# SELECT pg_postmaster_start_time();
pg_postmaster_start_time
----------------------------
 2014-12-05 12:09:55.583-08
(1 row)

pg_trigger_depth() function

The pg_trigger_depth() function returns current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

Syntax:

pg_trigger_depth()

Return type

int

Example

postgres=# SELECT pg_trigger_depth();
pg_trigger_depth
------------------
                0
(1 row)

session_user() function

The session_user() function returns session user name.

Syntaxp:

session_user

Return type

name

Example

postgres=# SELECT session_user;
session_user
--------------
 postgres
(1 row)

user function

equivalent to current_user

Syntax:

user

Return type

name

Example

postgres=# SELECT user;
current_user
--------------
 postgres
(1 row)

version() function

The version() function returns the PostgreSQL version information.

Syntax:

version()

Return type

text

Example

postgres=# SELECT version();
version
-------------------------------------------------------------
 PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 32-bit
(1 row)

Previous: Enum Support Functions
Next: String Operators