autonomous transactions - Mailing list pgsql-hackers

<div class="Section1"><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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
andI have it logged to a table.  </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I figured out a way to "hack" an autonomous transaction by using a dblink in a function and here is
asimple example:</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">create or replace function fn_log_error(p_function varchar, p_location int, p_error varchar) returns void
as</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt">$$</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_sql varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_return varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  v_error varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  perform dblink_connect('connection_name', 'dbname=...');</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">  </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_sql := 'insert into error_log (function_name, location, error_message, error_time) values (''' ||
p_function_name|| ''', ' || </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">           p_location || ', ''' || p_error || ''', clock_timestamp())';</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  select * from dblink_exec('connection_name', v_sql, false) into v_return;</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  --get the error message</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  select * from dblink_error_message('connection_name') into v_error;</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0 then</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">    raise exception '%', v_error;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  end if;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  perform dblink_disconnect('connection_name');</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">exception</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  when others then</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">    perform dblink_disconnect('connection_name');</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">    raise exception '(%)', sqlerrm;</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">end;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">$$</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">language 'plpgsql' security definer;</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I thought I would share and it works rather well.  Maybe someone could enhance this concept to
includeit with the core database to provide autonomous transactions.</span></font><p class="MsoPlainText"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Jon</span></font></div>