This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

System information functions

These functions provide information about the current system state.

These functions provide information about the current system state. A superuser has unrestricted access to all system information, but users can view only information about their own, current sessions.

1 - CURRENT_DATABASE

Returns the name of the current database, equivalent to DBNAME.

Returns the name of the current database, equivalent to DBNAME.

Behavior type

Stable

Syntax

CURRENT_DATABASE()

Examples

=> SELECT CURRENT_DATABASE;
 CURRENT_DATABASE
------------------
 VMart
(1 row)

2 - CURRENT_LOAD_SOURCE

When called within the scope of a COPY statement, returns the file name used for the load.

When called within the scope of a COPY statement, returns the file name used for the load. The following exceptions apply:

  • If the function is called outside of the context of a COPY statement, it returns NULL.

  • If the function is called by a UDL that does not set the source, it returns the string <unknown>.

This function is not supported for COPY LOCAL.

Behavior type

Stable

Syntax

CURRENT_LOAD_SOURCE()

Examples

Create a table and populate one of its columns with the names of two separate files as they are loaded:

=> CREATE TABLE t (c1 integer, c2 varchar(50), c3 varchar(200));
CREATE TABLE

=> COPY t (c1, c2, c3 AS CURRENT_LOAD_SOURCE())
   FROM '/home/load_file_1' ON exampledb_node02,
        '/home/load_file_2' ON exampledb_node03 DELIMITER ',';
Rows Loaded
-------------
5
(1 row)

=> SELECT * FROM t;
c1  |      c2      |          c3
----+--------------+-----------------------
2   |  dogs        | /home/load_file_1
1   |  cats        | /home/load_file_1
4   |  superheroes | /home/load_file_2
3   |  birds       | /home/load_file_1
5   |  whales      | /home/load_file_2
(5 rows)

3 - CURRENT_SCHEMA

Returns the name of the current schema.

Returns the name of the current schema.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Stable

Syntax

CURRENT_SCHEMA()

Privileges

None

Examples

The following command returns the name of the current schema:

=> SELECT CURRENT_SCHEMA();
 current_schema
----------------
 public
(1 row)

The following command returns the same results without the parentheses:

=> SELECT CURRENT_SCHEMA;
 current_schema
----------------
 public
(1 row)

The following command shows the current schema, listed after the current user, in the search path:

=> SHOW SEARCH_PATH;
    name     |                      setting
-------------+---------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

See also

4 - CURRENT_SESSION

Returns the ID of the current client session.

Returns the ID of the current client session.

Many system tables have a SESSION_ID column. You can use the CURRENT_SESSION function in queries of these tables.

Behavior type

Stable

Syntax

CURRENT_SESSION()

Examples

Each new session has a new session ID:

$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

=> SELECT CURRENT_SESSION();
    CURRENT_SESSION
-----------------------
 initiator-24897:0x1f7
(1 row)
=> \q

$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

=> SELECT CURRENT_SESSION();
    CURRENT_SESSION
-----------------------
 initiator-24897:0x200
(1 row)

5 - CURRENT_TRANS_ID

Returns the ID of the transaction currently in progress.

Returns the ID of the transaction currently in progress.

Many system tables have a TRANSACTION_ID column. You can use the CURRENT_TRANS_ID function in queries of these tables.

Behavior type

Stable

Syntax

CURRENT_TRANS_ID()

Examples

Even a new session has a transaction ID:

$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.
=> SELECT CURRENT_TRANS_ID();
 current_trans_id
-------------------
 45035996273705927
(1 row)

This function can be used in queries of certain system tables. In the following example, a load operation is in progress:

=> SELECT key, SUM(num_instances) FROM v_monitor.UDX_EVENTS
   WHERE event_type = 'UNMATCHED_KEY'
   AND transaction_id=CURRENT_TRANS_ID()
   GROUP BY key;
          key           | SUM
------------------------+-----
 chain                  |   1
 menu.elements.calories |   7
(2 rows)

6 - CURRENT_USER

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Behavior type

Stable

Syntax

CURRENT_USER()

