Platform and Client Compatibility

enable_set_variables

Specifies whether to enable the enhancement of the SET syntax.

• If this parameter is not set, user-defined variables and the SET [GLOBAL | SESSION] syntax are not supported.
• If this parameter is set, the preceding syntax is supported in the B-compatible mode, for example, set @v1 = 1;.

set_session_transaction

Specifies whether to enable the SET SESSION TRANSACTION syntax.

• If this parameter is not set, SET SESSION TRANSACTION is equivalent to SET LOCAL TRANSACTION.
• If this parameter is set, the preceding syntax can be used in the B-compatible mode to modify the transaction features in the current session.

enable_modify_column

Specifies whether to enable the ALTER TABLE MODIFY syntax.

• If this parameter is not set, running ALTER TABLE table_name MODIFY column_name data_type; can change only the data type of the column.
• If this parameter is set, running ALTER TABLE table_name MODIFY column_name data_type; can change the entire column definition.

default_collation

Specifies whether to enable forward compatibility of the default collation.

• If this item is not specified, the character set or collation of a column of the character type is not explicitly specified, and the collation of a table is empty, the column uses the default collation.
• If this item is specified, the collation of a column of the character type inherits the collation of a table (if it is not empty). If the collation of a table is empty, the default collation corresponding to the database is used.

all

Determines whether to enable all syntax.

all cannot be specified together with other configuration items. In the table, specifying all configuration items except all that are separated by commas is equivalent to specifying all.

enable_escape_string

Specifies whether escape character control is enabled.

• If this item is not set, escape character behaviors are controlled by the GUC parameters standard_conforming_strings, escape_string_warning, and backslash_quote related to the GaussDB escape character.
• If this item is set, all MySQL escape characters except '\0' are supported by default. In addition, the output behavior of '\b', '\r', and '\Z' on the gsql client changes to be the same as that on the MySQL client.

display_leading_zero

Specifies how floating-point numbers are displayed. It determines the display of zeros before the decimal point of all character string types (such as char, character, nchar, varchar, character varying, varchar2, nvarchar2, text, and clob) and any precision types (such as float4, float8, and numeric) in the numeric type. In addition, the length of the calculated number is displayed synchronously.

• If this item is not specified, for a non-zero decimal number between –1 and +1, the 0 before the decimal point is not displayed. For example:

  1 2 3 4 5

  gaussdb=# select 0.1231243 as a, 0.1231243::numeric as b,0.1231243::integer(10,3) as c, length(0.1242343) as d; a | b | c | d ----------+----------+------+--- .1231243 | .1231243 | .123 | 8 (1 row)

• If this item is specified, for a non-zero decimal number between –1 and +1, the 0 before the decimal point is displayed. For example:

  1 2 3 4 5

  gaussdb=# select 0.1231243 as a, 0.1231243::numeric as b,0.1231243::integer(10,3) as c, length(0.1242343) as d; a | b | c | d -----------+-----------+-------+--- 0.1231243 | 0.1231243 | 0.123 | 9 (1 row)

end_month_calculate

Specifies the calculation logic of the add_months function.

Assume that the two parameters of the add_months function are param1 and param2, and that the month of param1 and param2 is result.

• If this item is not specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in param1. For example:

  1 2 3 4 5

  gaussdb=# select add_months('2018-02-28',3) from sys_dummy; add_months --------------------- 2018-05-28 00:00:00 (1 row)

• If this item is specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in result. For example:

  1 2 3 4 5

  gaussdb=# select add_months('2018-02-28',3) from sys_dummy; add_months --------------------- 2018-05-31 00:00:00 (1 row)

compat_analyze_sample

Specifies the sampling behavior of the ANALYZE operation.

If this item is specified, the sample collected by the ANALYZE operation will be limited to around 30,000 records, controlling database node memory consumption and maintaining the stability of ANALYZE.

bind_schema_tablespace

Binds a schema with the tablespace with the same name.

If a tablespace name is the same as sche_name, default_tablespace will also be set to sche_name if search_path is set to sche_name.

bind_procedure_searchpath

Specifies the search path of the database objects in a stored procedure for which no schema name is specified.

If no schema name is specified for a stored procedure, the schema to which the stored procedure belongs is searched preferentially.

If the stored procedure is not found, the following operations are performed:

• If this item is not specified, the system reports an error and exits.
• If this item is specified, the search continues based on the settings of search_path. If the issue persists, the system reports an error and exits.

correct_to_number

Specifies the compatibility of the to_number() result.

• If this item is not specified, the result of the to_number() function is the same as that in the A database by default.

  1 2 3

  gaussdb=# select '' AS to_number_14, to_number('34,50','999,99'); ERROR: invalid data. CONTEXT: referenced column: to_number

• If this item is specified, the result of the to_number() function is the same as that of pg11.

  1 2 3 4 5

  gaussdb=# select '' AS to_number_14, to_number('34,50','999,99'); to_number_14 | to_number --------------+----------- | 3450 (1 row)

unbind_divide_bound

Specifies the range check on the result of integer division.

• If this item is not specified, the range of the division result is verified. For example, an out-of-bounds error is reported because the output result of INT_MIN/(–1) is greater than INT_MAX.

  1 2	gaussdb=# select (-2147483648)::int4 / (-1)::int4; ERROR: integer out of range

• If this item is specified, the range of the division result does not need to be verified. For example, the output result of INT_MIN/(–1) is INT_MAX+1.

  1 2 3 4 5

  gaussdb=# select (-2147483648)::int4 / (-1)::int4; ?column? ------------ 2147483648 (1 row)

convert_string_digit_to_numeric

Determines whether to convert numeric constants of the character string type to those of the numeric type before these two types are compared.

• If this item is not specified, the numeric constants of the character string type are not converted to those of the numeric type.
• If this item is specified, the numeric constants of the character string type are converted to those of the numeric type.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

gaussdb=# create table test1 (c1 int, c2 varchar);
gaussdb=# insert into test1 values (2, '1.1');
gaussdb=# set behavior_compat_options='';
gaussdb=# select * from test1 where c2 > 1;
ERROR:  invalid input syntax for type bigint: "1.1"

gaussdb=# set behavior_compat_options='convert_string_digit_to_numeric';
gaussdb=# select * from test1 where c2 > 1;
 c1 | c2  
----+-----
  2 | 1.1 
(1 row)

return_null_string

Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions.

• If this item is not specified, the empty string is displayed as NULL.

  1 2 3 4 5

  gaussdb=# select length(lpad('123',0,'*')) from sys_dummy; length -------- (1 row)

• If this item is specified, the empty string is displayed as single quotation marks ('').

  1 2 3 4 5

  gaussdb=# select length(lpad('123',0,'*')) from sys_dummy; length -------- 0 (1 row)

compat_concat_variadic

Specifies the compatibility of variadic results of the concat() and concat_ws() functions. The B database does not have the variadic type. Therefore, this option has no impact on the B database.

• If this item is not specified and the concat function parameter is of the variadic type, the results of the A and C databases in compatibility mode are the same by default.

  1 2 3 4 5

  gaussdb=# select concat(variadic NULL::int[]) is NULL; ?column? ---------- t (1 row)

