813
PostgreSQL Oracle 兼容性 之 USERENV
標簽
PostgreSQL , Oracle , USERENV , 會話環境變量
背景
USERENV 是Oracle 用來獲取當前會話變量的函數。官方是這麼介紹的：
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm
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
https://www.postgresql.org/docs/9.6/static/multibyte.html
最後更新：2017-10-28 23:03:50