Other Functions

ANY_VALUE

ANY_VALUE(ANY arg)

Description: Returns the first data record in a specified column in each group.

Return type: any type

Examples:

m_db=# CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE

m_db=# INSERT INTO t1 VALUES(1, NULL), (1, 18), (2, 17), (2, 22);
INSERT 0 4

m_db=# SELECT * FROM t1;
 c1 | c2
----+----
  1 |
  1 | 18
  2 | 17
  2 | 22
(4 rows)

m_db=# SELECT ANY_VALUE(c2) FROM t1 GROUP BY c1;
 any_value
-----------

        17
(2 rows)

m_db=# DROP TABLE t1;

ATTNAME_EQ_MYSQL

attname_eq_mysql(name1, name2)

Description: Returns true if name1 is equal to name2 (case-insensitive). Otherwise, returns false.

Return value type: BOOL

Examples:

1
2
3
4
5

m_db=# SELECT attname_eq_mysql('abc', 'ABC');
 attname_eq_mysql 
------------------
 t
(1 row)

BENCHMARK

BENCHMARK(INT count, expr)

Description: Measures the execution efficiency of an expression specified by expr by repeatedly calculating the expression for the number of times specified by count. In normal cases, the return value of the function is always 0. However, you can control the total execution time of BENCHMARK based on the execution time displayed on the client. The BENCHMARK function can measure only the performance of scalar expressions. An expression can be a subquery, but the result returned by the subquery must be a single value.

Return type: INT

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT DEFAULT 18);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# \timing on
Timing is on.
m_db=# SELECT BENCHMARK(1000000, ABS(age)) FROM employee;
 benchmark 
-----------
         0
         0
         0
         0
         0
(5 rows)

Time: 2769.498 ms
m_db=# \timing off
Timing is off.
m_db=# DROP TABLE employee;
DROP TABLE

COLLATION

COLLATION (TEXT str)

Description: Returns the collation of a specified string.

Return value type: TEXT

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

mdb=# SELECT COLLATION('Gauss');
     collation      
--------------------
 utf8mb4_general_ci
(1 row)

mdb=# SELECT COLLATION(_BINARY'Gauss');
 collation 
-----------
 binary
(1 row)

mdb=# SELECT COLLATION(_GBK'Gauss');
   collation    
----------------
 gbk_chinese_ci
(1 row)

CONNECTION_ID

CONNECTION_ID()

Description: Returns the session ID of the currently connected client.

Return type: BIGINT UNSIGNED

Examples:

m_db=# SELECT connection_id();
  connection_id  
-----------------
 140598681532160
(1 row)

DATABASE

DATEBASE()

Description: Returns the name of the current database (schema).

Return value type: TEXT

Examples:

m_db=# SELECT DATABASE();
 database 
----------
 public
(1 row)

DEFAULT

DEFAULT(col_name)

Description: Returns the default value of col_name in a specified column. If col_name has no default value and NOT NULL is not specified, NULL is returned. If col_name has no default value and NOT NULL is defined, an error is reported.

Return type: any type

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT DEFAULT 18);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT DEFAULT(age) FROM employee;
 default 
---------
      18
      18
      18
      18
      18
(5 rows)

m_db=# DROP TABLE employee;
DROP TABLE

FOUND_ROWS

FOUND_ROWS()

Description: Returns the number of rows in the execution result of the previous query statement of the current connection. When the previous query statement uses the keyword SQL_CALC_FOUND_ROWS, the FOUND_ROWS function returns the number of all rows even if the previous query statement contains the LIMIT clause. If the previous statement is not a SELECT statement, the return value of the FOUND_ROWS function is not defined.

Return type: BIGINT

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT * FROM employee;
 id |  name   | age 
----+---------+-----
  2 | Bob     |  36
  3 | John    |  25
  5 | Mary    |  25
  4 | Michael |  36
  1 | Tom     |  25
(5 rows)

m_db=# SELECT FOUND_ROWS();
 found_rows 
------------
          5
(1 row)

m_db=# SELECT * FROM employee LIMIT 3;
 id | name | age 
----+------+-----
  2 | Bob  |  36
  3 | John |  25
  5 | Mary |  25
(3 rows)

m_db=# SELECT FOUND_ROWS();
 found_rows 
------------
          3
(1 row)

m_db=# SELECT * FROM employee LIMIT 7;
 id |  name   | age 
----+---------+-----
  2 | Bob     |  36
  3 | John    |  25
  5 | Mary    |  25
  4 | Michael |  36
  1 | Tom     |  25
(5 rows)

m_db=# SELECT FOUND_ROWS();
 found_rows 