• If this item is specified and the concat function parameter is of the variadic type, different result formats of the A and C databases in compatibility mode are retained.

  1 2 3 4 5 6 7 8 9 10 11 12

  --In the A database: gaussdb=# select concat(variadic NULL::int[]) is NULL; ?column? ---------- t (1 row) --In the C database: gaussdb=# select concat(variadic NULL::int[]) is NULL; ?column? ---------- f (1 row)

merge_update_multi

When MERGE INTO ... WHEN MATCHED THEN UPDATE (see "SQL Reference > SQL Syntax > MERGE INTO" in Developer Guide) and INSERT ... ON DUPLICATE KEY UPDATE (see "SQL Reference > SQL Syntax > INSERT" in Developer Guide) are used, it controls the UPDATE behavior if a piece of target data in the target table conflicts with multiple pieces of source data.

• If this item is specified and the preceding scenario exists, the system performs multiple UPDATE operations on the conflicting row.
• If this item is not specified (the default value is retained), an error is reported, indicating that the MERGE or INSERT operation fails.

plstmt_implicit_savepoint

Determines whether the execution of an UPDATE statement in a stored procedure has an independent subtransaction.

If this parameter is set, the implicit savepoint is enabled before executing each UPDATE statement in the stored procedure, and the subtransaction is rolled backed to the latest savepoint in the EXCEPTION block by default, ensuring that only the modification of failed statements is rolled back. This option is used to be compatible with the EXCEPTION behavior of the ORA database.

hide_tailing_zero

Configuration item for numeric display.

• If this item is specified, the trailing zeros after the decimal point are hidden in all scenarios where numeric values are output, even if the precision format is specified.
• If this item is not specified, numeric data is displayed in the specified precision.

gaussdb=# set behavior_compat_options='hide_tailing_zero';
gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999');
    a    | to_char
---------+----------
 123.123 |  123.123
(1 row)
gaussdb=# set behavior_compat_options='';
gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999');
       a        |   to_char
----------------+-------------
 123.1230000000 |  123.123000
(1 row)

rownum_type_compat

Specifies the ROWNUM type. The default value is BIGINT. After this parameter is specified, the value is changed to NUMERIC.

gaussdb=# set behavior_compat_options='';
gaussdb=# create table tb_test(c1 int,c2 varchar2,c3 varchar2);
gaussdb=# insert into tb_test values(1,'a','b');
gaussdb=# create or replace view v_test as select rownum from tb_test;
gaussdb=# \d+ v_test
                View "public.v_test"
 Column |  Type  | Modifiers | Storage | Description 
--------+--------+-----------+---------+-------------
 rownum | bigint |           | plain   | 
View definition:
 SELECT ROWNUM AS "rownum"
   FROM tb_test;

gaussdb=# set behavior_compat_options = 'rownum_type_compat';
gaussdb=# create or replace view v_test1 as select rownum from tb_test;
gaussdb=# \d+ v_test1
                View "public.v_test1"
 Column |  Type   | Modifiers | Storage | Description 
--------+---------+-----------+---------+-------------
 rownum | numeric |           | main    | 
View definition:
 SELECT ROWNUM AS "rownum"
   FROM tb_test;

aformat_null_test

Specifies the logic for checking whether rowtype is not null.

When this parameter is set, if one column in a row is not empty, true is returned for checking whether rowtype is not null. When this parameter is not set, if all columns in a row are not empty, true is returned for checking whether rowtype is not null. This parameter has no influence on checking whether rowtype is not null.

gaussdb=# set behavior_compat_options='aformat_null_test';
gaussdb=# select r, r is null as isnull, r is not null as isnotnull from (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
      r      | isnull | isnotnull 
-------------+--------+-----------
 (1,"(1,2)") | f      | t
 (1,"(,)")   | f      | t
 (1,)        | f      | t
 (,"(1,2)")  | f      | t
 (,"(,)")    | f      | t
 (,)         | t      | f
(6 rows)
gaussdb=# set behavior_compat_options='';
gaussdb=# select r, r is null as isnull, r is not null as isnotnull from (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
      r      | isnull | isnotnull 
-------------+--------+-----------
 (1,"(1,2)") | f      | t
 (1,"(,)")   | f      | t
 (1,)        | f      | f
 (,"(1,2)")  | f      | f
 (,"(,)")    | f      | f
 (,)         | t      | f
(6 rows)

aformat_regexp_match

Determines the matching behavior of regular expression functions.

Otherwise, the meanings of the options supported by the flags parameter of the regular expression are as follows:

1. By default, the character '\n' can be matched.
2. The n option in flags indicates that the multi-line matching mode is used.
3. The regexp_replace(source, pattern replacement) function replaces only the first matched substring.
4. If the value of flags is '' or null, the return value of regexp_replace(source, pattern, replacement, flags) is the character string after replacement.

compat_cursor

Determines the compatibility behavior of implicit cursor states. If this parameter is set and the A-compatible mode is used, the effective scope of implicit cursor states (SQL%FOUND, SQL%NOTFOUND, SQL%ISOPNE, and SQL%ROWCOUNT) is extended from only the currently executed function to all subfunctions called by this function.

proc_outparam_override

Determines the overloading of output parameters of a stored procedure. After this parameter is enabled, the stored procedure can be properly created and called even if only the output parameters of the stored procedure are different. Currently, this parameter can be used only when gsql and JDBC are used to connect to the database. If this parameter is enabled for other tools to connect to the database, stored procedures with the out parameter cannot be called.

It supports the functions that contain the out output parameter and returns data of the record type. Besides, a value is assigned to the out parameter.

proc_implicit_for_loop_variable

Determines the behavior of the FOR_LOOP query statement in a stored procedure. When this parameter is set, if rec has been defined in the FOR rec IN query LOOP statement, the defined rec variable is not reused but a new variable is created. Otherwise, the defined rec variable is reused and no variable is created.

allow_procedure_compile_check

Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure. If this parameter is set, when the SELECT, OPEN CURSOR FOR, CURSOR%rowtype, or FOR rec IN statement is executed in a stored procedure, the stored procedure cannot be created if the queried table does not exist, and the compilation check of the trigger function is not supported. If the queried table exists, the stored procedure is successfully created.

Note: When creating an encrypted function, you need to disable allow_procedure_compile_check.

char_coerce_compat

Controls the behavior when char(n) types are converted to other variable-length string types. This parameter is valid only when the sql_compatibility parameter is set to A. After this parameter is enabled, spaces at the end are not omitted in implicit conversion, explicit conversion, or conversion by calling the text(bpchar) function.

• If this item is specified, spaces at the end are not omitted in conversion. In addition, if the length of the char(n) type exceeds that of other variable-length string types, an error is reported.
• If this item is not specified, spaces at the end are omitted when the char(n) type is converted to another variable-length string type.

gaussdb=# set behavior_compat_options='';
gaussdb=# create table tab_1(col1 varchar(3)); 
gaussdb=# create table tab_2(col2 char(3));
gaussdb=# insert into tab_2 values('   ');
gaussdb=# insert into tab_1 select col2 from tab_2;
gaussdb=# select * from tab_1 where col1 is null;
 col1 