Notes

  • The CURRENT_USER function does not require parentheses.

  • This function is useful for permission checking.

  • CURRENT_USER is equivalent to SESSION_USER, USER, and USERNAME.

Examples

=> SELECT CURRENT_USER();
 CURRENT_USER
--------------
 dbadmin
(1 row)

The following command returns the same results without the parentheses:

=> SELECT CURRENT_USER;
 CURRENT_USER
--------------
 dbadmin
(1 row)

7 - DBNAME (function)

Returns the name of the current database, equivalent to CURRENT_DATABASE.

Returns the name of the current database, equivalent to CURRENT_DATABASE.

Behavior type

Immutable

Syntax

DBNAME()

Examples

=> SELECT DBNAME();
      dbname
------------------
 VMart
(1 row)

8 - HAS_TABLE_PRIVILEGE

Returns true or false to verify whether a user has the specified privilege on a table.

Returns true or false to verify whether a user has the specified privilege on a table.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Behavior type

Stable

Syntax

HAS_TABLE_PRIVILEGE ( [ user, ] '[[database.]schema.]table', 'privilege' )

Parameters

user
Name or OID of a database user. If omitted, Vertica checks privileges for the current user.
[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table
Name or OID of the table to check.
privilege
A table privilege, one of the following:

Privileges

Non-superuser, one of the following:

  • Table owner

  • USAGE privilege on the table schema and one or more privileges on the table

Examples

=> SELECT HAS_TABLE_PRIVILEGE('store.store_dimension', 'SELECT');
 HAS_TABLE_PRIVILEGE
---------------------
 t
(1 row)

=> SELECT HAS_TABLE_PRIVILEGE('release', 'store.store_dimension', 'INSERT');
 HAS_TABLE_PRIVILEGE
---------------------
 t
(1 row)

=> SELECT HAS_TABLE_PRIVILEGE(45035996273711159, 45035996273711160, 'select');
 HAS_TABLE_PRIVILEGE
---------------------
t
(1 row)

9 - LIST_ENABLED_CIPHERS

Returns a list of enabled cipher suites, which are sets of algorithms used to secure TLS/SSL connections.

Returns a list of enabled cipher suites, which are sets of algorithms used to secure TLS/SSL connections.

By default, Vertica uses OpenSSL's default cipher suites. For more information, see the OpenSSL man page.

Syntax

LIST_ENABLED_CIPHERS()

Examples

=> SELECT LIST_ENABLED_CIPHERS();
SSL_RSA_WITH_RC4_128_MD5
SSL_RSA_WITH_RC4_128_SHA
TLS_RSA_WITH_AES_128_CBC_SHA

See also

10 - SESSION_USER

Returns a VARCHAR containing the name of the user who initiated the current database session.

Returns a VARCHAR containing the name of the user who initiated the current database session.

Behavior type

Stable

Syntax

SESSION_USER()

Notes

  • The SESSION_USER function does not require parentheses.

  • SESSION_USER is equivalent to CURRENT_USER, USER, and USERNAME.

Examples

=> SELECT SESSION_USER();
 session_user
--------------
 dbadmin
(1 row)

The following command returns the same results without the parentheses:

=> SELECT SESSION_USER;
 session_user
--------------
 dbadmin
(1 row)

11 - USER

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Behavior type

Stable

Syntax

USER()

Notes

Examples

=> SELECT USER();
 current_user
--------------
 dbadmin
(1 row)

The following command returns the same results without the parentheses:

=> SELECT USER;
 current_user
--------------
 dbadmin
(1 row)

12 - USERNAME

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Returns a VARCHAR containing the name of the user who initiated the current database connection.

Behavior type

Stable

Syntax

USERNAME()

Notes

Examples

=> SELECT USERNAME();
 username
--------------
 dbadmin
(1 row)

13 - VERSION

Returns a VARCHAR containing a Vertica node's version information.

Returns a VARCHAR containing a Vertica node's version information.

Behavior type

Stable

Syntax

VERSION()

Examples

=> SELECT VERSION();
                    VERSION
-------------------------------------------
Vertica Analytic Database v10.0.0-0
(1 row)