PostgreSQL Oracle 兼容性 之 USERENV

标签

PostgreSQL , Oracle , USERENV , 会话环境变量

背景

USERENV 是Oracle 用来获取当前会话变量的函数。官方是这么介绍的：

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117

Describes the current session. The predefined parameters of namespace USERENV are listed in Table 5-11.

一些常见的例子：

SELECT USERENV('CLIENT_INFO') FROM dual;    

SELECT USERENV('ENTRYID') FROM dual;    

SELECT USERENV('ISDBA') FROM dual;    -- 查看当前用户是否是DBA如果是则返回true    

SELECT USERENV('LANG') FROM dual;    

SELECT USERENV('LANGUAGE') FROM dual;    

SELECT USERENV('SESSIONID') FROM dual;  -- 会话标志：sessionId    

SELECT USERENV('TERMINAL') FROM dual;

PostgreSQL中如何实现类似的功能呢？

1、写个壳子，支持输出任意类型。（因为前面提到的变量，返回的类型可能是时间、字符串、数字等。）

create or replace function userenv(anynonarray) returns anynonarray as $$
declare
begin
  case lower($1)
  when 'sessionid' then
    return get_session_id();
  when 'isdba' then
    return get_isdba();
  when 'action' then
    return get_action();
  else
    return null;
  end case;
end;
$$ language plpgsql strict;

然后需要写实际的函数，例如

1、USERENV('SESSIONID')：

create sequence public.pg_session_id_sequence_oracle_comp;
grant all on sequence public.pg_session_id_sequence_oracle_comp to public;  

create OR replace function get_session_id() returns int8 AS $$
declare res int8;
begin
SELECT currval('public.pg_session_id_sequence_oracle_comp') into res;
return res;
exception
    WHEN sqlstate '55000' THEN
SELECT nextval('public.pg_session_id_sequence_oracle_comp') into res;
return res;
    WHEN sqlstate '42P01' THEN
create sequence public.pg_session_id_sequence_oracle_comp;
SELECT nextval('public.pg_session_id_sequence_oracle_comp') into res;
return res;
end;
$$ language plpgsql strict SET client_min_messages to error;

2、USERENV('ISDBA')

create OR replace function get_isdba() returns boolean AS $$
  select rolsuper from pg_roles where rolname=current_user;
$$ language sql strict SET client_min_messages to error;

3、USERENV('ACTION')

create OR replace function get_ACTION() returns text AS $$
  select application_name from pg_stat_activity where pid=pg_backend_pid();
$$ language sql strict SET client_min_messages to error;

使用例子：

test=> select userenv('isdba'::Text);
 userenv
---------
 false
(1 row)  

test=> select userenv('action'::Text);
 userenv
---------
 psql
(1 row)  

test=> select userenv('sessionid'::Text);
 userenv
---------
 1
(1 row)  

test=> select userenv('hello'::Text);
 userenv
---------  

(1 row)

其他的ENV变量请自行增加，PG的各种获取渠道，动态视图、管理函数等如下。

https://www.postgresql.org/docs/10/static/functions-info.html

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#MONITORING-STATS-VIEWS

https://www.postgresql.org/docs/9.6/static/multibyte.html