------
 
(1 row)
gaussdb=# select * from tab_1 where col1='   ';
 col1 
------
(0 rows)
gaussdb=# delete from tab_1;
gaussdb=# set behavior_compat_options = 'char_coerce_compat';
gaussdb=# insert into tab_1 select col2 from tab_2;
gaussdb=# select * from tab_1 where col1 is null;
 col1 
------
(0 rows)
gaussdb=# select * from tab_1 where col1='   ';
 col1 
------
    
(1 row)

truncate_numeric_tail_zero

Configuration item for numeric display.

• If this item is specified, the trailing zeros after the decimal point are hidden in all numeric output scenarios except to_char(numeric, format).
• If this item is not specified, numeric data is displayed in the default precision.

For example:

gaussdb=# set behavior_compat_options='truncate_numeric_tail_zero';
gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999');
    a    |   to_char
---------+-------------
 123.123 |  123.123000
(1 row)
gaussdb=# set behavior_compat_options='';
gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999');
       a        |   to_char
----------------+-------------
 123.1230000000 |  123.123000
(1 row)

plsql_security_definer

After this parameter is enabled, the definer permission is used by default when a stored procedure is created.

plpgsql_dependency

If this parameter is set, a function, stored procedure, or package containing undefined objects can be created. You can query the dependency in GS_DEPENDENCIES and GS_DEPENDENCIES_OBJ.

If this parameter is enabled, when creating a PL/SQL object, the OID that depends on the PL/SQL object is automatically updated.

disable_rewrite_nesttable

If this parameter is enabled, rewriting columns of the tableof type in the pg_type table will be disabled. That is, when you read the pg_type table, the actual stored value of the tableof type is displayed.

skip_insert_gs_source

If this parameter is enabled, no data is inserted into the dbe_pldeveloper.gs_source table when the PL/SQL objects are created.

disable_emptystr2null

In the A-compatible parameter binding scenario, if this parameter is enabled, the function of converting the values of parameters of the character type from an empty string to null by default, if any, is disabled. text, clob, blob, raw, bytea, varchar, nvarchar2, bpchar, char, name, byteawithoutorderwithqualcol, and byteawithoutordercol. This parameter is reserved for emergency. Do not set it unless necessary.

select_into_return_null

This parameter takes effect only in PG-compatible mode. If it is enabled, NULL values can be assigned to the variables in the stored procedure statement SELECT select_expressions INTO [STRICT] target FROM... without specifying STRICT when the query result is empty.

proc_uncheck_default_param

When a function is called, the system does not check whether the default parameter is omitted.

• If this item is not specified and a function with default parameters is called, input parameters are added to the function from left to right. If inputs of non-default parameters are missing, an error is reported. For example:

  gaussdb=# create or replace function test(f1 int, f2 int default 20, f3 int, f4 int default 40, f5 int default 50) return int
  gaussdb-# as
  gaussdb$# begin
  gaussdb$# raise info 'f1:%',f1;
  gaussdb$# raise info 'f2:%',f2;
  gaussdb$# raise info 'f3:%',f3;
  gaussdb$# raise info 'f4:%',f4;
  gaussdb$# raise info 'f5:%',f5;
  gaussdb$# return 1;
  gaussdb$# end;
  gaussdb$# /
  CREATE FUNCTION
  gaussdb=# select test(1,2);
  ERROR:  function test(integer, integer) does not exist
  LINE 1: select test(1,2);
                 ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  CONTEXT:  referenced column: test

• If this item is specified and a function with default parameters is called, input parameters are added to the function from left to right. The number of defaulted inputs depends on the number of default parameters. If an input of a non-default parameter is missing, the previous default value is used to fill this parameter. For example:

  gaussdb=# create or replace function test(f1 int, f2 int default 20, f3 int, f4 int default 40, f5 int default 50) return int
  gaussdb-# as
  gaussdb$# begin
  gaussdb$# raise info 'f1:%',f1;
  gaussdb$# raise info 'f2:%',f2;
  gaussdb$# raise info 'f3:%',f3;
  gaussdb$# raise info 'f4:%',f4;
  gaussdb$# raise info 'f5:%',f5;
  gaussdb$# return 1;
  gaussdb$# end;
  gaussdb$# /
  CREATE FUNCTION
  gaussdb=# select test(1,2);
  INFO:  f1:1
  CONTEXT:  referenced column: test
  INFO:  f2:2
  CONTEXT:  referenced column: test
  INFO:  f3:20
  CONTEXT:  referenced column: test
  INFO:  f4:40
  CONTEXT:  referenced column: test
  INFO:  f5:50
  CONTEXT:  referenced column: test
   test 
  ------
      1
  (1 row)

  As shown above, f3 is filled with an incorrect default value.

  WARNING:

  In this scenario, a non-default parameter is filled with the previous default value.

dynamic_sql_compat

After this parameter is enabled:

1. Duplicate parameters in the SQL statement template are not regarded as the same parameter, but match variables in the USING clause in sequence.

2. If a stored procedure is called during dynamic statement execution, the IN and OUT attributes in a stored procedure and the USING clause are not checked.

dynamic_sql_check

After this parameter is enabled, an error is reported during dynamic statement execution if the number of different template parameters in the dynamic statement template SQL is different from that of variables in the USING clause.

enable_funcname_with_argsname

If the parameter is enabled, the projection alias displays the complete function when SELECT is used to call a function.

• If this item is not specified, the projection alias displays only the function name when SELECT is used to call a function. For example:

  gaussdb=# select power(2,3);
   power 
  -------
       8
  (1 row)
  
  gaussdb=# select count(*) from db_ind_columns;
   count 
  -------
     611
  (1 row)
  
  gaussdb=# select count(index_name) from db_ind_columns;
   count 
  -------
     611
  (1 row)
  
  gaussdb=# SELECT left('abcde', 2);
   left 
  ------
   ab
  (1 row)
  
  gaussdb=# SELECT pg_client_encoding();
   pg_client_encoding 
  --------------------
   UTF8
  (1 row)

