System information functions

• 1: CURRENT_DATABASE
• 2: CURRENT_LOAD_SOURCE
• 3: CURRENT_SCHEMA
• 4: CURRENT_SESSION
• 5: CURRENT_TRANS_ID
• 6: CURRENT_USER
• 7: DBNAME (function)
• 8: HAS_TABLE_PRIVILEGE
• 9: LIST_ENABLED_CIPHERS
• 10: SESSION_USER
• 11: USER
• 12: USERNAME
• 13: VERSION

1 - CURRENT_DATABASE

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

Behavior type

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. With an optional integer argument, it returns the Nth /-delimited path part.

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

If the current load uses a UDSource function that does not set the URI, CURRENT_LOAD_SOURCE returns the string UNKNOWN. You cannot call CURRENT_LOAD_SOURCE(INT) when using a UDSource.

Behavior type

Syntax

CURRENT_LOAD_SOURCE( [ position ])

Arguments

position (positive INTEGER)

Path element to return instead of returning the full path. Elements are separated by slashes (/) and the first element is position 1. If the value is greater than the number of elements, the function returns an error. You cannot use this argument with a UDSource function.

Examples

The following load statement populates a column with the name of the file the row was loaded from:

=> 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)

The following example reads year and month columns out of a path:

=> COPY reviews
        (review_id, stars,
         year AS CURRENT_LOAD_SOURCE(3)::INT,
         month AS CURRENT_LOAD_SOURCE(4)::INT)
FROM '/data/reviews/*/*/*.json' PARSER FJSONPARSER();

3 - 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

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

• SET SEARCH_PATH

4 - CURRENT_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

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.

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

Behavior type

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.

Behavior type

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.

Behavior type

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.

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

Behavior type

Behavior type

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:

• SELECT: Query tables. SELECT privileges are granted by default to the PUBLIC role.

• INSERT: Insert table rows with INSERT, and load data with COPY.

  Note

  COPY FROM STDIN is allowed for users with INSERT privileges, while COPY FROM file requires admin privileges.

• UPDATE: Update table rows.

• DELETE: Delete table rows.

• REFERENCES: Create foreign key constraints on this table. This privilege must be set on both referencing and referenced tables.

• TRUNCATE: Truncate table contents. Non-owners of tables can also execute the following partition operations on them:

  • DROP_PARTITIONS
  • SWAP_PARTITIONS_BETWEEN_TABLES
  • MOVE_PARTITIONS_TO_TABLE

• ALTER: Modify a table's DDL with ALTER TABLE.

• DROP: Drop a table.

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.

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

• TLS protocol
• Security parameters

10 - SESSION_USER

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

Behavior type

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.

Behavior type

Syntax

USER()

Notes

• The USER function does not require parentheses.

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

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.

Behavior type

Syntax

USERNAME()

Notes

• This function is useful for permission checking.

• USERNAME is equivalent to CURRENT_USER, SESSION_USER and USER.

Examples

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

13 - VERSION

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

Behavior type

Syntax

VERSION()

Examples

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