Column RDB$TYPES.RDB$SYSTEM_FLAG can be changed from 0 to 1 using UPDATE command (after custom type has been inserted with RDB$SYSTEM_FLAG = 0)

[pavel-zotov]

Consider script:

set bail on;
set blob all;
set list on;
set count on;
shell del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
set echo on;

select * from rdb$types where coalesce(rdb$system_flag,0)=0;

insert into rdb$types(rdb$field_name, rdb$type, rdb$type_name, rdb$description, rdb$system_flag)
values('amount_avaliable', -32767, 'stock_amount','Total number of units that can be sold immediately to any customer', 0);

update rdb$types set rdb$system_flag = 1 where coalesce(rdb$system_flag,0)=0;
commit;

select * from rdb$types where rdb$type = -32767;

Its output will finish with following:

select * from rdb$types where rdb$type = -32767;

RDB$FIELD_NAME                  amount_avaliable

RDB$TYPE                        -32767
RDB$TYPE_NAME                   stock_amount

RDB$DESCRIPTION                 b:780
Total number of units that can be sold immediately to any customer
RDB$SYSTEM_FLAG                 1 <<<<<<<<<<<< [ ! ]

(so, rdb$system_flag became 1 which means 'system type').

PS. If we specify value = 1 for rdb$system_flag in INSERT statement then error will be:

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
INSERT operation is not allowed for system table "SYSTEM"."RDB$TYPES".
-------------------------------------------------------------------------------------------------------------
SQLCODE: -902
SQLSTATE: 42000
GDSCODE: 335545030

Maybe it will be better to change its ggdscode and text to more appropriate like "rdb$system_flag can not be 1 when inserting user-defined types" ?