• If this item is specified, the projection alias displays the complete function when SELECT is used to call a function. For example:

  gaussdb=# set behavior_compat_options = 'enable_funcname_with_argsname';
  SET
  gaussdb=# select power(2,3);
   power(2,3) 
  ------------
            8
  (1 row)
  
  gaussdb=# select count(*) from db_ind_columns;
   count(*) 
  ----------
        611
  (1 row)
  
  gaussdb=# select count(index_name) from db_ind_columns;
   count(index_name) 
  -------------------
                 611
  (1 row)
  
  gaussdb=# SELECT left('abcde', 2);
   left('abcde',2) 
  -----------------
   ab
  (1 row)
  
  gaussdb=# SELECT pg_client_encoding();
   pg_client_encoding() 
  ----------------------
   UTF8
  (1 row)

  CAUTION:

  • Currently, only func_name(args_list), func_name(), and func_name(*) can be used to display complete functions and the arguments can only be character strings, numeric values, column names, and functions. A function name can contain a schema or package name. The parameter cannot contain other clauses (such as the ORDER BY clause) or be an expression. The parameter can contain only the DISTINCT keyword. If the parameter contains other keywords, complete functions cannot be displayed.
  • Some special functions do not support displaying projection alias, including COLLATION FOR, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIME, LOCALTIMESTAMP, SYSDATE, SESSIONTIMEZONE, ROWNUM, CURRENT_ROLE, CURRENT_USER, SESSION_USER, USER, CURRENT_CATALOG, CURRENT_SCHEMA, CAST, EXTRACT, TIMESTAMPDIFF, OVERLAY, POSITION, SUBSTRING, TREAT, TRIM, NULLIF, NVL, NVL2, COALESCE, GREATEST, LEAST, LNNVL, REGEXP_LIKE, and XML functions.
  • If some secure encryption and decryption functions and masking functions are displayed completely by projection aliases, it may bring security problems. Therefore, only function names are displayed here, including: gs_encrypt_aes128, gs_decrypt_aes128, gs_encrypt, gs_decrypt, gs_encrypt_bytea, gs_decrypt_bytea, aes_encrypt, aes_decrypt, pg_create_physical_replication_slot_extern, dblink_connect, creditcardmasking, basicemailmasking, fullemailmasking, alldigitsmasking, shufflemasking, randommasking, regexpmasking, and gs_digest.
  • Parameter transfer using => is not supported for the projection alias to display the complete function. The projection alias cannot contain double quotation marks (""), for example, select "power"(2,3).
  • To enable the projection alias to display the complete function, this function is not affected by parameters such as removing 0 at the end.

proc_outparam_transfer_length

varray_compat

tableof_elem_constraints

After this parameter is enabled:

1. For the collection type, the validity of elements will be verified.
2. If the index is of the varchar collection type, the validity of the index length is also verified.

For details, see "Stored Procedure > Arrays, Collections, and Records > Collections" in Developer Guide.

The following is an example of verifying the validity of elements:

If this parameter is not enabled, the elements are not verified when the following stored procedure is called:

gaussdb=# CREATE OR REPLACE procedure p1 is
gaussdb$#     type t1 is table of varchar(5);
gaussdb$#     v t1 := t1();
gaussdb$# begin
gaussdb$#     v.extend();
gaussdb$#     v(1) := '123456';
gaussdb$#     raise info '%', v;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL p1();
INFO:  {123456}
 p1 
----

(1 row)

If this parameter is enabled, the elements are verified and an error may be reported when the following stored procedure is called:

gaussdb=# SET behavior_compat_options = 'tableof_elem_constraints';
SET
gaussdb=# CREATE OR REPLACE procedure p1 is
gaussdb$#     type t1 is table of varchar(5);
gaussdb$#     v t1 := t1();
gaussdb$# begin
gaussdb$#     v.extend();
gaussdb$#     v(1) := '123456';
gaussdb$#     raise info '%', v;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL p1();
ERROR:  value too long for type character varying(5)
CONTEXT:  PL/SQL function p1() line 5 at assignment

The following is an example of verifying the index value:

If this parameter is not enabled, no error is reported when the index value exceeds the defined length.

gaussdb=# CREATE OR REPLACE procedure p1 is
gaussdb$#     type t1 is table of int index by varchar(5);
gaussdb$#     v t1;
gaussdb$# begin
gaussdb$#     v('123456') := 1;
gaussdb$#     raise info '%', v;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL p1();
INFO:  {indexbyType:varchar,"123456"=>1}
 p1 
----

(1 row)

If this parameter is enabled, an error is reported when the index value exceeds the defined length.

gaussdb=# SET behavior_compat_options = 'tableof_elem_constraints';
SET
gaussdb=# CREATE OR REPLACE procedure p1 is
gaussdb$#     type t1 is table of int index by varchar(5);
gaussdb$#     v t1;
gaussdb$# begin
gaussdb$#     v('123456') := 1;
gaussdb$#     raise info '%', v;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL p1();
ERROR:  value too long for type character varying(5)
CONTEXT:  PL/SQL function p1() line 4 at assignment

allow_rownum_alias

After this parameter is enabled, ROWNUM can be used as a column alias in SQL statements using the AS syntax. ROWNUM is used as a common identifier and cannot be used as a pseudocolumn.

current_sysdate

If this parameter is enabled, the current OS time is obtained when the sysdate command is executed.

gaussdb=# set behavior_compat_options='current_sysdate';
SET
gaussdb=# select sysdate;
current_sysdate
---------------------
2023-06-20 20:15:27
(1 row)

allow_function_procedure_replace

In the default scenario, for A and PG compatibility modes, when there is a stored procedure (or function) outside the package, you cannot create a function (or stored procedure) with the same name because they are of different object types, and an error is reported. After this parameter is enabled, you can use the CREATE OR REPLACE method to replace stored procedures and functions with the same names outside the package.

gaussdb=# create or replace function proc_test return varchar2 as
gaussdb$# begin
gaussdb$# return '1';
gaussdb$# end;
gaussdb$# /
CREATE FUNCTION
gaussdb=# create or replace procedure proc_test as
gaussdb$# begin
gaussdb$# null;
gaussdb$# end;
gaussdb$# /
ERROR:  cannot change routine kind
DETAIL:  "proc_test" is a function.
gaussdb=# \df+ proc_test
                                                                             List of functions
 Schema |   Name    | Result data type  | Argument data types |  Type  | Volatility |   Owner   | Language | Source code | Description | fencedmode | propackage | prokind
--------+-----------+-------------------+---------------------+--------+------------+-----------+----------+-------------+-------------+------------+------------+---------
 public | proc_test | character varying |                     | normal | volatile   | wangxinyu | plpgsql  |  DECLARE   +|             | f          | f          | f
        |           |                   |                     |        |            |           |          | begin      +|             |            |            |
        |           |                   |                     |        |            |           |          | return '1';+|             |            |            |
        |           |                   |                     |        |            |           |          | end         |             |            |            |
(1 row)

-- Types can be replaced after the parameter is set.
gaussdb=# set behavior_compat_options='allow_function_procedure_replace';
SET
gaussdb=# create or replace procedure proc_test as
gaussdb$# begin
gaussdb$# null;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# \df+ proc_test
                                                                            List of functions
 Schema |   Name    | Result data type | Argument data types |  Type  | Volatility |   Owner   | Language | Source code | Description | fencedmode | propackage | prokind
--------+-----------+------------------+---------------------+--------+------------+-----------+----------+-------------+-------------+------------+------------+---------
 public | proc_test | void             |                     | normal | volatile   | wangxinyu | plpgsql  |  DECLARE   +|             | f          | f          | p
        |           |                  |                     |        |            |           |          | begin      +|             |            |            |
        |           |                  |                     |        |            |           |          | null;      +|             |            |            |
        |           |                  |                     |        |            |           |          | end         |             |            |            |
(1 row)

collection_exception_backcompat

Exception value thrown when an error related to the collection type is reported in the PL/SQL. Currently, three exception values are controlled. The mapping is as follows:

Parameter not enabled Parameter enabled

collection_is_null program_limit_exceeded

subscript_beyond_count program_limit_exceeded

