Hi,
In oracle there is a built in way to make a transaction autonomous using PRAGMA autonomous_transaction, but in EDB Postgres there is none ( as far as I know). What should one do in pl/pgsql code to make an equivalency of autonomous transaction functionality in EDB Postgres ?
For example:
Procedure write_log_table (
valueA IN VARCHAR2
)
IS
PRAGMA autonomous_transaction;
BEGIN
INSERT INTO TABLE
( seq,
column_name_2,
column_name_3)
VALUES ( seq.NEXTVAL,
valueA ,
NVL(USERENV('TERMINAL'), 'Unknown') );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise exception message
END write_log_table;
Thanks
You can use dblink module to make autonomous transaction, dblink make a new connections and a new conections is a new transaction, for example a extra function that will be execute with dblink module
----function extra
CREATE OR REPLACE FUNCTIONatx_write_log_table(Avalue VARCHAR2)
RETURNS void AS
$BODY$
DECLARE
l_sql text;
BEGIN
RAISE NOTICE 'AUTO_TX CON DBLINK ';
perform dblink_connect('new_conn','port=5432 dbname=mydb host=localhost user=postgres password=postgres');
l_sql := 'INSERT INTO TABLE
( seq,
column_name_2,
column_name_3)
VALUES ( 1,'''|| valueA ||''' , 'values2' ); ';
perform dblink_exec('new_conn',l_sql);
perform dblink_disconnect('new_conn');
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE ' error';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Procedure write_log_table (
valueA IN VARCHAR2
)
IS
BEGIN
--call extra function
perform atx_write_log_table(valueA);
EXCEPTION
WHEN OTHERS THEN
raise exception message
END write_log_table;
regards
You can use dblink module to make autonomous transaction, dblink make a new connections and a new conections is a new transaction, for example a extra function that will be execute with dblink module
----function extra
CREATE OR REPLACE FUNCTIONatx_write_log_table(Avalue VARCHAR2)
RETURNS void AS
$BODY$
DECLARE
l_sql text;
BEGIN
RAISE NOTICE 'AUTO_TX CON DBLINK ';
perform dblink_connect('new_conn','port=5432 dbname=mydb host=localhost user=postgres password=postgres');
l_sql := 'INSERT INTO TABLE
( seq,
column_name_2,
column_name_3)
VALUES ( 1,'''|| valueA ||''' , 'values2' ); ';
perform dblink_exec('new_conn',l_sql);
perform dblink_disconnect('new_conn');
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE ' error';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Procedure write_log_table (
valueA IN VARCHAR2
)
IS
BEGIN
--call extra function
perform atx_write_log_table(valueA);
EXCEPTION
WHEN OTHERS THEN
raise exception message
END write_log_table;
regards