Platform and Client Compatibility

display_leading_zero

Specifies how floating point numbers are displayed.

• If this item is not specified, for a decimal number between –1 and 1, the 0 before the decimal point is not displayed. For example, 0.25 is displayed as .25.
• If this item is specified, for a decimal number between –1 and 1, the 0 before the decimal point is displayed. For example, 0.25 is displayed as 0.25.

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:

openGauss=# 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:

openGauss=# 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 object for which no schema name is specified.

If no schema name is specified for a stored procedure, the search is performed in the schema to which the stored procedure belongs.

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 specified, the result of the to_number() function is the same as that of PG11. Otherwise, the result is the same as that of the O database.

unbind_divide_bound

Specifies the range check on the result of integer division.

If this item is specified, you do not need to check the range of the division result. For example, the result of INT_MIN/(-1) can be INT_MAX+1. If this item is not specified, an out-of-bounds error is reported because the result is greater than INT_MAX.

convert_string_digit_to_numeric

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

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.

openGauss=# 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 ('').

openGauss=# 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.

If this item is specified and a concat function has a parameter of the variadic type, different result formats in A database and Teradata are retained. If this item is not specified and a concat function has a parameter of the variadic type, same result formats in A database and Teradata are retained, and the results are the same as those in A database. This option has no effect on MY because MY has no variadic type.

merge_update_multi

When MERGE INTO... WHEN MATCHED THEN UPDATE (see MERGE INTO) and INSERT... ON DUPLICATE KEY UPDATE (see INSERT) are used, control 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 and the preceding scenario exists, an error is reported, that is, 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 O database.

hide_tailing_zero

Configuration item for numeric display. If this parameter is not set, numeric data is displayed in the specified precision. When setting this parameter, hide 0 at the end of the decimal place.

set behavior_compat_options='hide_tailing_zero';
select cast(123.123 as numeric(15,10));
numeric
---------
123.123
(1 row)

rownum_type_compat

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

aformat_null_test

Determines the logic for checking whether the row type is not null. When this parameter is set, if a column in a row is not null, true is returned.

When this parameter is not set, if all columns in a row are not null, true is returned.

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 O compatibility mode is used, the effective scope of implicit cursor states (SQL %FOUND, SQL %NOTFOUND, SQL %ISOPNE and SQL %ROWCOUNT) are extended only the currently executed function to all subfunctions invoked by this function.

proc_outparam_override

Determines the reloading of output parameters of a stored procedure. After this parameter is enabled, the stored procedure can be properly invoked even if only the output parameters of the stored procedure are different.

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 and a new variable is created. Otherwise, the defined rec variable is reused and no new variable is created.

allow_procedure_compile_check

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

char_coerce_compat

Determines the behavior when char(n) types are converted to other variable-length string types. By default, spaces at the end are omitted when the char(n) type is converted to other variable-length string types. After this parameter is enabled, spaces at the end are not omitted during conversion. In addition, if the length of the char(n) type exceeds the length of other variable-length string types, an error is reported. This parameter is valid only when sql_compatibility is set to A.

pgformat_substr

Controls the performance of substr(str, from, for) in different scenarios. By default, if the value of from is less than 0, substr counts from the end of the string. If the value of for is less than 1, substr returns NULL. After this parameter is enabled, if the value of from is less than 0, substr counts from the first (-from + 1) bit of the character string. If the value of for is less than 0, substr reports an error. This parameter is valid only when **sql_compatibility** is set to **PG**.

disable_record_type_in_dml

Prohibit inserting virtual columns. After enabling this option will prohibit to use record type variables as insertion values in insert statements.

create table t1(col1 varchar(10),col varchar(10));
create table t2(col1 varchar(10),col varchar(10));
set behavior_compat_options='disable_record_type_in_dml';
insert into t1 values('one','two');
declare
  cursor cur1 is select * from t1;
  source cur1%rowtype:=('ten','wtu');
begin
  for source in cur1
  loop
    raise notice '%',source;
    insert into t2 values(source);
  end loop; 
end;
/
ERROR:  The record type variable cannot be used as an insertion value.
CONTEXT:  SQL statement "insert into t2 values(source)"
PL/pgSQL function inline_code_block line 7 at SQL statement
set behavior_compat_options='';
insert into t1 values('one','two');
declare
cursor cur1 is select * from t1;
source cur1%rowtype:=('ten','wtu');
begin
for source in cur1
loop
raise notice '%',source;
insert into t2 values(source);
end loop;
end;
/
NOTICE:  (one,two)
NOTICE:  (one,two)