subscript_outside_limit program_limit_exceeded

Example:

gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1;
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v.count();
gaussdb$# exception when collection_is_null then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Reference to uninitialized collection
 p1 
----

(1 row)

gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1 := t1(1, 2, 3);
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v(4);
gaussdb$# exception when subscript_beyond_count then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Subscript beyond count
 p1 
----

(1 row)

gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1 := t1(1, 2, 3);
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v(-1);
gaussdb$# exception when subscript_outside_limit then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Subscript outside of limit
 p1 
----

(1 row)

gaussdb=# set behavior_compat_options = 'collection_exception_backcompat';
SET
gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1;
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v.count();
gaussdb$# exception when program_limit_exceeded then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Reference to uninitialized collection
 p1 
----

(1 row)

gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1 := t1(1, 2, 3);
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v(4);
gaussdb$# exception when program_limit_exceeded then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Subscript beyond count
 p1 
----

(1 row)

gaussdb=# create or replace procedure p1 is
gaussdb$#     type t1 is table of int;
gaussdb$#     v t1 := t1(1, 2, 3);
gaussdb$#     v_int int;
gaussdb$# begin
gaussdb$#     v_int := v(-1);
gaussdb$# exception when program_limit_exceeded then
gaussdb$#     raise info '%', sqlerrm;
gaussdb$# end;
gaussdb$# /
CREATE PROCEDURE

gaussdb=# call p1();
INFO:  Subscript outside of limit
 p1 
----

(1 row)

enable_case_when_alias

If this parameter is enabled, the aliases of the CASE WHEN and DECODE syntaxes are character strings starting with __unnamed_.

Example:

gaussdb=# set behavior_compat_options='enable_case_when_alias';
SET
gaussdb=# create table test(c1 varchar2);
CREATE TABLE
gaussdb=# insert into test values('x');
INSERT 0 1
gaussdb=# select decode(c1,'x','0','default') from test;
 __unnamed_decode__ 
--------------------
 0
(1 row)

gaussdb=# select (case c1 when 'x' then '0' else 'default' end) from test;
 __unnamed_case_when__ 
-----------------------
 0
(1 row)

plsql_rollback_keep_user

Determines whether ROLLBACK and ROLLBACK TO SAVEPOINT in PL/SQL changes the current user. If this parameter is enabled, ROLLBACK in the PL/SQL does not change the current user.

Example:

gaussdb=# create user plsql_rollback1 PASSWORD '********';

gaussdb=# create user plsql_rollback2 PASSWORD '********';

gaussdb=# grant plsql_rollback1 to plsql_rollback2;

gaussdb=# create or replace procedure plsql_rollback1.p1 () authid definer

gaussdb-# as

gaussdb$# va int;

gaussdb$# begin

gaussdb$# raise info 'current usr:%', current_user;

gaussdb$# rollback;

gaussdb$# raise info 'current usr:%', current_user;

gaussdb$# end;

gaussdb$# /

CREATE PROCEDURE

gaussdb=# set session AUTHORIZATION plsql_rollback2 PASSWORD '********';

SET

gaussdb=> set behavior_compat_options = 'plsql_rollback_keep_user';

SET

gaussdb=> call plsql_rollback1.p1 ();

INFO: current usr:plsql_rollback1

INFO: current usr:plsql_rollback1

p1

----

(1 row)

forbid_package_function_with_prefix

After this parameter is enabled, an error is reported if a function with a prefix is created in a package.

For example:

-- Create a schema sch1.
gaussdb=# CREATE SCHEMA sch1;

-- An error is reported after a package is created.
gaussdb=# CREATE PACKAGE pck1
          IS 
          PROCEDURE sch1.pck1();
          END pck1;
          /
ERROR:  not support procedure name use *.* format in package at or near ";"
CONTEXT:  compilation of PL/pgSQL package near line 1

enable_bpcharlikebpchar_compare

Enables or disables the bpcharlikebpchar and bpcharnlikebpchar operators.

• This parameter is enabled by default for a newly installed database.
• After the database of a version earlier than 505.1.0 is upgraded, this parameter is disabled by default.

 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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

gaussdb=# SELECT bpcharlikebpchar('455'::BPCHAR(10), '455  '::BPCHAR);
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11));
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=# CREATE TABLE op_test (
  col BPCHAR(2) DEFAULT NULL
 );
CREATE TABLE
gaussdb=# CREATE INDEX op_index ON op_test(col);
CREATE INDEX
gaussdb=# INSERT INTO op_test VALUES ('a');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('1');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('11');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('12');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('sd');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('aa');
INSERT 0 1
gaussdb=# SHOW behavior_compat_options;
 behavior_compat_options 
-------------------------
(1 row)
-- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (all data should be returned in normal cases).
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
              QUERY PLAN              
--------------------------------------
 Sort
   Sort Key: col
   ->  Seq Scan on op_test
         Filter: (col ~~ (col)::text)
(4 rows)
gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 11
 12
 aa
 sd
(4 rows)
gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare';
SET
gaussdb=# SHOW behavior_compat_options;
     behavior_compat_options     
---------------------------------
 enable_bpcharlikebpchar_compare
(1 row)
-- After this parameter is enabled, the latest bpcharlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching.
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
          QUERY PLAN          
------------------------------
 Sort
   Sort Key: col
   ->  Seq Scan on op_test
         Filter: (col ~~ col)
(4 rows)
gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 1 
 11
 12
 a 
 aa
 sd
(6 rows)
gaussdb=# DROP TABLE op_test;
DROP TABLE

cursor_asensitive

Used for forward compatibility of cursor data-sensitive behavior. This item is not supported in the centralized mode.

show_full_error_lineno

Displays the number of the line that contains the header information of a stored procedure. When compilation error information is printed during the creation of a stored procedure, the number of the line where the CREATE statement starts is displayed. When a statement with compilation problems is called, there are two cases: if the recompilation is non-invalid, the number of the line where the CREATE statement starts is displayed; if an error is reported indicating invalid recompilation, the line number is displayed based on the original logic. To display the error information, you must have permission to view gs_source which contains the original definition of the stored procedure; otherwise, the number of the line where the error is reported is displayed based on the original logic. If an error is reported when the stored procedure is executed, after this parameter is set, the number of the line where the CREATE statement starts is displayed. This parameter takes effect only in centralized mode.

set behavior_compat_options='plpgsql_dependency, show_full_error_lineno'; 
set plsql_compile_check_options='plsql_expression_check';
CREATE OR REPLACE function proclex1(type1 int) return int 
is
  var1 int;
  var2 int;
begin
var3 := 30; --- var3 is not defined.
return type1;
end;
/
WARNING:  "var3" is not a known variable
DETAIL:  N/A
CONTEXT:  compilation of PL/pgSQL function "proclex1" near line 6
WARNING:  Function created with compilation errors.

enable_crosstype_integer_operator

Enables or disables the cross-type integer operator.

• This parameter is enabled by default for a newly installed database.
• After the database of a version earlier than 505.1.0 is upgraded, this parameter is disabled by default.
• Involved operators: =, <>, <, >, <=, and >=
• After this parameter is enabled, the involved cross-type integers can be compared directly without implicit conversion. Here is an example that uses (int1 op int2):

 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

