Autonomous Transaction

[angie]

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

[asotolongo · Accepted solution]

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