PostgreSQL Oracle 兼容性 之 USERENV

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

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

<a href="https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117">https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117</a>

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

一些常见的例子：

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

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

1、USERENV('SESSIONID')：

2、USERENV('ISDBA')

3、USERENV('ACTION')

使用例子：

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

<a href="https://www.postgresql.org/docs/10/static/functions-info.html">https://www.postgresql.org/docs/10/static/functions-info.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#MONITORING-STATS-VIEWS">https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#MONITORING-STATS-VIEWS</a>

<a href="https://www.postgresql.org/docs/9.6/static/multibyte.html">https://www.postgresql.org/docs/9.6/static/multibyte.html</a>