gaussdb=# CREATE TABLE implicit_index(c1 int1);
CREATE TABLE
gaussdb=# CREATE INDEX idx1 ON implicit_index(c1);
CREATE INDEX
gaussdb=# SET behavior_compat_options='enable_crosstype_integer_operator';
SET
gaussdb=# EXPLAIN SELECT * FROM implicit_index WHERE c1 = 1::int2;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Only Scan using idx1 on implicit_index  (cost=0.00..4.48 rows=13 width=1)
   Index Cond: (c1 = 1::smallint)
(3 rows)

gaussdb=# SET behavior_compat_options='';
SET
gaussdb=# EXPLAIN SELECT * FROM implicit_index WHERE c1 = 1::int2;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on implicit_index  (cost=0.00..49.52 rows=13 width=1)
   Filter: ((c1)::bigint = 1::smallint)
(2 rows)

gaussdb=# DROP TABLE implicit_index;
DROP TABLE

time_constexpr_compact

If this parameter is enabled, in a scenario where the time expression is executed, the type automatically returned is with timezone or without timezone depending on whether the constant carries the time zone.

Currently, the timestamp and time types are supported.

-- Execute a timestamp expression without a time zone.
gaussdb=# SELECT timestamp '1999-03-15 8:00:00';
      timestamp      
---------------------
 1999-03-15 08:00:00
(1 row)
-- The type timestamp with time zone is returned after a timestamp expression with a time zone is executed.
gaussdb=# SELECT timestamp '1999-03-15 8:00:00 -8:00:00';
      timestamptz       
------------------------
 1999-03-16 00:00:00+08
(1 row)

10c

Compatible version of platform A

s1

• Compatible minor version of platform A, which affects functions TRUNC(date, fmt), ROUND(date, fmt), NVL2, LPAD, RPAD, ADD_MONTHS, MONTHS_BETWEEN, REGEXP_REPLACE, REGEXP_COUNT, TREAT, EMPTY_CLOB, INSTRB, trunc(number), greatest, least, mod, round(number), cast, to_date, to_timestamp, chr, rtrim, translate, to_char, to_number, and to_timestamp_tz.
• Data type casting: A decimal character string is rounded off when it is converted to an integer (int1/int2/int4/int8/int16).
• Data type casting: Implicit conversion from timestamp with time zone to timestamp without time zone is supported.

s2

• Compatible minor version of platform A, which affects functions such as dump, to_single_byte, to_multi_byte, nls_upper, nls_lower, initcap, ascii2, asciistr, unistr, vsize, cosh, remainder, sinh, tanh, nanvl, current_date, current_timestamp, dbtimezone, numtodsinterval, numtoyminterval, new_time, sessiontimezone, sys_extract_utc, tz_offset, to_binary_double, to_binary_float, to_dsinterval, to_yminterval, lnnvl, ora_hash, rawtohex2, bit2coding, and bit4coding.
• Supports all behaviors when the compatibility configuration item is set to s1.

s3

• Compatible minor version of platform A. If the parameter is enabled, nested calling of functions without parameters is supported.
• Supports all behaviors when the compatibility configuration item is set to s2.

s4

• Compatible minor version of platform A, which affects functions such as nchr(cvalue int|bigint), to_timestamp_tz, getclobval(xml), and getstringval(xml).
• Supports all behaviors when the compatibility configuration item is set to s3.

s5

• Compatible minor version of platform A. If the parameter is enabled, the composite type with the same name as a function is preferentially parsed. The sys_guid() function is affected.
• Supports all behaviors when the compatibility configuration item is set to s4.

s1

1. The following parameters are influenced:

   If this parameter is enabled, the datetime option in the disable_keyword_options parameter is removed, indicating that DATETIME is used as a non-reserved keyword. datetime can be used as a table name, column name, or alias, but cannot be used as a function name, stored procedure name, or parameter name of a function or stored procedure.

2. The following functions are influenced:

   curdate, from_days, date_format, str_to_date, current_date, datediff, timestampdiff, date_add, subtime, month, time_to_sec, to_days, to_seconds, dayname, monthname, convert_tz, sec_to_time, addtime, adddate, date_sub, timediff, last_day, weekday, from_unixtime, unix_timestamp, subdate, day, year, weekofyear, dayofmonth, dayofyear, week, yearweek, dayofweek, time_format, hour, minute, second, microsecond, quarter, utc_date, get_format, extract, makedate, period_add, timestamppadd, period_diff, utc_time, utc_timestamp, sysdate, current_timestamp, maketime, curtime, current_time, localtime, localtimestamp, now, lc_time_names, default_week_format, and json_object.

3. The following types are influenced:
   1. Be compatible with the integer type tinyint with the value range changed to –128 to 127.
   2. Be compatible with the character string types char and varchar. The precision n of char(n) and varchar(n) is changed from the byte length to the character length.
   3. Be compatible with the text types tinytext, mediumtext, and longtext. These types are mapped to the text type after the parameter is enabled.
   4. Be compatible with binary types tinyblob, blob, mediumblob, and longblob. These types are mapped to the bytea type after the parameter is enabled.
   5. Be compatible with floating-point types double, float, and real. Among them, double is mapped to the float8 type, double(p, s) is mapped to the numeric type, float(p, s) is mapped to the numeric type, real is mapped to float8, and real(p, s) is mapped to the numeric type.
   6. Be compatible with high-precision types numeric, dec, and decimal. If the precision and scale are not specified, the default precision is changed to 10 and the scale is changed to 0.
   7. Be compatible with time types datetime, timestamp, time, and date. Replace datetime[(p)] with a timestamp without time zone[(p)]. Replace timestamp[(p)] with a timestamp with time zone[(p)]. The input, output, range, and precision specifications of these types are changed. For details, see "SQL Reference > Data Type" in Developer Guide.
   8. Be compatible with integer types tinyint, smallint, int, and bigint. The display width and ZEROFILL attributes of these types take effect after the parameter is enabled.
4. The following syntax is influenced:

   The column constraint ON UPDATE update_expr can be specified.

5. The following operators are influenced:
   1. An error is reported when the || operator between bytea types is used.
   2. The REGEXP operator is supported.

s2

The compatibility behavior controlled by s1 is included. In addition, the following impacts are included:

1. The following parameters are influenced:
   1. The auto_increment_cache parameter takes effect only when this option is enabled.
   2. When this option is enabled, the multi_insert_min_rows parameter takes effect for auto-increment columns.
   3. When this option is enabled, standard_conforming_strings and escape_string_warning are set to off.
   4. The collation_connection and character_set_connection parameters take effect only when this option is enabled.
2. The following functions are affected:
   1. When 0, NULL, and definite values are imported or batch inserted into the auto-increment column, the auto-increment count is updated immediately when the definite values are inserted, and then 0 or NULL increases automatically based on the definite values.
   2. The like operator does not report an error when an escape character is at the end of the matching string.
   3. The sorting rule priorities of character sets and collations will be changed. For details, see section 7.4.7 in Developer Guide (Centralized).
   4. After the parameter is enabled, the collation of foreign keys cannot be inconsistent with that of columns.
