PostgreSQL执行Oracle转换SQL报sys_context不存在错误如何解决

问题原因 #

PostgreSQL 未内置 Oracle 风格的 sys_context 函数，该函数用于读取命名空间下的自定义上下文参数，PostgreSQL 中等价的内置功能为 current_setting 函数。

解决步骤 #

1. 提前配置对应上下文参数
   如果是会话临时使用，执行如下语句设置参数：
   SET temp1.hid = '实际的hid数值';
   如果需要全局生效，可将 temp1.hid = 数值 加入 postgresql.conf 配置文件，执行 pg_ctl reload 重载配置即可。

2. 修改SQL语句
   将原SQL中的 sys_context 调用替换为 current_setting，修改后完整语句如下：

select
        hid,
        pname,
        starting_from,
        ename
    from schema1.pet
    where hid=COALESCE(current_setting('temp1.hid', true)::int,0);

兼容说明 #

• current_setting 第二个参数传 true 表示参数不存在时返回null而非报错，和原sys_context行为一致
• 用::int做类型转换替代原来的TO_NUMBER，是PostgreSQL原生的类型转换语法，兼容性更好
• 如果你使用的PostgreSQL版本低于9.6，不支持current_setting的第二个参数，可以用如下兼容写法：

select
        hid,
        pname,
        starting_from,
        ename
    from schema1.pet
    where hid=COALESCE((select setting::int from pg_settings where name = 'temp1.hid'),0);

内容的提问来源于stack exchange，提问作者xpetta