System information functions

​

`current_database()

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

current_database() → *current_database_name*

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.

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

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.

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

SELECT current_schema(); → 'public'

​

current_user

Returns the name of the current effective user.

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

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.

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

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.

pg_get_viewdef(view_name) → text

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

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.

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

pg_typeof() → regtype

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.

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.

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.

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

SELECT session_user(); → `root`

​

user

Returns the name of the current database user.

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

SELECT user(); → `root`

​

version()

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

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