SYS_CONTEXT Function - Oracle to PostgreSQL Migration

In Oracle, the SYS_CONTEXT function retrieves system or session-level information previously set by the user application using the DBMS_SESSION.SET_CONTEXT procedure.

In PostgreSQL, system information is retrieved using dedicated built-in functions, while session-level information for applications can be stored and accessed using session variables.

Oracle:

  -- Get the current user
  SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual;

PostgreSQL:

  -- Get the current user
  SELECT SESSION_USER;

Mapping SYS_CONTEXT parameters for system information::

In Oracle, an application can store and retrieve session-level information using the DBMS_SESSION.SET_CONTEXT procedure and the SYS_CONTEXT function:

Oracle:

  -- Define a procedure that sets the context
  CREATE OR REPLACE PROCEDURE set_application_context IS
  BEGIN
    -- Set the application name and version
    DBMS_SESSION.SET_CONTEXT('Application_info', 'Name', 'Web Store');
    DBMS_SESSION.SET_CONTEXT('Application_info', 'Version', '3.1');
  END;
  /
 
  -- Context can be set by the specified procedure
  CREATE OR REPLACE CONTEXT Application_info USING set_application_context;
 
  -- Define the context for the current session
  EXEC set_application_context;
 
  -- Retrieve context parameters
  SELECT SYS_CONTEXT('Application_info', 'Name') FROM dual;
  /* Web Store */
 
  SELECT SYS_CONTEXT('Application_info', 'Version') FROM dual;
  /* 3.1 */

In PostgreSQL, if you have only a few session-level parameters, each parameter can be stored in its own session variable using the SET_CONFIG and CURRENT_SETTING functions:

PostgreSQL:

  -- Set variable for the current session
  SELECT SET_CONFIG('Application_info.name', 'Web Store', FALSE);
  SELECT SET_CONFIG('Application_info.version', '3.1', FALSE);
 
  -- Retrieve context parameters
  SELECT CURRENT_SETTING('Application_info.name');
  /* Web Store */
 
  SELECT CURRENT_SETTING('Application_info.version');
  /* 3.1 */

If numerous context parameters exist across different namespaces, a single JSON session variable can be used to store and retrieve them, to provide a structured and scalable approach:

PostgreSQL:

  -- Set context in JSON
  SELECT SET_CONFIG('sys_context.Application_info',
    JSONB_SET(COALESCE(CURRENT_SETTING('sys_context.Application_info', TRUE), '{}')::JSONB, 
      '{Name}', '"Web Store"')::TEXT, FALSE);
 
  SELECT SET_CONFIG('sys_context.Application_info',
    JSONB_SET(COALESCE(CURRENT_SETTING('sys_context.Application_info', TRUE), '{}')::JSONB, 
      '{Version}', '"3.1"')::TEXT, FALSE);
 
  -- Retrieve context parameters
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE)::JSONB ->> 'Name';
  /* Web Store */
 
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE)::JSONB ->> 'Version';
  /* 3.1 */
 
  -- Reviewing all context parameters
  SELECT CURRENT_SETTING('sys_context.Application_info', TRUE);
  /* {"Name": "Web Store", "Version": "3.1"} */

For more information, see Oracle to PostgreSQL Migration.