oracle模式下列名默认转为大写,包括建表时存储及查询时比对、显示:
create table tb (foo varchar2(10)); -- 默认转大写存储
select "foo" from tb; -- failed
select "FOO" from tb; -- success
select foo from tb; -- success
lightdb23.3新增oracle模式下最外层查询的列名显示默认转大写(建表时存储及查询时比对均不改变),需开启用户会话级guc参数:
-- 需确保处于oracle模式
show lightdb_dblevel_syntax_compatible_type;
lightdb_dblevel_syntax_compatible_type
----------------------------------------
Oracle
(1 row)
-- 配置用户会话级guc参数,true开启默认转大写,false关闭默认转大写
set lightdb_oracle_sql_mode to 'true';
show lightdb_oracle_sql_mode;
lightdb_oracle_sql_mode
-------------------------
on
(1 row)
create table tb (Foo varchar2(10)); -- 不改变列存储方式,仍以小写存储
select foo from tb;
FOO
-----
(0 rows)
select "FOO" from tb; -- 列FOO不存在
ERROR: column "FOO" does not exist
select foo from (select foo as FOO from tb); -- 显示大写FOO
FOO
-----
(0 rows)
select "FOO" from (select foo as FOO from tb); -- 列FOO不存在
-- 关闭默认转大写
set lightdb_oracle_sql_mode to 'false';
select foo from (select foo as FOO from tb); -- 显示小写foo
foo
-----
(0 rows)
最后,需要指出的是,该功能目前暂不支持使用游标进行查询操作:
begin;
declare test_cursor cursor
for
select ciTY_Id, logdaTe logDATE, peaktemP as Peaktemp, test_columncase.unitsales as UnitsaleS
from test_columncase where ciTY_id > 10;
-- 暂不支持在游标中查询的列名默认转大写
fetch all from test_cursor;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
18 | 07-30-2006 | 2 | 222
19 | 08-20-2006 | 3 | 333
(2 rows)