I have an Oracle stored procedure which I'm migrating to PostgreSQL.
There are DBMS_APPLICATION_INFO.READ_MODULE
, DBMS_APPLICATION_INFO.SET_ACTION
, and DBMS_APPLICATION_INFO.SET_MODULE
procedures in the stored procedure for which I'm unable to find alternatives in Postgres.
What could be the closest alternatives for these procedures in Postgres?
You can use the parameter application_name
:
Example:
postgres=# show application_name;
application_name
------------------
psql
(1 row)
postgres=# select application_name from pg_stat_activity where pid = pg_backend_pid();
application_name
------------------
psql
(1 row)
postgres=# set application_name='myapp';
SET
postgres=# select application_name from pg_stat_activity where pid = pg_backend_pid();
application_name
------------------
myapp
(1 row)
postgres=#
Here is a possible solution to implement action and module:
begin;
BEGIN
set application_name='my_module:my_action';
SET
select application_name from pg_stat_activity where pid = pg_backend_pid();
application_name
---------------------
my_module:my_action
(1 row)
commit;
COMMIT
select application_name from pg_stat_activity where pid = pg_backend_pid();
application_name
---------------------
my_module:my_action
(1 row)
do
$$
declare
v_module text;
v_action text;
v_application_name text;
v_pos int;
begin
select application_name into v_application_name
from pg_stat_activity
where pid = pg_backend_pid();
v_pos = position(':' in v_application_name);
if (v_pos > 0)
then
v_module = substring(v_application_name, 1, v_pos - 1);
v_action = substring(v_application_name, v_pos + 1);
raise notice 'v_module=%, v_action=%', v_module, v_action;
end if;
end;
$$;
NOTICE: v_module=my_module, v_action=my_action
DO