I really needed this functionality in PostgreSQL. A common use for
autonomous transactions is error logging. I want to log sqlerrm in a
function and raise an exception so the calling application knows there is an
error and I have it logged to a table.



I figured out a way to "hack" an autonomous transaction by using a dblink in
a function and here is a simple example:



create or replace function fn_log_error(p_function varchar, p_location int,
p_error varchar) returns void as

$$

declare

v_sql varchar;

v_return varchar;

v_error varchar;

begin

perform dblink_connect('connection_name', 'dbname=...');



v_sql := 'insert into error_log (function_name, location, error_message,
error_time) values (''' || p_function_name || ''', ' ||

p_location || ', ''' || p_error || ''', clock_timestamp())';



select * from dblink_exec('connection_name', v_sql, false) into v_return;



--get the error message

select * from dblink_error_message('connection_name') into v_error;



if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0
then

raise exception '%', v_error;

end if;



perform dblink_disconnect('connection_name');



exception

when others then

perform dblink_disconnect('connection_name');

raise exception '(%)', sqlerrm;

end;

$$

language 'plpgsql' security definer;



I thought I would share and it works rather well. Maybe someone could
enhance this concept to include it with the core database to provide
autonomous transactions.





Jon