3. The following syntax is influenced:
   1. The CREATE TABLE table_name LIKE source_table syntax is supported.
   2. The syntaxes CREATE TABLE table_name LIKE source_table and CREATE TABLE table_name (LIKE source_table) cannot specify INCLUDING and EXCLUDING options. By default, INCLUDING ALL is specified.
   3. The LOAD DATA syntax is supported. Some syntax functions in gs_loader that are consistent with the LOAD DATA syntax will change.
   4. The collate clause can be specified by set names.
   5. The syntaxes for changing table names, such as ALTER TABLE and RENAME TABLE, have impact in the following scenario:

      If the character string corresponding to the new table name starts with "#MySQL50#" and is followed by other characters, "#MySQL50#" will be ignored.

      If the old and new table names are the same, no error is reported.

s1

strict_trans_tables

Currently, only data types and system functions in B-compatible and M-compatible databases can be verified.

If this parameter is set, its format and range are strictly verified. If an invalid value is entered or the value exceeds the range, an error is reported during parsing.

If this parameter is not set, the format and range of the input parameter are loosely verified. If an invalid value is entered or the value exceeds the range, a warning is reported during parsing and the value 0 of the corresponding data is returned.

The options strict_trans_tables, only_full_group_by, no_zero_in_date, no_zero_date, and error_for_division_by_zero can only be set or canceled at the same time.

It can be set and canceled independently.

only_full_group_by

Projection columns that contain non-GROUP BY keys and that are not constants and aggregate functions are not allowed.

It can be set and canceled independently.

no_zero_in_date

The year, month, and day of the DATE cannot be 0.

It can be set and canceled independently.

no_zero_date

The value of DATE cannot be 0 (0000-00-00).

It can be set and canceled independently.

error_for_division_by_zero

The value cannot be divided by 0.

It can be set and canceled independently.

no_auto_create_user

This item has no actual function. It is used only for compatibility that an error is not reported when the SET SQL_MODE statement contains this option.

It can be set and canceled independently.

It can be set and canceled independently.

no_engine_substitution

This item has no actual function. It is used only for compatibility that an error is not reported when the SET SQL_MODE statement contains this option.

It can be set and canceled independently.

It can be set and canceled independently.

pad_char_to_full_length

Be used for formatted output of CHAR columns in a table. If there are CHAR columns, a string with spaces at the end is output. Otherwise, a string without spaces at the end is output.

It can be set and canceled independently.

It can be set and canceled independently.

no_auto_value_on_zero

If this option is included, the value 0 inserted into the AUTO_INCREMENT column does not trigger auto-increment.

It can be set and canceled independently.

It can be set and canceled independently.

no_unsigned_subtraction

Unsigned numbers cannot be subtracted. If unsigned integers are subtracted and the result is negative, an error is returned.

-

It can be set and canceled independently.

ansi_quotes

ANSI_QUOTES mode is enabled. In this mode, double quotation marks are treated as identifier quotes rather than string quotes, and you must quote the entire name of a table or column with double quotation marks instead of backquotes.

-

It can be set and canceled independently.

allow_invalid_dates

If this option is enabled, MySQL allows invalid dates to be inserted, for example, '0000-00-00'.

-

It can be set and canceled independently.

real_as_float

By default, the REAL type is regarded as the DOUBLE type. If this option is enabled, the REAL type is regarded as the FLOAT type.

-

It can be set and canceled independently.

s1

Specifies how gs_loader processes nul characters in data files during data import. If a data file contains the nul character, the data is truncated based on the position of the nul character. The data before the nul character is imported to the table, and the data after the nul character is truncated and discarded. This is used for forward compatibility of the function.

s2

Specifies how gs_loader processes nul characters in data files during data import. If a data file contains the nul character, the nul character is converted to the space character ' ' (0x20), and then processed, determined, and imported.

Empty

The default parameter settings do not affect any functions.

s1

• Supports gs_loader specifying data type (CHAR[(length)]/INTEGER external[(length)]/FLOAT external[(length)]/DECIMAL external[(length)]/TIMESTAMP/DATE/DATE EXTERNAL/INTEGER/SMALLINT/RAW[(length)]) to import data.
• Supports gs_loader converting expressions and extending scenarios for columns.

10c,s1

anyarray array_extendnull(anyarray,int4,int4); -- funcoid: 6018

clob empty_clob(); -- funcoid: 3825

int4 instrb(text,text); -- funcoid: 3240

int4 instrb(text,text,int4); -- funcoid: 3241

int4 instrb(text,text,int4,int4); -- funcoid: 3242

numeric months_between(timestamp,timestamp); -- funcoid: 1522

timestamp round(timestamp); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='round' and pronamespace=11 and pronargs=1 and allargtypes[0]=1114 query statement.

timestamp round(timestamp,text); -- funcoid: 4465

timestamp to_date(text,text,bool); -- funcoid: 1524

timestamp to_date(text,text,text,bool); -- funcoid: 1525

numeric to_number(text,text,bool); -- funcoid: 1787

numeric to_number(text,text,text,bool); -- funcoid: 1788

timestamp to_timestamp(text,text,bool); -- funcoid: 606

timestamp to_timestamp(text,text,text,bool); -- funcoid: 607

timestamptz to_timestamp_tz(text); -- funcoid: 1806

timestamptz to_timestamp_tz(text,text); -- funcoid: 1807

timestamptz to_timestamp_tz(text,text,bool); -- funcoid: 1808

timestamptz to_timestamp_tz(text,text,text,bool); -- funcoid: 1809

10c,s2

text DBTimezone(); -- funcoid: 5562

int8 ascii2(text); -- funcoid: 1625

text asciistr(text); -- funcoid: 1626

text asciistr(blob); -- funcoid: 1629

int4 bit2coding(text); -- funcoid: 9311

int4 bit4coding(text); -- funcoid: 9325

float8 cosh(float8); -- funcoid: 1548

numeric cosh(numeric); -- funcoid: 1549

timestamptz current_timestamp(numeric); -- funcoid: 3257

text dump(any); -- funcoid: 9086

text dump(any,int4); -- funcoid: 9088

text dump(any,int4,int4); -- funcoid: 9089

text dump(any,int4,int4,int4); -- funcoid: 9090

float4 nanvl(float4,float4); -- funcoid: 7112

float4 nanvl(float4,numeric); -- funcoid: 7115

float8 nanvl(float8,float8); -- funcoid: 7113

float4 nanvl(numeric,float4); -- funcoid: 7116

numeric nanvl(numeric,numeric); -- funcoid: 7114

timestamp new_time(timestamp,text,text); -- funcoid: 374

text nls_lower(text); -- funcoid: 4082

text nls_lower(text,text); -- funcoid: 4083

text nls_upper(text); -- funcoid: 4084

text nls_upper(text,text); -- funcoid: 4085

interval numtoyminterval(numeric,text); -- funcoid: 9333

int8 ora_hash(any); -- funcoid: 6127

int8 ora_hash(any,int8); -- funcoid: 6128

text rawtohex2(any); -- funcoid: 9540

