Convert function SYS_CONTEXT in Oracle to PostgreSQL

Question

I'm converting Oracle SQL to PostgreSQL. I can't find a function like Oracle in PostgreSQL. SYS_CONTEXT('USERENV', 'SESSIONID'); SYS_CONTEXT('USERENV', 'SESSION_USER'); SYS_CONTEXT('USERENV', 'OS_USER'); SYS_CONTEXT('USERENV', 'TERMINAL'); SYS_CONTEXT('USERENV', 'MODULE'); SYS_CONTEXT('USERENV', 'ACTION')

Laurenz Albe · Accepted Answer · 2021-12-14 03:04:52Z

2

There are no totally identical replacements in PostgreSQL. You will have to be creative. In my opinion, the best equivalents are:

for SESSIONID, use the backend process ID as returned by the function pg_backend_pid()
for SESSION_USER, use the session_user function (no parentheses!)
for MODULE and ACTION, use the parameter application_name
there is no equivalent for TERMINAL and OS_USER: the former makes no sense in the context of PostgreSQL, the latter makes no sense at all

answered Dec 14, 2021 at 3:04

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

terminal and os_user are provided by the SQL client to Oracle, so this could be appended to application_name if the information is needed.

Collectives™ on Stack Overflow

Convert function SYS_CONTEXT in Oracle to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related