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;      

Parameter	Return Value
ACTION	Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.
CLIENT_INFO	Returns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.
ENTRYID	The current audit entry number. The audit entryid sequence is shared between fine-grained audit records and regular audit records. You cannot use this attribute in distributed SQL statements. The correct auditing entry identifier can be seen only through an audit handler for standard or fine-grained audit.
ISDBA	Returns TRUE if the user has been authenticated as having DBA privileges either through the operating system or through a password file.
LANG	The abbreviated name for the language, a shorter form than the existing ‘LANGUAGE’ parameter.
LANGUAGE	The language and territory currently used by your session, along with the database character set, in this form: language_territory.characterset
SESSIONID	The auditing session identifier. You cannot use this attribute in distributed SQL statements.
TERMINAL	The operating system identifier for the client of the current session. In distributed SQL statements, this attribute returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations. (The return length of this parameter may vary by operating system.)

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();    
  when 'lang' then  
    return get_lang();  
  when 'language' then  
    return get_language();  
  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)    

4、USERENV(‘LANG’), USERENV(‘LANGUAGE’)

Oracle

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch3globenv.htm

language

Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN. The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN.

territory

Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA, FRANCE, or CANADA. If the territory is not specified, then the value is derived from the language value.

charset

Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user’s terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII, WE8ISO8859P1, WE8DEC, WE8MSWIN1252, or JA16EUC. Each language has a default character set associated with it.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions216.htm#SQLRF06157

LANG

LANG returns the ISO abbreviation for the language name, a shorter form than the existing ‘LANGUAGE’ parameter.

LANGUAGE

LANGUAGE returns the language and territory used by the current session along with the database character set in this form:

language_territory.characterset

PostgreSQL

https://www.postgresql.org/docs/10/static/locale.html

LC_COLLATE String sort order
LC_CTYPE Character classification (What is a letter? Its upper-case equivalent?)
LC_MESSAGES Language of messages
LC_MONETARY Formatting of currency amounts
LC_NUMERIC Formatting of numbers
LC_TIME Formatting of dates and times

create OR replace function get_LANG() returns text AS $$     
   select (regexp_split_to_array(current_setting('lc_messages'), '\.'))[1];    
$$ language sql strict;   
  
create OR replace function get_LANGUAGE() returns text AS $$     
   select (regexp_split_to_array(current_setting('lc_monetary'), '\.'))[1]||'.'||pg_client_encoding();  
$$ language sql strict;   
  
postgres=# select userenv('language'::text);  
  userenv     
------------  
 en_US.UTF8  
(1 row)  
  
postgres=# select userenv('lang'::text);  
 userenv   
---------  
 en_US  
(1 row)  

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

https://www.postgresql.org/docs/current/static/libpq-envars.html

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

digoal’s 大量PostgreSQL文章入口