------------
          5
(1 row)

m_db=# SELECT SQL_CALC_FOUND_ROWS * FROM employee LIMIT 3;
 id | name | age 
----+------+-----
  2 | Bob  |  36
  3 | John |  25
  5 | Mary |  25
(3 rows)

m_db=# SELECT FOUND_ROWS();
 found_rows 
------------
          5
(1 row)

m_db=# DROP TABLE employee;
DROP TABLE

LAST_INSERT_ID

• last_insert_id()

  Description: Returns the first automatically generated value that is successfully inserted into the AUTO_INCREMENT column in the last INSERT statement executed in the current session.

  Return type: BIGINT UNSIGNED

  Examples:

  m_db=# CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT AUTO_INCREMENT UNIQUE) AUTO_INCREMENT=10;
  NOTICE:  CREATE TABLE will create implicit sequence "t1_c2_seq" for serial column "t1.c2"
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
  NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t1_c2_key" for table "t1"
  CREATE TABLE
                                    ^
  m_db=# INSERT INTO t1(c1) VALUES(1), (2), (3);
  INSERT 0 3
  
  m_db=# SELECT last_insert_id();
   last_insert_id 
  ----------------
   10
  (1 row)
  m_db=# DROP TABLE t1;
  DROP TABLE

• last_insert_id(expr)

  Description: Returns the value of the last_insert_id(expr) function as the return value for subsequent executions of the last_insert_id() function. If expr is NULL, last_insert_id (expr) returns NULL. If last_insert_id () is executed again, the return value is 0.

  Return type: BIGINT UNSIGNED

  Examples:

  m_db=# SELECT last_insert_id(100);
   last_insert_id 
  ----------------
   100
  (1 row)
  
  m_db=# SELECT last_insert_id();
   last_insert_id 
  ----------------
   100
  (1 row)

ROW_COUNT

ROW_COUNT()

Description: Returns the number of rows affected by the previous statement. For DDL statements, 0 is returned. For DML statements except SELECT, the actual number of affected rows is returned. For SELECT statements, –1 is returned, except for SELECT INTO, which returns the actual number of affected rows.

Return type: BIGINT

Examples:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT ROW_COUNT();
 row_count 
-----------
         5
(1 row)

m_db=# UPDATE employee SET age=26 WHERE age=25;
UPDATE 3
m_db=# SELECT ROW_COUNT();
 row_count 
-----------
         3
(1 row)

m_db=# DELETE FROM employee WHERE age=36;
DELETE 2
m_db=# SELECT ROW_COUNT();
 row_count 
-----------
         2
(1 row)

m_db=# DROP TABLE employee;
DROP TABLE

SCHEMA

SCHEMA()

Description: Returns the name of the current schema (database).

Return value type: TEXT

Examples:

m_db=# SELECT SCHEMA();
 schema
--------
 public
(1 row)

SLEEP

SLEEP(INT duration)

Description: Causes the session executing this function to sleep for the specified number of seconds.

Return type: BIGINT

Examples:

1
2
3
4
5

m_db=# SELECT SLEEP(1);
 sleep 
-------
     0
(1 row)

SYSTEM_USER

SYSTEM_USER()

Description: Returns a UTF8-encoded string that contains the username of the current account used by the current client and the IP address of the current client host. The format is username@IP address.

Return value type: TEXT

Examples:

1
2
3
4
5

m_db=# SELECT SYSTEM_USER();
  system_user  
---------------
 oms@localhost
(1 row)

UUID

UUID()

Description: Returns a universally unique identifier (UUID) defined in RFC 4122, ISO/IEF 9834-8:2005, and related standards. The identifier is a string consisting of lowercase hexadecimal digits. The string consists of a group of 8-bit digits, three groups of 4-bit digits, and a group of 12-bit digits. A total of 32 digits represent 128 bits.

Return value type: VARCHAR

Examples:

m_db=# SELECT UUID();
                 uuid                 
--------------------------------------
 b28d6aaa-7a3a-c03c-f812-fa163e6cc336
(1 row)

UUID_SHORT

UUID_SHORT()

Description: Returns a short UUID under certain conditions. This identifier is a 64-bit unsigned integer.

The returned value is unique when the following conditions are met:

• The number of service nodes in the current cluster cannot exceed 256.

• You cannot set the system time of the server host between node restarts.

• The average number of UUID_SHORT() calls per second between node restarts is less than 16 million.

Return value type: UINT64

Examples:

1
2
3
4
5

m_db=# SELECT UUID_SHORT();
      uuid_short      
----------------------
 13863614461119561729
(1 row)