System information functions

RisingWave provides functions to help you get system information, including database, schema, user, role, session, and more.

current_database()

Returns the name of the current database. You can use this function in SQL commands, functions, and operators.

Syntax

current_database() → *current_database_name*

Examples

SELECT current_database(); →  `db_name`
SELECT count(current_database()); → 1

current_role

Returns the name of the role that the current user is acting as, if the current user is a member of one or more roles.

Syntax

current_role → *current_role_name* 
current_role() → *current_role_name*

Example

SELECT current_role(); → 'root'

current_shema

Returns the current schema. This is the schema that will be used for creating tables or other named objects without specifying a target schema.

Syntax

current_schema → *current_schema_name* 
current_schema() → *current_schema_name*

Example

SELECT current_schema(); → 'public'

current_user

Returns the name of the current effective user.

Syntax

current_user → *user_name*
current_user() → *user_name* 

Example

SELECT current_user(); → `root`

pg_get_keywords()

Returns a collection of records that provide information about the SQL keywords recognized by the server. Each record has three columns: word, catcode, and catdesc.

The word column contains the actual keyword, while the catcode column indicates the category code of the keyword.

Here are the possible category codes and their meanings:

• U: Indicates an unreserved keyword.

• C: Indicates a keyword that can be used as a column name.

• T: Indicates a keyword that can be used as a type or function name.

• R: Indicates a fully reserved keyword.

The catdesc column contains a string that describes the category of the keyword. This description may be localized, depending on the language settings of the server.

Syntax

pg_get_keywords () → setof record ( word text, catcode text, catdesc text)

Examples

SELECT * FROM pg_get_keywords() LIMIT 1;

----RESULT
 word    | catcode | catdesc 
---------+---------+---------
ABSOLUTE | R       | Reserved
(1 rows)

pg_get_viewdef()

Returns the definition of a specified view or materialized view.

Syntax

pg_get_viewdef(view_name) → text

Examples

-- Create a table
CREATE TABLE table1 (
    id int PRIMARY KEY,
    name VARCHAR 
);

-- Create a materialized view
CREATE MATERIALIZED VIEW materialized_view1 AS 
SELECT id, name 
FROM table1;

-- Retrieve the definition of the materialized view using pg_get_viewdef
SELECT pg_get_viewdef('materialized_view1'::regclass);
----RESULT
 SELECT id, name FROM table1
(1 row)

pg_index_column_has_property()

Checks if an index column has a specific property. The index parameter represents the OID of the index, while the column parameter represents the column number (starting from 1) within the index. If the property name is not recognized or doesn't apply to the object, or if the OID or column number is invalid, this function will return NULL.

Syntax

pg_index_column_has_property ( index regclass, column integer, property text ) → boolean

The supported properties are as follows:

• asc: Indicates whether the column sorts in ascending order on a forward scan.

• desc: Indicates whether the column sorts in descending order on a forward scan.

• nulls_first: Indicates whether the column sorts with nulls first on a forward scan.

• nulls_last: Indicates whether the column sorts with nulls last on a forward scan.

Examples

-- Create a table named 't' with columns 'a' and 'b' of type INT
CREATE TABLE t (a INT, b INT); 

-- Create an index named 'i' on table 't' with column 'a' in ascending order and column 'b' in descending order
CREATE INDEX i ON t (a ASC, b DESC);


-- Check if the first column of index 'i' has the 'ASC' property
SELECT pg_index_column_has_property('i'::REGCLASS, 1, 'ASC');
----RESULT
t

-- Check if the first column of index 'i' has the 'DESC' property
SELECT pg_index_column_has_property('i'::REGCLASS, 1, 'DESC');
----RESULT
f

pg_typeof()

Returns the standard name of the data type of the provided value. More specifically, it returns the OID of the data type of the provided value. It returns a regtype, which is an OID alias type. Therefore it’s the same as an OID for comparison purposes but displays as a type name.

Syntax

pg_typeof() → regtype

Examples

SELECT pg_typeof(round(null));  → `double precision`
SELECT pg_typeof(row(true, 1, 'hello')); → `record`
SELECT pg_typeof(array[1, 2]); → `integer[]`

pg_relation_size

Computes the disk space used by one “fork” of the specified relation.

Syntax

pg_relation_size ( relation regclass [, 'main' ] ) → bigint

Returns the size of the main data fork of the relation. This function can be used to determine if the main data fork of a relation exists. If its size is not 0, it means that the main data fork of the relation exists.

Examples

SELECT pg_relation_size('t') != 0; → t
SELECT pg_relation_size('t', 'main') != 0; → t

session_user

Returns the name of the current session user.

Syntax

session_user → *session_user_name*
session_user() → *session_user_name* 

Example

SELECT session_user(); → `root`

user

Returns the name of the current database user.

Syntax

user → *user_name*
user() → *user_name* 

Example

SELECT user(); → `root`

version()

Displays the PostgreSQL version and the RisingWave version implemented in the current instance of RisingWave.

Example

SELECT version ();
---------RESULT
 version 
-----------------
 PostgreSQL 8.3-RisingWave-1.0.0-alpha (76672d87cf5c20aa8fbb6f11996ef15255443b51)
(1 row)