标签
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();
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
