作者
digoal
日期
2017-09-26
标签
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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