numeric remainder(int8,int8); -- funcoid: 9767

numeric remainder(int2,int2); -- funcoid: 9765

numeric remainder(int4,int4); -- funcoid: 9766

float4 remainder(float4,float4); -- funcoid: 9771

float4 remainder(float4,numeric); -- funcoid: 9768

float8 remainder(float8,float8); -- funcoid: 9770

float4 remainder(numeric,float4); -- funcoid: 9769

numeric remainder(numeric,numeric); -- funcoid: 9761

numeric remainder(int1,int1); -- funcoid: 9764

text session_time_zone(); -- funcoid: 9571

float8 sinh(float8); -- funcoid: 1546

numeric sinh(numeric); -- funcoid: 1547

timestamp sys_extract_utc(timestamp); -- funcoid: 5258

timestamp sys_extract_utc(timestamptz); -- funcoid: 5259

float8 tanh(float8); -- funcoid: 9762

numeric tanh(numeric); -- funcoid: 9763

float8 to_binary_double(text); -- funcoid: 9669

float8 to_binary_double(text,text); -- funcoid: 9670

float8 to_binary_double(text,text,bool); -- funcoid: 9671

float8 to_binary_double(text,text,text,bool); -- funcoid: 9672

float4 to_binary_float(text); -- funcoid: 9673

float4 to_binary_float(text,text); -- funcoid: 9674

float4 to_binary_float(text,text,bool); -- funcoid: 9675

float4 to_binary_float(text,text,text,bool); -- funcoid: 9676

blob to_blob(any); -- funcoid: 6990

interval to_dsinterval(text); -- funcoid: 9126

interval to_dsinterval(text,text,bool); -- funcoid: 9127

text to_multi_byte(text); -- funcoid: 9537

text to_multi_byte(blob); -- funcoid: 9539

varchar to_nchar(int8); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=20 query statement.

varchar to_nchar(int2); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=21 query statement.

varchar to_nchar(int4); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=23; query statement.

text to_nchar(text); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=25 query statement.

varchar to_nchar(float4); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=700 query statement.

varchar to_nchar(float8); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=701 query statement.

varchar to_nchar(numeric); -- funcoid: obtains the OID by using the select oid from pg_proc where proname='to_nchar' and pronamespace=11 and pronargs=1 and allargtypes[0]=1700 query statement.

text to_nchar(timestamp); -- funcoid: 5698

text to_nchar(timestamptz); -- funcoid: 5699

text to_nchar(anyset); -- funcoid: 5700

text to_nchar(int8,text); -- funcoid: 5694

text to_nchar(int4,text); -- funcoid: 5693

text to_nchar(float4,text); -- funcoid: 5695

text to_nchar(float8,text); -- funcoid: 5696

text to_nchar(timestamp,text); -- funcoid: 5697

text to_nchar(timestamptz,text); -- funcoid: 5691

text to_nchar(interval,text); -- funcoid: 5690

text to_nchar(numeric,text); -- funcoid: 5692

text to_single_byte(text); -- funcoid: 9536

text to_single_byte(blob); -- funcoid: 9538

interval to_yminterval(text); -- funcoid: 9124

interval to_yminterval(text,text,bool); -- funcoid: 9125

text tz_offset(text); -- funcoid: 706

text unistr(text); -- funcoid: 9081

text unistr(blob); -- funcoid: 9082

int4 vsize(any); -- funcoid: 9083

10c,s4

clob getclobval(xml); -- funcoid: 8011

varchar getstringval(xml); -- funcoid: 6976

nvarchar2 nchr(int8); -- funcoid: 1694

timestamptz to_timestamp_tz(text,text,text); -- funcoid: 1804

timestamptz to_timestamp_tz(text,text,text,text,bool); -- funcoid: 1805

1

bit_length(string)

2

btrim(string text [, characters text])

3

char_length(string)

character_length(string)

4

chr(cvalue int|bigint)

chr(integer)

5

concat(str1,str2)

6

concat_ws(sep text, str"any" [, str"any" [, ...] ])

7

decode(string text, format text)

8

dump(expr[, return_fmt [, start_position [, length ] ] ])

9

encode(data bytea, format text)

10

find_in_set(text, set)

11

format(formatstr text [, str"any" [, ...] ])

12

left(str text, n int)

13

length(string)

14

lengthb(text/bpchar)

15

ltrim(string [, characters])

16

md5(string)

17

notlike(x bytea name text, y bytea text)

18

octet_length(string)

19

overlay(string placing string FROM int [for int])

20

quote_ident(string text)

21

quote_literal(string text)

22

quote_nullable(string text)

23

rawcat(raw,raw)

24

regexp_count(string text, pattern text [, position int [, flags text]])

25

regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]])

26

regexp_like(source_string, pattern [, match_parameter])

regexp_like(text,text,text)

27

regexp_matches(string text, pattern text [, flags text])

28

regexp_replace(string, pattern, replacement [,flags ])

29

regexp_split_to_array(string text, pattern text [, flags text ])

30

regexp_split_to_table(string text, pattern text [, flags text])

31

regexp_substr(source_char, pattern)

regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]])

32

repeat(string text, number int )

33

repexp_replace(string text, pattern text [, replacement text [, position int [, occurrence int [, flags text]]]])

34

replace(string text, from text, to text)

35

replace(string, substring)

36

reverse(str)

37

right(str text, n int)

38

rtrim(string [, characters])

39

sha(string)

40

sha1(string)

41

sha2(string, hash_length)

42

split_part(string text, delimiter text, field int)

43

substring(string [from int] [for int])

substring(string from pattern for escape)

substring(string from pattern)

44

substring_inner(string [from int] [for int])

45

tconvert(key text, value text)

46

to_single_byte(char)

47

translate(string text, from text, to text)

48

trim([leading |trailing |both] [characters] from string)

49

unistr(string)

50

vsize(expr)

51

PKG_UTIL.RAW_CAST_FROM_VARCHAR2

52

PKG_UTIL.LOB_CONVERTTOCLOB

53

PKG_UTIL.LOB_RAWTOTEXT

54

PKG_UTIL.LOB_TEXTTORAW

55

PKG_UTIL.RAW_CAST_TO_VARCHAR2

56

DBE_OUTPUT.PUT

57

DBE_OUTPUT.PUT_LINE

'sqrt_karatsuba'

• If this parameter is set, the Karatsuba square root algorithm is used when the sqrt function is called. The Karatsuba algorithm has higher performance but its precision may be different from that of the Newton iteration algorithm in rare cases.
• If this parameter is not set, the default Newton iteration algorithm is used to calculate the square root for the sqrt algorithm.

'allow_textconcat_null'

• If this parameter is set, the value of the corresponding character string is returned when a character string is combined with the null value in PG-compatible mode.

  -- Run the following command in PG-compatible mode:
  gaussdb=# set gs_format_behavior_compat_options='allow_textconcat_null';
  SET
  gaussdb=# select 'a' || null || 'b';
   ?column?
  ----------
   ab
  (1 row)
  

• If this parameter is not set, NULL is returned when a character string is combined with the null value in PG-compatible mode.